GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844

Download Excel File: https://excelisfun.net/files/EMT1844.xlsx
Learn about how to use the GROUPBY function to make single cell formula reports that can update instantly when source data changes.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions
2. (00:58) Compare arguments in GROUPBY and PIVOTBY functions.
3. (01:58) GROUPBY and the 7 arguments.
4. (01:58) 16 New Lambda Replacement Functions.
5. (05:43) New Lambda Replacement Functions as replacement for LAMBDA.
6. (06:17) PERCENTAGE of in GROUPBY Function.
7. (07:04) Why use formulas rather than PivotTables.
8. (07:17) Two Fields in Row Area of Report.
9. (08:05) Problem: No Label for Calculation Column. Look at field_header argument.
10. Continue reading “GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844”

Digging deeper into Data Activator in Microsoft Fabric

We look deeper at Data Activator in Microsoft Fabric with Will Thompson. He shows us how to work with real time data and also how you can leverage Power Automate from Power Platform to take action.

What is Data Activator?
https://learn.microsoft.com/fabric/data-activator/data-activator-introduction

Assign data to objects in Data Activator
https://learn.microsoft.com/fabric/data-activator/data-activator-assign-data-objects

Use Custom Actions to trigger Power Automate Flows
https://learn.microsoft.com/fabric/data-activator/data-activator-trigger-power-automate-flows

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

Continue reading “Digging deeper into Data Activator in Microsoft Fabric”

Leverage OneLake with Azure Databricks

Want to start using Azure Databricks with OneLake in Microsoft Fabric? Patrick walks you through how to get this working! Storing the data in OneLake opens up a lot of other possibilities.

Using Azure Databricks with Microsoft Fabric and OneLake
https://blog.fabric.microsoft.com/blog/using-azure-databricks-with-microsoft-fabric-and-onelake

Integrate OneLake with Azure Databricks
https://learn.microsoft.com/fabric/onelake/onelake-azure-databricks

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

http://twitter.com/guyinacube
http://twitter.com/awsaxton
http://twitter.com/patrickdba
Continue reading “Leverage OneLake with Azure Databricks”

Excel – First Look at Amazing GroupBy PivotBy PercentOf Functions in Excel – Episode 2633

Microsoft Excel Tutorial: Three New Functions Debut in Excel: GROUPBY, PIVOTBY, and PERCENTOF.

To download the workbook from today: https://www.mrexcel.com/youtube/6jP7DflN4sw/

Read the Microsoft Announcement: https://techcommunity.microsoft.com/t5/excel-blog/new-aggregation-functions-groupby-and-pivotby/ba-p/3965765

Welcome to episode 2633 of MrExcel’s netcast, where we explore the latest and greatest features of Microsoft Excel. In this episode, we will take a first look at three new functions: GROUPBY, PIVOTBY, and PERCENT OF. These functions are incredibly simple to use, yet offer deep options for data analysis. So let’s dive in and see what they can do!

First up, we have GROUPBY. This function allows us to group data by Continue reading “Excel – First Look at Amazing GroupBy PivotBy PercentOf Functions in Excel – Episode 2633”

Excel New Check Performance To Reduce Excel File Size – Episode 2632

Microsoft Excel Tutorial: A new Check Performance Feature debuts in Excel to clean excess cell formatting that can cause large file sizes.

Welcome to episode 2632 of MrExcel’s Net Cast! In this video, we’ll be discussing a new feature in the Windows version of Excel that has been available in Excel online for some time now. It’s called Check Performance and it can help you clean up unwanted cell formats and reduce file sizes. This feature can be found on the Review tab and is definitely worth checking out for any workbook that you’ve been using for years.

Now, before you go Continue reading “Excel New Check Performance To Reduce Excel File Size – Episode 2632”

Unleash the Potential of Data Activator in Microsoft Fabric

Get ready to unlock the full potential of Power BI with Data Activator in Microsoft Fabric. Join Will Thompson as he walks you through the features and benefits of this powerful tool. Whether you’re a Power BI pro or just starting out, this video has something for everyone.

What is Data Activator?
https://learn.microsoft.com/fabric/data-activator/data-activator-introduction

Get started with Data Activator
https://learn.microsoft.com/fabric/data-activator/data-activator-get-started

Will Thompson:
https://twitter.com/Will_MI77
https://www.linkedin.com/in/will-mi77/

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Continue reading “Unleash the Potential of Data Activator in Microsoft Fabric”

Why On-Object Editing in Power BI???

Curious about why Power BI introduced On-object editing? Rosie Levasseur joins us to explain why! We’ve come to love it and hope you give it a try.

Use on-object interaction with visuals in your report (preview)
https://learn.microsoft.com/power-bi/create-reports/power-bi-on-object-interaction

Roseanne Levasseur
https://www.linkedin.com/in/roseanne-levasseur/

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

http://twitter.com/guyinacube
http://twitter.com/awsaxton
http://twitter.com/patrickdba
http://www.facebook.com/guyinacube
rel=”nofollow”>https://www.instagram.com/guyinacube/
https://guyinacube.com

***Gear***
🛠 Check out my Tools page – https://guyinacube.com/tools/

#PowerBI #MicrosoftFabric #GuyInACube

FILTER Function to Extract Records by Area Code. Excel Magic Trick 1843

Download Excel File: https://excelisfun.net/files/EMT1843.xlsx
Learn about how to use FILTER and SEARCH to filter a data set by area code.
Topics:
1. (00:00) Introduction
2. (00:05) FILTER and SEARCH functions to filter by Area Code.
3. (02:04) Summary
4. (02:13) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #Filter #Search #filterfunction #filterfun

Exploring Ceiling, Floor, Ceiling.Math, and Floor.Math Functions in Excel and Power Query: A Guide

In the realm of numerical precision, Excel provides an array of functions to manipulate data accurately. Among these, Ceiling, Floor, Ceiling.Math, and Floor.Math functions play a pivotal role in rounding numbers to specific intervals. Additionally, when working with extensive datasets, Excel’s Power Query offers an advanced platform for data transformation. In this comprehensive guide, we will explore how to utilize Ceiling, Floor, Ceiling.Math, and Floor.Math functions in both Excel and Power Query, empowering you with enhanced data manipulation techniques.

Understanding Ceiling, Floor, Ceiling.Math, and Floor.Math Functions:

Before we delve into their applications, let’s understand the functionalities of these functions. The Ceiling function rounds a number up, away from zero, to the nearest multiple of significance. The Floor function rounds a number down, towards zero, to the nearest multiple of significance. Ceiling.Math and Floor.Math are specialized versions of these functions, offering precise control over the rounding process.

Ceiling, Floor, Ceiling.Math, and Floor.Math in Excel:

In Excel, employing these functions is straightforward. Consider a dataset with decimal values representing product prices:

  1. Ceiling Function in Excel: =CEILING(A2, 1) This formula rounds up the value in cell A2 to the nearest multiple of 1.
  2. Floor Function in Excel: =FLOOR(A2, 1) This formula rounds down the value in cell A2 to the nearest multiple of 1.
  3. Ceiling.Math Function in Excel: =CEILING.MATH(A2, 1) This formula rounds up the value in cell A2 to the nearest multiple of 1, similar to the Ceiling function.
  4. Floor.Math Function in Excel: =FLOOR.MATH(A2, 1) This formula rounds down the value in cell A2 to the nearest multiple of 1, similar to the Floor function.

Ceiling, Floor, Ceiling.Math, and Floor.Math in Power Query:

In Power Query, these functions extend your data transformation capabilities:

  1. Ceiling Function in Power Query: =Table.TransformColumns(Source, {{"Price", each Number.RoundUp(_, 0), type number}}) This formula rounds up the ‘Price’ column to the nearest whole number in the Power Query editor.
  2. Floor Function in Power Query: =Table.TransformColumns(Source, {{"Quantity", each Number.RoundDown(_, 0), type number}}) This formula rounds down the ‘Quantity’ column to the nearest whole number in the Power Query editor.
  3. Ceiling.Math Function in Power Query: =Table.TransformColumns(Source, {{"Value", each Number.RoundUp(_, 1), type number}}) This formula rounds up the ‘Value’ column to the nearest multiple of 1 in the Power Query editor.
  4. Floor.Math Function in Power Query: =Table.TransformColumns(Source, {{"Amount", each Number.RoundDown(_, 1), type number}}) This formula rounds down the ‘Amount’ column to the nearest multiple of 1 in the Power Query editor.

Applications and Benefits:

Understanding how to apply Ceiling, Floor, Ceiling.Math, and Floor.Math functions in both Excel and Power Query empowers you with precise control over numerical data. Whether you’re dealing with financial calculations, inventory management, or scientific analysis, these functions ensure your numbers align perfectly with your specific requirements, providing accuracy and reliability in your calculations.

Conclusion:

Mastering the Ceiling, Floor, Ceiling.Math, and Floor.Math functions in both Excel and Power Query equips you with advanced tools for nuanced data manipulations. By following the examples provided in this guide, you can confidently round numbers up or down, ensuring your data is finely tuned for comprehensive analysis and well-informed decision-making. Embrace the power of these functions in both Excel and Power Query, and elevate your data processing capabilities to new heights. Happy analyzing!

Want to Unlock the Secrets in Your Data? #enterprisedna

Want to Unlock the Secrets in Your Data?

A picture is worth a thousand data points.

Facts or Fiction?

A good visualization distinguishes one from the other.

Numbers Don’t Lie

But they can mislead if presented wrong.

Visual Integrity

Ensure your visuals are as truthful as they are stunning.

Chart Choices

Line, bar, radar. Your choice could redefine your data.

The Human Element

Understand your audience for effective visualization.

Data’s Best Friend

Aim to become indispensable in your organization.

Lead with Visualization

Master the art at Enterprise DNA.

Why Just Good When You Can Be Great? #dataviz #datavisualization #enterprisedna

Why Just Good When You Can Be Great?

Turn your good visualizations into great ones. Find out how.

Do You Speak Data?

Let your data do the talking through visuals that make sense.

Death by Pie Chart

Some visuals can distort your message. Be careful.

Keys to Effective Visualization

Colors, scales, labels—every detail counts.

Size Matters

The scale of your visuals can amplify or dilute your insights.

Story Over Numbers

Your data tells a story. Make sure you’re narrating it well.

Take the Leap

Go beyond conventional graphs. Get creative.

Be a Data Artist

Bring your data to life. Learn how at Enterprise DNA.

Is Your Data Telling You a Story? #datatalks #dataanalysis #enterprisedna

Is Your Data Telling You a Story?

Your data holds secrets. Unlock them with the magic wand called data visualization.

Why Stare at Numbers?

Spreadsheets are great but visuals bring data to life. Ever wonder how?

Data Overwhelm

Staring at raw data feels like reading an ancient text. Confusing and overwhelming.

The Power of Visualization

With the right graphs, trends jump out at you. Insights become crystal clear.

Why Most Visualizations Fail

Not all graphs are created equal. Bad visuals mislead.

The Art of Picking the Right Graph

Bar, line, or scatter plot? The graph you choose can make or break your analysis.

Simplify Your Decision-Making

Good visuals remove guesswork. They steer you Continue reading “Is Your Data Telling You a Story? #datatalks #dataanalysis #enterprisedna”