Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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"
Solved! Go to 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"
Proud to be a Super User!
General syntax for if statement in M:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [IsWeekend] then "TT" else "FF")
Thank you! I think I did this right, but I am getting the following error:
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.
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"
Proud to be a Super User!
Thank you soooo much!! It worked.
Steven
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |