Rename a Column based on the Column Position

[Shawkins566]

I have pulled multiple files into PowerBI from a folder, the files are all .txt files so getting them into a usable format is a pain, but I've got it working, the only thing is, my first column says "Source.Name" which gives me the file name for each of the file rows (which I need because that contains the date), then the rest of the columns say Column 1, Column 2, Column 3 and so on.  The first row contains the headers so I promote the first row, but that means that the first row file name replaces "Source.Name" as the header.  I renamed this as Date but when I refresh the data, it breaks because if that file doesn't run first or is missing from the folder it's trying to change "21.03.24 Group Summary Data" to "Date", but saying "21.03.24 Group Summary Data" is missing.

I'm looking to find out if I can replace the column header based on the table position so that no matter what is in the first column header it'll change to "Date"

Anyone any ideas?

Many thanks

Sarah

[Zang_Mi · Accepted solution]

Hello,

There is an idea to solve your problem:

1. Make the query capturing automatically the name of the first column
2. Save it as variable
3. Use this variable for the renaming

Let's say that we want to rename the first column of the following table by replacing the text '202400.xlsx' by 'Date'.

By doing it manually, Power BI will add the following step to the query, where the original column name is a fixed text. 

We can replace this text by a variable that captures the first column name, which may change.

In this new query, the yellow line shows how can we get all the column names as a list with the function Table.ColumnNames, and the expression {0} to get the first item from the list (in that case, the first column name).

Then, in the last step, we can just replace the fixed text by this variable to make the query works in the same way, but able to update the name automatically even though the input files change.

Regards

[Shawkins566]

Hi, I'm not quite understanding how to enter this into the PowerBI

[Zang_Mi]

Hello, you can edit the query using Power Query Advanced Editor in Power BI.