Renaming column when source data changes|Power Query|Excel Forum|My Online Training Hub

Hi,

I have created a query that pulls the data from another Excel file. The query picks up various worksheets from the file. It then inserts the worksheet names as the first column in the table when the data is returned.

I want the query to work on different files where the worksheet names will be different. The data in the worksheets is not in tables and I have to do some filtering to remove rows I don't need etc. When I have done this I then promote the first row as headers to get my column names. The issue I have is that when I promote the headers the first column title becomes the name of the first worksheet. This means that if I want it to work with other files where the worksheet names are different it will give me an error at the point when I try and rename this column.

I have attached two files. The first, "PQ_Use_Sheet_Name_Source_File.xlsx", is the source data. You can see that it contains three worksheets named "10-North", "20 - South" and "30 - East".

The second file, "PQ_Use_Sheet_Name_Result_File.xlsx", is the file containing the query. The query works ok (you will need to change the source file reference to your own file location) but as you can see when the column is renamed it is hard coded with "10 - North"

= Table.RenameColumns(#"Promoted Headers",{{"10 - North", "Team"}})

Is there a way around this so it will work with other files regardless of what the worksheet names are?

Thanks

Bax