I recently had a problem where I was using the query builder in Power BI to extract data from all files in a folder. When some new files were added to the folder my query broke.
It turns out that the issue was with my promote headers step. Because the first file the query opened changed, the header that was promoted in the filename column was different, throwing off all my subsequent query steps. I needed a way of referencing the first column without referencing it by name.
The solution is actually pretty simple, but it requires editing the M code rather than using functions accessible from the ribbon.
My next step after promoting the headers was to rename some of them, including the filename column as that had been replaced with the filename of the first file.
This issue was that the column was no longer called “201701.xlsx” as the first file had changed. The column I’m after is the first column in the table so I just need to create a bit of code that will dynamically select the first column.
There is a Table.ColumnNames function that will create a list of column names of a table. I can then use the List.First function on that list to grab the name of the first column. So all I need to do is replace the “201701.xlsx” argument with the below: