Get and Transform (Power Query) Tip – Group Results Without an Aggregate Column

I do some pretty advanced Power Query work (yes, I know they call it “Get and Transform”, but it will always be Power Query to me) and in order to remove duplicates, it is common to use the “Group By” feature. This has the unfortunate requirement of requiring you to select an aggregation for the grouping.

Using this method, you would need to then have an additional step where you would delete the aggregate column before continuing. Although this doesn’t sound like a big deal, if you do the group operations multiple times, it could get redundant and make the total number of steps grow unnecessarily.

So how do we group the result set without having to create an aggregate column?

Well, you can’t do this through the GUI, but you can do it through the “Advanced Editor”. If you’ve never used the Advanced Editor, this is really where the “Power” of “Power Query” comes out.

To do the grouping:

  1. First, pull in the data. For our example, we are pulling in from a SharePoint list titled “Product Sales” which stores product sale information. For our example, we are looking to get a unique listing of products.

Here’s our list:

SharePoint Demo List

Within Excel, we bring in the list by selecting Data Tab > From Other Sources > From SharePoint List:

Power Query - Query SharePoint List

Then type in the URL to your SharePoint list and click “OK”.

Power Query - Connect to SharePoint site

In the Navigator screen that appears, select the list you want to group:

Power Query - Select SharePoint List

Then in Excel, you will see the pull of the list in its raw form and a number of extra columns we don’t need.

Power Query - Raw Query

To only return the column to group (Title in our case), we select it and remove all other columns:

Power Query - Select Title Column

The resulting set looks like this:

Power Query - Select Single Column Results

  1. Do the grouping like you would with the aggregate column (say Count).

With this single column, we would select the “Group By” Action and select the column to group by (Title in our case), and click “OK”.

Power Query - Group By Screen

In the preview panel, you will see the grouped list with the aggregated column:

Power Query - Group By with Aggregation

  1. Open “Advanced Editor” and locate the step you just created.

Clicking on the “Advanced Editor”, you see the row that we just created. You’ll notice the reference to our aggregate column in the text ‘{“Count”, each Table.RowCount(_), type number}’

Power Query - Advanced Editor with Aggregation Code

  1. Remove the entry for the aggregate column and leave an empty set.

    We simply remove this text so that we have an empty set in the step:

Power Query - Advanced Editor with Aggregation Code Removed

  1. Re-run the query to see that the aggregate column is not created and what remains is a clean set without duplicates.

    After we re-run, there is no “Count” column and our Power Query code is clean.

Power Query - Grouped Rows without Aggregation

That’s all there is to it. Happy Power Querying (or Get and Transforming…. <grumble><grumble>) !

Leave a Reply

Your email address will not be published. Required fields are marked *