Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sshweky
Helper III
Helper III

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

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

General syntax for if statement in M:

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Capture.JPG

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

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you soooo much!! It worked.

 

 

Steven

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.