Forum Discussion
Conditional change to column name? - inconsistent source naming
Final solution --
I removed this clause from the code:
each Text.Contains(_, "ChangeThisSubstring"))),
This is the condition that was causing the failure, since my source data didn't always contain any instances of "ChangeThisSubstring". With the condition removed, this meant that my "#Added Custom" column name replacement table now had a line for every column in my data, not just the columns that needed replacing -- but for other columns, the "new" name was the same as the old name since the Text.Replace rules didn't affect them.
Then, realizing that {0} was an index for which column to replace, I hardcoded the column I wanted to update using the index for that column:
Rename = Table.RenameColumns(#"Promoted Headers", Record.ToList(Table.ToRecords(#"Added Custom"){8}))
Now, the name for the index is always "changed" to the Text.Replace version every time data is loaded. If a column name is needed, it's changed to the updated version. If it's already the updated version, it just changes to a copy of the original name.
Yes, there are Power Query functions to list column names.
https://docs.microsoft.com/en-us/powerquery-m/table-columnnames
You can evaluate the list and then decide if any of them need renaming.
You also want to use Table.SelectColumns rather than Table.RemoveColumns when choosing what to keep.
Hi, I have a partial solution based on your comment and on this thread: https://community.powerbi.com/t5/Desktop/Rename-Columns-with-IF-formula/td-p/325573
However, this solution only works when the column I want to rename is present in the source table. What if I need to consume an earlier version of the source data where no rename is necessary? Basically, my case is that I have to swap between different versions of the underlying Excel data, and they changed the name of the column for recent tables. I think I need to check whether the #"Added Custom" table is null (in the code below), and if it is, I need the code to skip the subsequent Rename step.
Here's my code:
let
Source = Excel.Workbook(File.Contents(FolderPathParameter&FileParamater), null, true),
#"BA Level_Sheet" = Source{[Item="SourceExcelTab",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"SourceExcelTab", [PromoteAllScalars=true]),
#"Get Columns Names" = Table.FromList(List.Select(Table.ColumnNames(#"Promoted Headers"),each Text.Contains(_, "ChangeThisSubstring"))),
#"Added Custom" = Table.AddColumn(#"Get Columns Names", "Custom", each Text.Replace([Column1],"ChangeThisSubstring","NewSubstring") ),
Rename = Table.RenameColumns(#"Promoted Headers", Record.ToList(Table.ToRecords(#"Added Custom"){0}))
in
Rename
two options
1. use the Table.Columnnames list to find out if a particular column is present or not.
2. Familiarize yourself with the try ... otherwise ... concept in Power Query. Then brute force your way through the rename.
sounds promising, thank you. I will look at this.
Learning about try ... otherwise will definitely be helpful. FYI that there is an optional paramter too in rename columns called MissingField.Ignore that may also be useful (i.e., no error if you try to rename a column that isn't there).
https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Final solution --
I removed this clause from the code:
each Text.Contains(_, "ChangeThisSubstring"))),
This is the condition that was causing the failure, since my source data didn't always contain any instances of "ChangeThisSubstring". With the condition removed, this meant that my "#Added Custom" column name replacement table now had a line for every column in my data, not just the columns that needed replacing -- but for other columns, the "new" name was the same as the old name since the Text.Replace rules didn't affect them.
Then, realizing that {0} was an index for which column to replace, I hardcoded the column I wanted to update using the index for that column:
Rename = Table.RenameColumns(#"Promoted Headers", Record.ToList(Table.ToRecords(#"Added Custom"){8}))
Now, the name for the index is always "changed" to the Text.Replace version every time data is loaded. If a column name is needed, it's changed to the updated version. If it's already the updated version, it just changes to a copy of the original name.
Note that {8} is a row reference, not a column reference.
Yes, I guess that's right -- but it's a row reference to the custom table of column names. Anyway, when I specified the correct (row) column name in the script, it correctly swapped the correct (column) column name in the outer table. Maybe there are nuances I don't fully understand, but this appears to work.
