Power Query: Month Mayhem – Part 6
7 August 2024
Welcome to our Power Query blog. Today, I continue mapping the data.
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-1722333396.png/07d2396a8cbe0dcf0c6216decc0d6948.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-1722333415.png/4ff41a7a511bf844c47ac9c5ba146bb1.jpg)
Last time, I created a mapping query, which I called Column_Mapping:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1722333450.png/249c779a5f6af73b9edf151e1dd321ba.jpg)
I will continue mapping my data. Whilst I can use the query I have created for the data, I need a more concise mapping tool for the months. I take a reference copy of Column_Mapping, which I call Column_Mapping_Master.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1722333471.png/73f8e8cb248a1b6390e06f3cb87e6f2f.jpg)
I am going to filter on Months_Unpivoted.Attribute to remove null values:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1722333486.png/b94c16f9260a431410201d4520b3a402.jpg)
This reduces the number of rows to the months where data will appear:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1722333506.png/5d5dba0e421920945de0db105714eb0b.jpg)
Column_Mapping_Master is complete. It is time to begin rebuilding my data to create the new table. I start in Column_Mapping, where I am going to ‘Merge as New’ with Column_Mapping_Master:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1722333527.png/8e698463e5abc51db491ceaa4078278b.jpg)
I merge on Section Index and Attribute, being careful to select the columns in the same order. I call the new query Mapping Merge:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1722333544.png/88d8e385a216b199594c0f77d57261a6.jpg)
I expand Column_Mapping_Master and select Mapping:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1722333622.png/96bb630065abd3b53b047aa1dfe0a76e.jpg)
Since there is already a column with the same name, I keep the prefix option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1722338034.png/668923604d4980370b7d8f6ea38b9dfe.jpg)
I select the ‘Choose Columns’ option from the Home tab, and select Column1, Index,Value and Column_Mapping_Master.Mapping:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1722338062.png/8302809a6d0b520704ddf4f067085e34.jpg)
This gives me four [4] columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1722338093.png/152935c117410798921efa815a2bf418.jpg)
I want to sort on the column number, but if I sort on the text, ‘Column11’ would come before ‘Column2’. I need to get the digits from the Column_Mapping_Master.Mapping column, whilst keeping the column, so I use Extract from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1722338119.png/55d0d8a8849e0b966c38b30619094204.jpg)
I extract the data after ‘Column’. This gives me a new column, which I can change to whole numbers and sort on:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1722338142.png/edecf28c0b87e51c0202c3357496835c.jpg)
I can now delete the new column and select Column_Mapping_Master.Mapping and then Value and choose to pivot these columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1722338165.png/279327d94b8faa73de7e272d933c7ae0.jpg)
I select the ‘Advanced options’ and choose ‘Don’t Aggregate’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1722338194.png/41cbdbbeb70ca9f33bddb8b7aa8ae549.jpg)
This query is complete; I will continue transforming my data next time.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1722338213.png/da4ffd396af9ca2cb5da8ccc07eb50d2.jpg)
Come back next time for more ways to use Power Query!