cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sshweky Member
Member

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

Accepted Solutions
KHorseman Super Contributor
Super Contributor

Re: Conditional merge columns in Query Editor

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? Please mark it as a solution.

Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Conditional merge columns in Query Editor

General syntax for if statement in M:

 

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

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

sshweky Member
Member

Re: Conditional merge columns in Query Editor

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

KHorseman Super Contributor
Super Contributor

Re: Conditional merge columns in Query Editor

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? Please mark it as a solution.

Proud to be a Datanaut!
sshweky Member
Member

Re: Conditional merge columns in Query Editor

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 Super Contributor
Super Contributor

Re: Conditional merge columns in Query Editor

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? Please mark it as a solution.

Proud to be a Datanaut!
sshweky Member
Member

Re: Conditional merge columns in Query Editor

Thank you soooo much!! It worked.

 

 

Steven

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 381 members 3,156 guests
Please welcome our newest community members: