Power Query: Row Together Part 5

Welcome to our Power Query blog.  This week, I begin 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.  This week, I have a slightly different version of the challenge.  The data I will be 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 for each Company they have sold to detailing the total Amount, viz.

I begin by extracting the Table into Power Query by selecting the Table and using the right-click menu to access the option 'Get Data from Table/Range…'.

This creates a new query SalesResults:

I will keep the 'Changed Type' step as I will be summing the Amount.  The key to ensuring that the total line is in the correct place is to create an index.  To begin, I need to make sure that my data is in Salesperson order:

Now I can add an 'Index column' from the 'Add Column' tab:

I choose to create an index that starts 'From 1', although for this example it doesn't matter where I start from.  Having created my index, the next step is to group the data to create the total line for each salesperson.  I access the 'Group By' option from the Home tab.

I have used the Advanced form of the 'Group By' dialog.  I am grouping by Salesperson, and I would like new columns grouping Company and Amount.  I also need the maximum Index so that I can put the total row at the bottom of each Salesperson group.  I click OK to group my data:

This issue is familiar from Part 1.  Since I am summing Company, I need to change the M code for the 'Grouped Rows' step.

I need to change the M code from this:

to this:

I now have the total rows:

There are several more changes I need to make to the total rows, which is where I will continue next week.

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