Power Query: Month Mayhem – Part 7
14 August 2024
Welcome to our Power Query blog. Today, I begin to append the data I have transformed.
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-1723040031.png/a045b81795dadf324e30ffbef6816519.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-1723040285.png/6507f7485da064fb20655cad97937f29.jpg)
Last time, I created a summary version of my mapping query, which I called Column_Mapping_Master:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1723040304.png/96abdc6f6b6d237d8a2357fe04f9bc17.jpg)
I then created a new query, Mapping Merge which began as a ‘Merge as New’ between Column_Mapping and Column_Mapping_Master:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1723040321.png/858c4a94ed89ff9774e910519f21f2ca.jpg)
Today, I will append my data to produce the required format. Whilst I have been busy transforming the details and the month data, I haven’t forgotten about the interim report headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1723040348.png/308351d14358225f4818f07d4738ae05.jpg)
I will extract them from the Source Summary query. To do this, I start with a reference copy of Source Summary, which I initially call Report_Headings:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1723040372.png/b75bc82df05584600d37cbd59be06778.jpg)
It takes me only one step to get the headings; I need to filter on Column1 and select the null values:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1723040393.png/0da989e16c1e609ca494096cb89cccda.jpg)
This isolates the headings I need:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1723040411.png/1583f672a4fb00c95a08dd2936f3048a.jpg)
Next, I need to append more data to this query. I need the data from Mapping Merge. From the Home tab, I choose to ‘Append Queries’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1723040431.png/df72b52da27ef92d44282262ac3cf291.jpg)
This gives me all the headings at the top, so I need to sort my data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1723040459.png/d1441958d7d5178ce17378197208184d.jpg)
This is why I created the Index column – I sort ascending:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1723040478.png/e8511305144a431d71960a294b70b5a4.jpg)
This gives me the data and the interim headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1723040506.png/cedd2744e52282813b3fb320bca01c2c.jpg)
My transformations are almost done. Next time, I will append the top headings and prepare my data for loading.
Come back next time for more ways to use Power Query!