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"
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"
Community ChampionAnonymous - 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"})
- 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 OneStepTransformationPlease find the commented code below that should help understand what's going on:
