Power Query: Month Mayhem – Part 8
21 August 2024
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1723041066.png/3741291297de31a01581248852cb3eb9.jpg)
I would like to display the amount details in the salesperson sections, but aligned to the relevant month at the top of the page:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1723041082.png/f5f3bd2d0a70b78c5ce3b05ae83a4a23.jpg)
Last time, I extracted the interim report headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1723041100.png/c6bbe9a7656141f6972f8c364cecb305.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1723041118.png/8223c0356cf0fdc29ce59c099d464e15.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1723041138.png/cb24d1a7e07c660dcf5d00fdd556c7a9.jpg)
I may use the ‘Two tables’ format, and make sure that I put Months first.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1723041157.png/348e4da08cab3e00d2be2425a87af88e.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1723041176.png/088b83b335e19e326eac2b0038fe1564.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1723041191.png/1b4813db37cfad921372a1c10d70b05a.jpg)
Since some of the columns are a mixture of numbers and text, the ‘Changed Type’ step leaves most of them as data type ‘Any’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1723041213.png/e7fa361665b0d68f5879b6797410f499.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1723041234.png/273325ef5029df73c8d90e954fb9210a.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1723041266.png/095c5dc89d1702a8d568f0cc57082196.jpg)
I initially choose to set all the queries to ‘Connection Only’ as I only want to load Report Output.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1723041293.png/b5cf8b9339fd686935a24476119c24f5.jpg)
Having done this, I select the Report Output query, and right-click to change the ‘Load To…’ settings:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1723041340.png/7d5acbd0d8e8de4ad8ed62c91850f639.jpg)
I choose to load to a Table on the ‘Existing worksheet’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1723041544.png/5998500d53c2117dc6894fdec9d101ed.jpg)
I click ‘OK’ and the data is loaded:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1723043129.png/bcb307d1fc674a9b249fe182b192381f.jpg)
I can remove the filters in the Table tab if I wish:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1723043154.png/f383e24e186e5afd17b89afd7efdb635.jpg)
My query is now complete.
Come back next time for more ways to use Power Query!