Power Query: Month Mayhem – Part 2

Welcome to our Power Query blog.  Today, I continue looking at how to index my data to keep everything in order.

My salespeople are back from their break and I have more reports to construct.  I have a report with a list of the clients they have been working with each month:

I would like to display the amount details in the salesperson sections but aligned to the relevant month at the top of the page:

Last time, I extracted the data to a new workbook and began transforming the data, viz.

I created two indices, Source Full Index indicates the position of the row in the report, and Section Index will be used to keep the sub-sections together.  This time, I will recombine the data, and show how this will help me to identify the data in each section.

I choose to merge my query with itself, using the ‘Merge Queries’ option on the Home tab:

In the dialog, I select ‘Sheet1(Current)’ to merge with the same query.  

I select the column Source Full Index on both instances of the query and choose a ‘Left Outer’ join:

Having created the step, I have the following M code:

I want to change the first table to be the step ‘Renamed Columns’ (i.e. when I renamed the first index) instead of ‘Renamed Columns1’:

This gives me the following result:

I click on the icon next to the heading in the column Renamed Columns1, and choose which data to expand:

I only need the column Section Index.  I also notice that my data is no longer in ascending Source Full Index order:

I sort ascending on Source Full Index:

Now I can see that the report sub-heading sections may be grouped together by filling down Section Index.  I can achieve this by right-clicking on the heading of Section Index and choosing Fill and then Down:

I may now remove the column Source Full Index:

From this point, I need to treat the rows with amounts differently from the rest of the data.  I will be using this query as a building block.  I rename it to Source Data to make its purpose clearer:

Next time, I will take a reference copy of Source Data and continue transforming the data.

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