Forum Discussion
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
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"
6 Replies
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.
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"