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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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