Please note javascript is required for full website functionality.

Blog

Power Query: Row Together Part 7

6 August 2025

Welcome to our Power Query blog.  This week, I complete a new row challenge.


Over the last few weeks, I have been looking at how to solve tasks involving the manipulation of data to form new rows.  In Part 5, I began a slightly different version of the challenge.  The data I am  transforming is in an Excel Table called SalesResults:

I have a list of amounts accrued by each Salesperson for our suppliers.  The task is to create a row for each Salesperson containing all the companies and the total amount, but to include the other rows in the Table. 

Last week, I transformed the  SalesResults query by sorting the data in ascending Salesperson order and creating an index before grouping the data.

I amended the M code to aggregate the Company rows correctly to get the total rows:

I formatted the Salesperson column to include the prefix "Total for ".

I added a half [0.5] to each Index, so that each total would be at the bottom of the correct group and not part of the next group.

I have the totals in step 'Added to Column' and I have the other rows in step 'Added Index'.  I need to put these steps together by performing an Append:

The help refers to appending to another query in the workbook, but I can append SalesResults to itself:

I choose 'OK' and view the M code created:

I ned to change the M code from:

= Table.Combine({#"Added to Column", #"Added to Column"})

to this:

= Table.Combine({#"Added to Column", #"Added Index"})

This appends the two [2] steps:

Now, I can sort the Index column in ascending order:

The total lines appear at the bottom of each section.  All that remains is to delete the Index column and load the data. 

Come back next time for more ways to use Power Query!

Newsletter