Forum Discussion

sshweky's avatar
sshweky
Helper III
10 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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"

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

General syntax for if statement in M:

 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [IsWeekend] then "TT" else "FF")
  • sshweky's avatar
    sshweky
    Helper III
    10 years ago

    Thank you! I think I did this right, but I am getting the following error:

    SalesID# is type text. What is type list??

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Your syntax is incorrect for Table.TransformColumns. The second argument in Table.TransformColumns is expected to be a list. A list is essentially the same as an array. Not a single text value, but a series of separated values. See documentation here.

      • sshweky's avatar
        sshweky
        Helper III
        10 years ago

        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")