Group By creates a summary the input table grouped by the specified columns. It really improves report performance.
Power BI GroupBy function in DAX allows us to groups data based on given columns. You also can use Power Query Editor for Grouping the data.
Lets go through the steps as given below.
Go to Ribbon bar, click on Power Query Editor
Once you click a Power Query Editor window opens, on the left side you can see a available dataset, or you can also call it table lists, select the dataset that you want to use for Group By.
Here you can see we have one dataset Global-SuperStore.
Now take a copy of dataset for performing Group By, otherwise all the changes will be done to current dataset.
If you do not want to create a separate summarized table, you can use actual dataset.
Right click on Dataset – Global-Superstore, then Click on Reference in context menu.
Once you click on Reference, It creates a new dataset that is copy of Global-Superstore as shown in below screenshot.
You can provide a name for dataset, named it as ‘Group By Category’ as it is to be used to store summarized data that would be a Total Sum of Quantity By Category.
As we have a separate dataset named as ‘Group By Category’, Now time to summarized the data using Group By.
Below screenshot you can see category and quantity in dataset, here Category is used to Group By and Quantity column is used in aggregation.
Lets Group By the Data, Click on Transform tab, then click on Group By icon button.
Once you click on Group By icon button, a Group BY window opens.
Which gives you option to select column to group by and operation for aggregation.
By default a Group By option is set to basic that means you want to use single column to Group By and single column for aggregation, if you select Advance option then it gives you ability to add multiple columns to Group By also you can add multiple columns for aggregation.
Lets provide a columns to group by and columns for aggregation as per requirement.
As we want to summarize the data based on category so selected a Category column to Group By and Quantity for aggregation.
Specified a name for new column Total Sum Of Quantity that is aggregated value of quantity for individual Category.
Once you click on Ok button. You can see, it immediately groups rows based on category and aggregates the total sum of quantity.
Now click on Close & Apply tab, To save your pending changes and close the query editor.
You can see, the new summarized table – Group By Category in Fields Pane that store the groups of rows based on Total sum of Quantity by Category .
Now you can use this summarized table to load data on visuals as shown in below screenshot.
Group By on Multiple Columns
So far we have seen group by on single column, Lets apply Group By on multiped columns.
Lets repeat the same steps quickly as we did just above, open power query editor window, select original dataset and take a copy of dataset, right click on dataset then select Reference from context menu, and named the new dataset as ‘Group By Category and Subcategory’.
As this time we are going to summarized the data to get Total Sum of Quantity By Category and SubCategory.
Click on Transform tab, then click on Group By Icon Button.
A Group By window opens, select Advance option as this time you want grouping on multiple columns.
Finally, we have selected columns that is to be included in Group By and Aggregation as shown in below screenshot.
Once you click on Ok button, you can see it groups the data as we want to see.
Now click on Close & Apply icon button, To save the final changes and close the power query editor.
Lets load the data on visuals to display Total sum of quantity by Category and SubCategory.
Also Read..
Create Index column using a Power Query
