Renaming a Column if it Contains String

[programsam]

If the subject isn't enough, hopefully the image will better explain. I have a folder where Power Query appends all of hte files. Each file has a different date and the date is always in the "Status/On order" column. I am trying to rename the column IF it has the word "Status" in it to remove all of the extra noise and just call it "STATUS".

= Table.TransformColumnNames(#"Promoted Headers", each if Text.Contains(Text.Upper(_),"Status") then "STATUS" else (_))

 

[smozgur]

If the subject isn't enough, hopefully the image will better explain. I have a folder where Power Query appends all of hte files. Each file has a different date and the date is always in the "Status/On order" column. I am trying to rename the column IF it has the word "Status" in it to remove all of the extra noise and just call it "STATUS".

View attachment 95841

= Table.TransformColumnNames(#"Promoted Headers", each if Text.Contains(Text.Upper(_),"Status") then "STATUS" else (_))

You are using Text.Upper() function, which is the correct approach. However, you need to compare it with the uppercase word as well.

= Table.TransformColumnNames(#"Promoted Headers", each if Text.Contains(Text.Upper(_),"STATUS") then "STATUS" else (_))

 

[smozgur]

Note: If you have multiple columns with the Status keyword in them, then the other columns will be also renamed except for including an incremental number, such as STATUS1, STATUS2, etc. In this case, if you know the column position, then you can check that column only and update it if necessary.

ColumnName = Table.ColumnNames(#"Promoted Headers"){0},
    Result = Table.RenameColumns(#"Promoted Headers", if Text.Contains(Text.Upper(ColumnName), "STATUS") then {ColumnName, "STATUS"} else {})

 

[programsam]

Note: If you have multiple columns with the Status keyword in them, then the other columns will be also renamed except for including an incremental number, such as STATUS1, STATUS2, etc. In this case, if you know the column position, then you can check that column only and update it if necessary.

ColumnName = Table.ColumnNames(#"Promoted Headers"){0},
    Result = Table.RenameColumns(#"Promoted Headers", if Text.Contains(Text.Upper(ColumnName), "STATUS") then {ColumnName, "STATUS"} else {})

Thank you for this, your first response was enougth to point me in the right direction but this is certainly useful!

 

[smozgur]

Thank you for this, your first response was enougth to point me in the right direction but this is certainly useful!l

Glad to hear it helps!

 

[RoryA]

FWIW, you could also use:

Text.Contains(_, "Status", Comparer.OrdinalIgnoreCase)

for a case-insensitive comparison