Forum Discussion
m Code - Advanced query editor - change source
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 - 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"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.