Forum Discussion
Conditional merge columns in Query Editor
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'm so sorry, but I can't understand the documentation. Here is my code ... can you show me how to structure?
= Table.TransformColumns(#"Renamed Columns3", "SalesID#", each if [DIV]="FC" then "FC0" else "BB0")
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