Power Query: Month Mayhem – Part 8

Welcome to our Power Query blog.  Today, I complete the challenge.

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 interim report headings.  

I extracted them from the Source Summary query into a new query which I called Report Headings and then appended Mapping Merge to get the body of the query that I will be loading to the workbook:

My transformations are almost done.  I still need to add the top heading and fully prepare my data for loading.  I need to be able to control the order that the data is appended in, as I want to add this query to the top header (Months).  I could either start from Months, and ‘Append Queries’ from the Home tab,  or I can start from Report Headings and choose to ‘Append Queries as New’ from the ‘Append Queries’ dropdown on the Home tab.   I choose the latter option to create a new query which I will be loading to the workbook. 

I may use the ‘Two tables’ format, and make sure that I put Months first.

I can see that my new query, called Report Output, is already looking promising. I just need to remove the indexes I created.  I select the column headings of Section Index and Index whilst holding down the CTRL key:

I right-click and choose to ‘Remove Columns’.  The final transformation is to promote the headers, which I can do from the Home tab or the Transform tab:

Since some of the columns are a mixture of numbers and text, the  ‘Changed Type’ step leaves most of them as data type ‘Any’.

I select one of the headers and use CTRL + A to select them all.  On the Transform tab, I change ‘Data Type’ to ‘Text’ for all the columns:

This is incorporated by Power Query into the existing ‘Changed Type’ step.  My data is ready for me to ‘Close & Load To…’ from the Home tab:

I initially choose to set all the queries to ‘Connection Only’ as I only want to load Report Output.

Having done this, I select the Report Output query, and right-click to change the ‘Load To…’ settings:

I choose to load to a Table on the ‘Existing worksheet’:

I click ‘OK’ and the data is loaded:

I can remove the filters in the Table tab if I wish:

My query is now complete. 

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