Power Query Function In Power BI: Group By Function

This tutorial is about a power query function in Power BI called Group By. It’s an underrated feature in Power BI that lets you solve common problems in the simplest way possible. You can watch the full video of this tutorial at the bottom of this blog.

Group By function is the best option when you have a series of attributes that you want to group and create an aggregate.

Sales Table Example

This is a sample report using DAX calculations. It has circular redundancy and filter context problems.

The table was grouped by Customer, Year and Month, and Sum of Sales Amount.

The Total Sales amount is then placed on each row of the group.

Using The Power Query Function Group By

To replicate this using the Group By function, open Power Query and click Group By.

Once it’s open, click Advanced and start grouping attributes.

First, group Customer and Year and Month. In this case, name the column Total Sales Amount Grouped. Next, change the operation to Sum and the column to Sales Amount.

power query function

You can see that it generated a column. However, the data is incomplete because it only has one row per customer.

So, go back to the Group By function step to fix this.

At the bottom of the function, click Add Aggregation and name it AllData.

For the Operation, choose All Rows. This operation provides a way to return all of the rows that were in the original data with the aggregate operator applied to it. Next, move the parameters up and click OK.

power query function

This creates a new column in the table.

Now, remove the Customer and Year and Month columns and expand the AllData column.

You now have the same table set up as in the example.

So, instead of using complex DAX and circular references, you can use Group By to obtain your desired results easily.

Basketball Record Example

In this example, we have used the finals records of the Chicago Bulls basketball team.

These are the best seven-game series each year.

Applying The Power Query Function Group By

Using the Group By function, you must determine how many series wins they had that was 4 out of 7.

If you use DAX codes, you may end up using this measure to identify the series wins.

power query function

You can see that it uses nested FILTER commands, nested iterators, and some functions such as GENERATE, ADDCOLUMNS.

To do it in a power query, take out the wins and losses and convert them into a count measure.

Go to Add columns and click Column From Examples. Then, name the column Count. After that, assign 0 value for the losses and 1 for the wins.

power query function

The next step is to figure out the deciding game in each series which is always the last game played.

So, go to Group By and click Advanced. Group the Year and set the parameters.

Input AllData for the column name and set the Operation to All Rows. Next, click Add aggregation and call it Deciding Game. Set the Operation to Max and the Column to Games.

power query function

Once the columns are generated, expand the AllData column.

You now have the data for the deciding game.

The last thing you need to do is to create another column that returns the value of the Count column for the deciding game of the series.

To do that, click Conditional Column and name it Series Win Count. Set the parameter to generate the Count column if the Games column is equal to the Deciding Game column. If not, it should return to 0.

power query function

Once done, go to the Home tab and click Close & Apply.

This is the table that was created using complex DAX measures.

Recreating The Basketball Record Table

Using this analysis, you can recreate the table.

Take the Year and Series Win Count columns into the dashboard and turn them into a table.

Make sure that the Year doesn’t summarize and the format of the Series Win Count as a whole number.

Next, get the total of the Series Win Count. Click the dropdown arrow of the column and choose Sum.

power query function

You can see that it has the same results without using a single DAX function.

In these two examples, you can see how the Group By function works when you combine it with the ability to either return the aggregate or return all rows.

It gives you the flexibility to tackle a wide range of group problems.

Conclusion

***** Related Links *****
Effective Ways To Use The GROUPBY Function In Power BI
Organizing Your Queries Into Groups In Power BI
Power BI Calculation Groups – DAX Tutorial

Group By is a highly useful power query function in Power BI. It has great potential in creating reports and visualizations.

If using DAX functions and codes is too complex, you can use the Group By function as an alternative. It helps generate valuable data for complex problems easily.

In this tutorial, we have shown how you can maximize the potential of the Group By function and apply it to your own reports.

Brian

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More