How to rename headers using a loop in Power BI?

Question

I have a situation where I need to rename headers based on a table with the old name (HeadersTranslated[HeaderList]), the new name (HeadersTranslated[HeaderEnglish]) and an indeks number (HeadersTranslated[Indeks]).

I am therefore trying to loop through the indeks numbers and do a Rename for each.

However I cannot seem to get it working for more than 1 column at a time (by replacing StartTheLoop(HeadersTranslated[Indeks]) with StartTheLoop(0) e.g. in below:

let
   Source = Data_Cyrillic,
   #"Promoted Headers" = Table.PromoteHeaders(Source,[PromoteAllScalars=true]),
   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type number}, ... , {"Column52", type number}}),

   StartTheLoop = (MyCounter)=>

   let     
       #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{HeadersTranslated[HeaderList]{MyCounter}, HeadersTranslated[HeaderEnglish]{MyCounter}}})
   in
       #"Renamed Columns"
in
   StartTheLoop(HeadersTranslated[Indeks])

Answer 1

This doesn't use a loop or an index but I believe it solves your real concern.

If I start with this as my table with the columns that I want to rename--I'll call it Table1:

And this as my table with the list of new column names I want to use--I'll call this table NewColumnNames:

Then, working in Table1, I can double-click the first column title and rename it anything--I'll type Test:

Then, in the formula bar, replace this highlighted part, which is a list of the column name I'm replacing ("Column1") and the name I'm replacing it with ("Test")...

...with List.Zip({Table.ColumnNames(Source),NewColumnNames[NewColumnNames]}). This change replaces all the original column names with the column names from then NewColumnNames list (column) in the NewColumnNames table.

The List.Zip function zips, like a zipper (not like a file compressor). It weaves list items together sort of like the teeth of a zipper. In this case, it takes the first column name, "Column1" (which it pulls from the list derived by the function Table.ColumnNames(Source)), then the first NewColumnName, "New Column1" (which it pulls from the table column NewColumnNames[NewColumnNames]) and puts them in a new list. Then it moves onto the second column name, and then the second NewColumnName, and so forth to create a list of lists. The list it creates is basically: {{"Column1", "New Column1"}, {"Column2", "New Column2"}, {"Column3", "New Column3"}, {"Column4", "New Column4"}}.

Answer 2

More generically, if you want to have Table1 with column header replacements for Table2 you can use this code. In this version, the row order between Table1 and Table2 column headers do not have to match, nor does there need to be a corresponding match for each row in Table1 , so you can pre-populate column header replacements that might not yet be needed

let
//Table1 is two column table with columns titles Old and New
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ReplacementTable = Table.TransformColumnTypes(Source,{{"Old", type text}, {"New", type text}}),
//Table2 is any table where column titles will be replaced using Table1
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
DemoteHeaders = Table.DemoteHeaders(Source2),
HeaderTransposed =Table.FromList(Table.ColumnNames(Source2)),
#"Merged Queries" = Table.NestedJoin(HeaderTransposed,{"Column1"},ReplacementTable ,{"Old"},"Headers",JoinKind.LeftOuter),
#"Expanded Headers" = Table.ExpandTableColumn(#"Merged Queries", "Headers", {"New"}, {"Headers.New"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Headers",{"Column1"}),
HeaderTransposed2 = Table.Transpose(#"Removed Columns"),
CombinedTables = Table.Combine({HeaderTransposed2,DemoteHeaders}),
PromoteHeaders = Table.PromoteHeaders(CombinedTables),
RemoveFirstRowFormerHeaders = Table.Skip(PromoteHeaders,1)
in RemoveFirstRowFormerHeaders