Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Easy fix for promoting headers when name can change issue in PowerQuery

When using PowerQuery and getting ‘Files from a folder’, there is a risk that your query will break if the files in the folder do not start with exactly the same file name as when you first created the PowerQuery (especially if you want to keep the column Source.Name). This happens when your query tries to ‘Use First Row as Headers’ but the file name has changed. Below an easy fix for promoting headers when name can change.

YouTube Promoting headers when name can change in PowerQuery

UPDATE: Note the YouTube video shows the deletion of the old Source Name column 1 step too late. It must be done before the Promote Headers Step as per the text below

Name of top file changes in folder

The issue relates to how Windows stores the files in a folder and having the order change e.g. August is placed in a folder but now it appears ahead of January.

As shown below in the PowerQuery window, at this point in the Query, when we promoted the headers, the header name became the name of the first file it saw in the folder i.e. CDE.xls .

promoting headers when name can change

This would be fine if we always knew that the file CDE would be in the folder and no other file will ever be sorted above it, so you will not be able to put the file ABC into the folder as it will automatically go to the top of the folder and break the PowerQuery.

So below, when ABC is now in the folder, the PowerQuery breaks because at this point it is looking for a column name called CDE but the column is now called ABC.

promoting headers when name can change

Create your own Source Name column and delete the original

The easiest way to address this, without complex M formula, is to create a new Custom Column that pulls through the source names you want to keep, but on the rows that contain the headers, you want to change the source file name to something generic, like Source Name!

In plain English we are going to tell PowerQuery to look at another column where we know the Header names won’t change (in the example below, column 2 will always have the words StoreName in the row which we want to use as headers). In those rows it will write the words “Source Name” but everywhere else it will pull whatever is in the original Source.Name.

You could use the Conditional Column tool as shown below or just write the IF formula.

promoting headers when name can change

The end result will look like this. The old Source.Name doesn’t change, but note that the new Source Name, on the row that we will eventually use as the headers, now has the words “Source Name” which will always appear there no matter which file is the first file in the folder.

promoting headers when name can change

Delete the original Source.Name column (it has done its job but will cause issues further down the line) and use the new Source Name column going forward.

Fix up the next steps to rather use the new column and your PowerQuery should now be immune to changes in file names and the resultant risk of sort order issues!

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Switch off PowerQuery Conditional Column wizard

Import all files in a folder into Excel

Begins with, Ends With, Contains missing in PowerQuery Filter

PowerQuery Online Course