Conditional merge columns in Query Editor

[sshweky]

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 line is the "Added Prefix" line, I want the prefic to be "FC0" when the [division] field="FC" & "BB0" when the [division] field="BB".

Can someone please help? Thank you!!

let
Source = Access.Database(File.Contents("C:\Users\Steven\Desktop\Dropbox (Personal)\FC\dashboard\fc.mdb")),
_opnord = Source{[Schema="",Item="opnord"]}[Data],
#"Removed Columns" = Table.RemoveColumns(_opnord,{"CO", "DIV"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DISTCTR", "DIV"}, {"SLSMAN", "slscode"}, {"CSTNO", "acct"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"SUBITM", "SLSAGT3"}),
#"Removed Columns2" = Table.RemoveColumns(#"Removed Columns1",{"pgmname", "dspitmid", "potype", "CSTLN", "CONSGNPO"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"liccde", "LicCode"}, {"ITMID", "Style #"}, {"BASPRC", "Sell Price"}, {"EXTPRICE", "OpenOrders$"}, {"CSTORD", "Cust PO#"}, {"CSTSKU", "Cust SKU#"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Style #", Order.Ascending}}),
#"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows",{{"ORDNO", "IntOrder#"}, {"PIKDT", "PickDate"}, {"REQDTE", "StartShip"}, {"CNLAFTDTE", "CancelDate"}, {"CSTNAM", "Customer Name"}, {"ENTDTE", "Entry Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Entry Date", type date}, {"PickDate", type date}, {"StartShip", type date}, {"CancelDate", type date}, {"Weekending", type date}, {"Cust SKU#", Int64.Type}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"acct", "Account#"}, {"OpenOrders$", "OpenOrder$"}, {"slscode", "SalesID#"}}),
#"Added Prefix" = Table.TransformColumns(#"Renamed Columns3", {{"SalesID#", each "FC0" & Text.From(_, "en-US"), type text}})
in
#"Added Prefix"

[Greg_Deckler]

General syntax for if statement in M:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [IsWeekend] then "TT" else "FF")

[sshweky]

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

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

[KHorseman]

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]

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

[KHorseman · Accepted solution]

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"

[sshweky]

Thank you soooo much!! It worked.

Steven