Yesterday, I posted a new Power Query Challenge, and in this post I'm going to show my solution to challenge 4.  You can pick up this solution as well as solutions created by the community in this thread of the Excelguru Forum.  And as a quick note - the very first answer posted there is much slicker than what I've written up here... but hopefully some tricks here will still help you up your Power Query game. 😉

Background on the Solution to Challenge 4

The original issue was to create a header from different rows in the data.  You can read the full reason for this in the original blog post, but basically put, I needed to convert this:

To this:

On a dynamic basis so that I could easily repoint the data set to this one:

And return this:

The major wrinkles in creating a Solution to Challenge 4

The biggest issues I had to deal with were these:

• I couldn't just promote row 1 to headers and rename the first column.  Why?  Because Power Query would have hard coded "admin" in the first set, which would have triggered an error when pointed to Sales
• I couldn't just rename the annual columns.  Why?  Because the years change, so Column2 is 2015 in one data set and 2016 in the other
• After promoting the header row, I couldn't declare data types.  Why?  Because the column names get hard coded, meaning that the code would trigger an error when it couldn't find 2015 or 2018 in the data sets.

Fixing these on a static basis is easy, it's wanting it to be dynamic that is the issue.

Creating the Solution to Challenge 4

So how did I accomplish the goal?  I started with the workbook I posted in the forum, then took these steps.

As described in the original post, I edited the MakeMyHeaders query in order to do the work.  I then:

• Demoted Headers (Home -> Use First Row as Headers -> Use Headers as First Row)
• I then right clicked the "Changed Type" step in the Applied Steps area and renamed it to "AllData" (with no space)

This basically gives me an easy-to-come-back-to point of reference for later.  And since I did not include a space, it's super easy to type.  (If I left a space in there, it would be #"All Data" instead.)

Next, I needed to create my header row which involved:

• Keeping the top 2 rows only (Home -> Keep Top Rows -> 2)
• Right clicking and renaming the step "HeaderBase"

I then replaced the department name (Admin) with "Name". The trick was to make this dynamic, which involved a couple of steps.

• Right click Column1 and do a replacement as follows:

• Then, in the formula bar, remove the " characters around both HeaderBase[Column1]{1} and HeaderBase[Column1]{0}

So what was that all about?  It's replacing the value in the 2nd row - row {1} - with the value from the first row - row {0}.  And it's completely dynamic!  (For reference, the reason I reduced this to only 2 rows was that if I tried this when all rows were showing, the departments on the data rows would be lost.)

I then went and removed the Top 1 row, leaving me with this:

So far so good.  Now I just needed to add back the original data.  To do that:

• I went to Home -> Append and appended the MakeMyHeaders query (yes, I appended it to itself)
• I then modified the formula from:

= Table.Combine({#"Removed Top Rows", #"Removed Top Rows"})

• To

= Table.Combine({#"Removed Top Rows", AllData})

Which left this:

The final cleanup took a few more steps:

• Promote First Row to Headers
• Delete the automatically created Changed Type step (so we don't lock down the years in the code)
• Remove Top Rows -> Top 2 Rows (to get ride of the rows I used to create the Header)

And we're done!

Proving that the Solution to Challenge 4 works

Naturally, loading this query to a table will show that it works.  But to really prove it out:

• Edit the MakeMyHeaders query
• Select the Source step
• Change the formula to =Sales
• Go to Home -> Close & Load

You'll see that it updates nicely with the updated headers

Why the solution to Challenge 4 even matters

If you ever decide to combine Excel worksheets, and want to hold on to the worksheet name as well as the data, you'll need this technique!