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.
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
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.
