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.
Table of contents
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 .
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.
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.
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.
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.
Related
Switch off PowerQuery Conditional Column wizard
Import all files in a folder into Excel
Begins with, Ends With, Contains missing in PowerQuery Filter