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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

m Code - Advanced query editor - change source

ff

Hello Community  -   I would like to change  the source of my data from an Excel file that lives on my hard drive, to a dataflow we have recently created.  

 

The challenge, is that I have created merged columns, various transformations, etc....to the columns in my table, and I don't want to re - do all of this.     Is there a way to modify the M code so that it just references the new source....but still keeps all of the various transformations I have made?     Below is example of the M code from the dataflow.   

 

let
Source = PowerBI.Dataflows(null),
#"cc333e94-8511-4f27-a7d2-e782b4eccd6b" = Source{[workspaceId="cc333e94-8511-4f27-a7d2-e782b4eccd6b"]}[Data],
#"ab939b42-7cc8-4583-b91d-9ee159a94932" = #"cc333e94-8511-4f27-a7d2-e782b4eccd6b"{[dataflowId="ab939b42-7cc8-4583-b91d-9ee159a94932"]}[Data],
ShippedOrdersALL_Query1 = #"ab939b42-7cc8-4583-b91d-9ee159a94932"{[entity="ShippedOrdersALL_Query"]}[Data]
in
ShippedOrdersALL_Query1

1 ACCEPTED SOLUTION

@Anonymous - OK, here goes:

let
Source = PowerBI.Dataflows(null),
#"cc333e94-8511-4f27-a7d2-e782b4eccd6b" = Source{[workspaceId="cc333e94-8511-4f27-a7d2-e782b4eccd6b"]}[Data],
#"ab939b42-7cc8-4583-b91d-9ee159a94932" = #"cc333e94-8511-4f27-a7d2-e782b4eccd6b"{[dataflowId="ab939b42-7cc8-4583-b91d-9ee159a94932"]}[Data],
ShippedOrdersALL_Query1 = #"ab939b42-7cc8-4583-b91d-9ee159a94932"{[entity="ShippedOrdersALL_Query"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(ShippedOrdersALL_Query1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Net Price", Currency.Type}, {"ChinaOrder", type logical}, {"EMEAOrder", type logical}, {"MunichOrder", type logical}, {"MEXOrder", type logical}, {"Market", type text}, {"Region", type text}, {"Team ID", type text}, {"Team Name", type text}, {"Due Date", type date}, {"Taken By", type text}, {"Order Date", type date}, {"Item", type text}, {"Order", type text}, {"Customer", type text}, {"Bill to ST", type text}, {"Bill to ZIP", type text}, {"Bill to Country", type text}, {"Ship To Address", type text}, {"Ship To City", type text}, {"ShipTo State", type text}, {"Ship To ZIP", type text}, {"Ship To Country", type text}, {"Salesperson", type text}, {"End User Type", type text}, {"Status", type text}, {"Date Shipped", type date}, {"Family Code", type text}, {"Product Code", type text}, {"Line", type text}, {"Credit Hold", type logical}, {"Description", type text}, {"Name", type text}, {"Qty Shipped", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Flu Shipped 2019"}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "Custom", each if [Date Shipped] <> null then "Ordered-Shipped" else "Ordered-Not Shipped"),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Shipped Status"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Shipped Status", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Net Price", "derTotPrice", "Total Price", "Total Price_1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Line", Int64.Type}, {"Shipped Price", Currency.Type}}),
#"Replaced Value15" = Table.ReplaceValue(#"Changed Type2","TYLERS","Tyler Sandison",Replacer.ReplaceText,{"Salesperson"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Replaced Value15", "Date Shipped", "Date Shipped - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date Shipped - Copy", "Date Shipped Month"}}),
#"Extracted Month" = Table.TransformColumns(#"Renamed Columns2",{{"Date Shipped Month", Date.Month, Int64.Type}}),
in
#"Renamed Columns1"

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Anonymous - So, in theory yes. Seems like you have your dataflow query and you have your Excel query. Go into Advanced Editor in your dataflow query and copy your Source line and the next 2 lines. So:

 

Source = PowerBI.Dataflows(null),
#"cc333e94-8511-4f27-a7d2-e782b4eccd6b" = Source{[workspaceId="cc333e94-8511-4f27-a7d2-e782b4eccd6b"]}[Data],
#"ab939b42-7cc8-4583-b91d-9ee159a94932" = #"cc333e94-8511-4f27-a7d2-e782b4eccd6b"{[dataflowId="ab939b42-7cc8-4583-b91d-9ee159a94932"]}[Data],
ShippedOrdersALL_Query1 = #"ab939b42-7cc8-4583-b91d-9ee159a94932"{[entity="ShippedOrdersALL_Query"]}[Data]

 

Now, go into your Excel query in Advanced Editor and replace the Source and probably Navigation line with the ones you copied. Add a "," comma after your last line that you paste. In the next step below where you pasted, change the reference to the row to be "ShippedOrdersALL_Query1" where previously it was your Navigation step.

If you can post the first 10 lines or so of your Excel query, I can probably be very specific.


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you @Greg_Deckler      

 

Ok, so here are the first few lines from the M query (Excel file as source code).   (I've also added the "in" statement....but of course it refers to the last line of my total "let" statement...which is not showin in full here).

 

let
Source = Excel.Workbook(File.Contents("C:\Users\d.davis\Osram GmbH\Business Ops Team - Documents\Power Bi Reporting\Flu_Shipped Orders.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Net Price", Currency.Type}, {"ChinaOrder", type logical}, {"EMEAOrder", type logical}, {"MunichOrder", type logical}, {"MEXOrder", type logical}, {"Market", type text}, {"Region", type text}, {"Team ID", type text}, {"Team Name", type text}, {"Due Date", type date}, {"Taken By", type text}, {"Order Date", type date}, {"Item", type text}, {"Order", type text}, {"Customer", type text}, {"Bill to ST", type text}, {"Bill to ZIP", type text}, {"Bill to Country", type text}, {"Ship To Address", type text}, {"Ship To City", type text}, {"ShipTo State", type text}, {"Ship To ZIP", type text}, {"Ship To Country", type text}, {"Salesperson", type text}, {"End User Type", type text}, {"Status", type text}, {"Date Shipped", type date}, {"Family Code", type text}, {"Product Code", type text}, {"Line", type text}, {"Credit Hold", type logical}, {"Description", type text}, {"Name", type text}, {"Qty Shipped", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Flu Shipped 2019"}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "Custom", each if [Date Shipped] <> null then "Ordered-Shipped" else "Ordered-Not Shipped"),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Shipped Status"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Shipped Status", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Net Price", "derTotPrice", "Total Price", "Total Price_1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Line", Int64.Type}, {"Shipped Price", Currency.Type}}),
#"Replaced Value15" = Table.ReplaceValue(#"Changed Type2","TYLERS","Tyler Sandison",Replacer.ReplaceText,{"Salesperson"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Replaced Value15", "Date Shipped", "Date Shipped - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date Shipped - Copy", "Date Shipped Month"}}),
#"Extracted Month" = Table.TransformColumns(#"Renamed Columns2",{{"Date Shipped Month", Date.Month, Int64.Type}}),

 

 

in
#"Renamed Columns1"

@Anonymous - OK, here goes:

let
Source = PowerBI.Dataflows(null),
#"cc333e94-8511-4f27-a7d2-e782b4eccd6b" = Source{[workspaceId="cc333e94-8511-4f27-a7d2-e782b4eccd6b"]}[Data],
#"ab939b42-7cc8-4583-b91d-9ee159a94932" = #"cc333e94-8511-4f27-a7d2-e782b4eccd6b"{[dataflowId="ab939b42-7cc8-4583-b91d-9ee159a94932"]}[Data],
ShippedOrdersALL_Query1 = #"ab939b42-7cc8-4583-b91d-9ee159a94932"{[entity="ShippedOrdersALL_Query"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(ShippedOrdersALL_Query1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Net Price", Currency.Type}, {"ChinaOrder", type logical}, {"EMEAOrder", type logical}, {"MunichOrder", type logical}, {"MEXOrder", type logical}, {"Market", type text}, {"Region", type text}, {"Team ID", type text}, {"Team Name", type text}, {"Due Date", type date}, {"Taken By", type text}, {"Order Date", type date}, {"Item", type text}, {"Order", type text}, {"Customer", type text}, {"Bill to ST", type text}, {"Bill to ZIP", type text}, {"Bill to Country", type text}, {"Ship To Address", type text}, {"Ship To City", type text}, {"ShipTo State", type text}, {"Ship To ZIP", type text}, {"Ship To Country", type text}, {"Salesperson", type text}, {"End User Type", type text}, {"Status", type text}, {"Date Shipped", type date}, {"Family Code", type text}, {"Product Code", type text}, {"Line", type text}, {"Credit Hold", type logical}, {"Description", type text}, {"Name", type text}, {"Qty Shipped", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Flu Shipped 2019"}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "Custom", each if [Date Shipped] <> null then "Ordered-Shipped" else "Ordered-Not Shipped"),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Shipped Status"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Shipped Status", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Net Price", "derTotPrice", "Total Price", "Total Price_1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Line", Int64.Type}, {"Shipped Price", Currency.Type}}),
#"Replaced Value15" = Table.ReplaceValue(#"Changed Type2","TYLERS","Tyler Sandison",Replacer.ReplaceText,{"Salesperson"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Replaced Value15", "Date Shipped", "Date Shipped - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date Shipped - Copy", "Date Shipped Month"}}),
#"Extracted Month" = Table.TransformColumns(#"Renamed Columns2",{{"Date Shipped Month", Date.Month, Int64.Type}}),
in
#"Renamed Columns1"

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler     Greg - With your intial post I was able to get things working.   Challenge is the new dataflow table (field headers) don't match with many of the field headers on the old Excel table....but I'm working thru them one by one in the query editor.  

 

On that note....is there a way to combine steps into one.    Here is a live example I am doing right now.    For whatever reason, the dataflow (which is connected via API into our lame ERP system) spits out  0  and   1   for what is supposed to be a TRUE/FALSE column.    So, I have to do two Replace steps, one of them is below....to replace the 0 and change it to FALSE.    I then repeat this step a second time to get the 1 value...change to TRUE...then I can change the column type to TRUE/FALSE.  

 

Is there a way to combine those two "replace steps"...into one step in the query editor?   

 

= Table.ReplaceValue(#"Renamed Columns13","0","False",Replacer.ReplaceText,{"AR"})

@Anonymous - I do not see any way to do that here: https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue

 

You are probably starting to get out of my depth with Power Query, I just hack around in it. @ImkeF or @edhans might be able to help but you might want to start a new thread and @ them in it.


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Anonymous ,

the only replacer function I'm aware of that accepts multiple replacement pairs is List.ReplaceMatchingItems.

So to apply it to scalar values, one has to tweak the code a bit:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMgSTBkgkUCQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) ) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    OneStepTransformation = Table.TransformColumns(#"Changed Type", {{"Column1", each List.ReplaceMatchingItems({_}, {{0, false}, {1, true}}){0}}})
in
    OneStepTransformation

Please find the commented code below that should help understand what's going on:

image.png

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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