Referencing the first column in Power Query

Indirectly reference a column to stop your queries breaking

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.

Table.RenameColumns(#"Promoted Headers",{{"201701.xlsx","Name"},...})

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:

List.First(Table.ColumnNames(#"Promoted Headers"))

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s