Power Query: Month Mayhem – Part 5

Welcome to our Power Query blog.  Today, I create mapping queries which I will be using to move 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:

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

Last time, I created the queries I need for the amount data.  As a reminder, I first created the Amounts query:

then I took a reference copy of Amounts, to create Amounts_Unpivoted:

This time, I will consider how I am going to move the data by creating mapping queries.

I am going to merge the amount and month data.  I start in the Amounts_Unpivoted query, and I choose to ‘Merge Queries as New’ from the ‘Merges Queries’ dropdown on the Home tab:

In the dialog, I choose to merge with the Months_Unpivoted query:

This will only pick up the month data from the Amounts_Unpivoted rows.  I click OK and rename the new query ‘Column_Mapping’:

I use the expand icon next to the Months_Unpivoted column header to choose which data to extract:

Since I am looking at what columns to map to, I need the Attribute column.   I already have a column with this name, so I choose to ‘Use original column name as prefix’: 

I can see that sometimes the column mapping appears in Attribute, and when it doesn’t, it appears in Months_Unpivoted.Attribute.  I need to create a ‘Conditional Column’ from the ‘Add Column’ tab that will either use the value in Months_Unpivoted.Attribute, or if that is null, it will use Attribute:

I have called the new column Mapping. 

Next time, I will continue mapping my data.

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