m Code - Advanced query editor - change source

[Anonymous]

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

[Greg_Deckler]

@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.

[Anonymous]

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"

[Greg_Deckler · 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"

[Anonymous]

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

[Greg_Deckler]

@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.

[ImkeF]

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: