Group Rows and Get Counts Using Power Query

Power Query lets you perform a series of steps to transform your Excel data. One of the steps it allows you to take is to group rows and get the counts of each group very easily. Let’s go through the steps in detail!

Key Takeaways

• Easily Summarize Data – Power Query allows you to group rows based on a specific column and perform calculations like counting, summing, or averaging.

• Group by One or Multiple Columns – You can group by a single column or multiple columns to create meaningful summaries.

• Count Occurrences with One Click – The Group By feature has a built-in Count Rows option that instantly counts occurrences in the dataset.

• Custom Aggregations for More Insights – Besides counting, you can apply custom aggregations like sum, average, or max/min on grouped data.

• Automate Grouping with Refreshable Queries – Once set up, Power Query automates the grouping process, so your data updates dynamically when refreshed.

How to Group Rows and Get Counts Using Power Query

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 

Excel 2013 & 2010:

 

STEP 3: This will open up the Power Query Editor.

We want to group this data by Country and show how many times each Country appeared in the table. (i.e. Australia appears 4 times in this table)

 

STEP 4: Within here you need to select Transform > Group By

 

STEP 5: Make sure to select Country for Group By, and select Count Rows for the Operation.

This will group your table by Country value, and count the number of occurrences of each country.

For example, the country of Australia appears 4 times in our table.

STEP 6: Now you will see your changes take place. And the data has now been grouped together.

 

STEP 7: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the new data.

You now have your new table with the counts of each country.

Frequently Asked Questions

How do I group rows in Power Query?
Select your data, go to Transform → Group By, choose the column to group by, and apply the desired aggregation (e.g., count, sum, average).

Can I group by multiple columns in Power Query?
Yes! In the Group By window, click Add Grouping to include multiple columns for grouping.

How do I count the number of rows in each group?
In the Group By window, choose All Rows as the operation, then add a Count Rows column to get the number of occurrences.

Can I perform additional calculations besides counting?
Yes! Power Query allows you to sum, average, find the min/max, or apply custom calculations while grouping rows.

Will my grouping update when new data is added?
Yes! Power Query saves your transformations, so when you refresh, the grouped data updates automatically with new entries.