Power Query: A Summary Taster

Welcome to our Power Query blog. Today I look at a way of extracting data from groupings.

Following on from last week’s blog on grouping in Power Query, despite having simplified my data into groupings, I can still access data that makes up the group to provide more information.

At the end of that article, I had made some changes to a query of mine, ‘ACCT_Order_Charges_with_Group’. I will be making more changes, but to avoid causing problems to the grouping query, I will create a copy of the query. I already have taken the grouping a step further from the end of the last blog to show which item group is the best seller:

The easiest way to make a copy of my query is in the Excel workbook: the ‘Workbook Queries’ pane should be displayed (if it’s not use the ‘Show Pane’ option on the ‘POWER QUERY’ tab, or in the ‘Get and Transform’ section). Right clicking my query gives me the option to ‘Duplicate’ it.

I duplicate my query, and give it a new name ‘Order_Charges_Summarising’:

Now, suppose I was at this stage and I needed to find out the most expensive selling item in each item group. I need to see the data behind the grouping, and to do this I need to modify the grouping step. I click on the gear icon next to the ‘Grouped Rows’ step and add a column (‘aggregation’) ‘Details’ which will act on all rows:

Clicking ‘OK’ gives me a new column, which contains tables. Clicking on the word ‘Table’ has two effects. Firstly, I get a warning because other steps exist after my ‘Grouped Rows’ step and secondly, I see the contents of my table viz.

The warning reminds me to remove the last two steps, which I can do since they were only introduced to tidy up my grouped data.  Having removed them, I need to find a way to get at the data in the tables so that I can work out which item was the most expensive selling item in each item group.  I am going to need a custom column, and I am going to use a record.  The formula I use is:

Clicking on any record will show me what is in it (and creates a step):

Since I have chosen to use a record, what is shown is not just the most expensive item in the group, but also the other data associated with that item, in effect a row within a row. I delete the last step and go back to the ‘Added Custom’ step. I then choose to use the expand icon next to the MaxRecord column name to expand all the records:

I only want the ‘Amount’ and ‘Description’ and I’ll keep the column names for now.

What I have is the description of the most expensive item in each group, and the price for that item, so I change the names accordingly, remove my Details column and reorder ready to load.

This is a simple example of getting data from records when the data has been grouped.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!