Power BI – Group rows

• The Group Rows feature in Power Query Editor allows you to group together rows in your data based on one or more columns. This can be useful for summarizing data, aggregating values, and performing other types of calculations and analysis.
• First, select the rows you want to group together using the Group Rows feature. Then, select the Group By button in the ribbon. This will open a dialog box where you can specify the columns you want to group by and any additional aggregation functions you want to perform, such as summing or averaging values.
• Once you have defined your grouping options, click OK to apply the changes. The grouped data will be displayed in a new table in the Power Query Editor.
• You can further refine the grouped data by adding additional transformations or calculations. For example, you can create new columns that calculate the difference between different groups, apply additional filters or sorting to the grouped data.
• Overall, the Group Rows feature in Power Query Editor provides a powerful way to summarize and analyze your data, making it a valuable tool for data professionals and business analysts.

Consider an education enrollment dataset is grouped in this example. The data comes from an Excel workbook. Power Query Editor is used to get just the columns you need, rename the table, and make a few other changes.

Let’s see how many agencies each state has. In the Transform tab or the Home tab of the ribbon, select the Group By button in the State Abbr column. (School districts, regional service districts, and others can be included.) Both tabs offer grouping options.

A Group By dialog box appears. Power Query Editor creates a new column when it groups rows. There are several ways to adjust the Group By operation.

• Columns can be grouped using the unlabeled dropdown list. In Power Query Editor, this value defaults to the selected column, but you can change it to any column in the table.
• New column name: According to the operation it applies to the grouped column, Power Query Editor suggests the name of the new column. However, you can name the new column whatever you like.
• Operation: Power Query Editor lets you choose the operation to be applied, such as Sum, Median, or Count Distinct Rows. Count Rows is the default value.
• Add grouping and Add aggregation: You can only access these buttons if you select the Advanced option. Using these buttons, you can group many columns (Group By actions) and create several aggregations in one operation. Power Query Editor creates a new column that operates on multiple columns based on your selections in this dialog box.

To add more groupings or aggregations to a Group By operation, select Add grouping or Add aggregation. To remove a grouping or aggregation, select the ellipsis (…) to the right of the row, and then click Delete.