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
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
Thanks for helping with this. I got this working by applying my Text.Replace logic to all column names, not just those that actually have the substring that needs changing (removing the clause that specifies getting only columns with the substring). With this change, my custom table is never null/ an error is never thrown, and the custom table that processes the name change (OldName, NewName) simply has a duplicate of the OldName whenever the substring is not present. Last, I specified the column index of the columns to "change" to the NewName.
However, I'm interested in whether you think try... otherwise would have been a better approach.
