Forum Discussion
Solved
Conditional merge columns in Query Editor
Hi - I need to add a prefix to a column based on the value od another column and I don't know how to structure an IF statement in the advanced query editor. Below is the code I have now. The last...
I don't think Table.TransformColumns is even the appropriate method for what you're trying to do. I would just add a new column that conditionally adds that prefix, then delete the old unneeded column.So instead of renaming slscode to SalesID#, add a new column called SalesID# then delete slscode.
... #"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"acct", "Account#"}, {"OpenOrders$", "OpenOrder$"}}), #"AddedSalesID" = Table.AddColumn(#"Renamed Columns3", "SalesID#", each Text.Insert(Text.From([slscode]), 0, if [DIV] = "FC" then "FC0" else "BB0")), #"RemovedSLSCode" = Table.RemoveColumns(#"AddedSalesID", {slscode}) in #"RemovedSLSCode"
I don't think Table.TransformColumns is even the appropriate method for what you're trying to do. I would just add a new column that conditionally adds that prefix, then delete the old unneeded column.So instead of renaming slscode to SalesID#, add a new column called SalesID# then delete slscode.
...
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"acct", "Account#"}, {"OpenOrders$", "OpenOrder$"}}),
#"AddedSalesID" = Table.AddColumn(#"Renamed Columns3", "SalesID#", each Text.Insert(Text.From([slscode]), 0, if [DIV] = "FC" then "FC0" else "BB0")),
#"RemovedSLSCode" = Table.RemoveColumns(#"AddedSalesID", {slscode})
in
#"RemovedSLSCode"
Thank you soooo much!! It worked.
Steven