Do lookup of row in previous query step

[Anonymous]

I have a query with the following steps, steps #1 -# 3 I've done, I need help with Step #4

Step 1. Detailed Table at transaction level

Step 2. Group and sum transaction level

Step 3. Bring back Step 1 (Detailed Table at transaction level)

Step 4. Lookup values in Step 2 (The grouped values) and create new column with the grouped sum

Below pictures will better explain what I'm trying to do.

Here is Step 1, the detailed table. 

Here is Step 2 where I group and sum the transactions:

Here is step 3: where I bring back the detailed transaction (Step 1)

For Step 4, which I don't know how to do, I want to lookup the delivery # that was grouped in Step 2 and bring back the value as a new column. In the above example delivery #80401337 will bring  back from the grouped table 17.896 for each row (so it will get repeated four times, which is what I want)

[camargos88]

Hi @Anonymous ,

You can group by the delivery level using all rows, and add a column to the [Data] with the sum.

Can you share you m code ?

[Anonymous]

When you say  m code, do you mean this?:

[camargos88]

@Anonymous ,

Yes, but paste like text...so I can edit it.

[Anonymous]

Here is the text of the code: 

let
Source = Excel.Workbook(File.Contents("\\dcfile02\tdrive$\Cost Accounting\Logistics\Deep Dive\Power BI\Test\Sample Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Reordered Columns" = Table.ReorderColumns(#"Promoted Headers",{"Act.goods issue date.Act.goods issue date Level 01", "Delivery.Delivery Level 01", "Weight Formula (Matr Wgt X Del Qty)", "Sales document.Sales document Level 01", "Delivery quantity", "Material.Material Level 01", "Material.Material Level 01.Key", "PH Category*.PH Category* Level 01", "Prod. hier. level 3.Prod. hier. level 3 Level 01", "Delivery USD net val", "Sales doc. type.Sales doc. type Level 01", "Item.Item Level 01", "Column13"}),
#"Step 1: Detailed Table" = Table.TransformColumnTypes(#"Reordered Columns",{{"Act.goods issue date.Act.goods issue date Level 01", type date}, {"Delivery.Delivery Level 01", type text}, {"Sales document.Sales document Level 01", type text}, {"Delivery quantity", Int64.Type}, {"Weight Formula (Matr Wgt X Del Qty)", type number}, {"Material.Material Level 01", type text}, {"Material.Material Level 01.Key", type text}, {"PH Category*.PH Category* Level 01", type text}, {"Prod. hier. level 3.Prod. hier. level 3 Level 01", type text}, {"Delivery USD net val", type number}, {"Sales doc. type.Sales doc. type Level 01", type text}, {"Item.Item Level 01", Int64.Type}, {"Column13", type any}}),
#"Step 2: Group by Delivery" = Table.Group(#"Step 1: Detailed Table", {"Delivery.Delivery Level 01"}, {{"Sum", each List.Sum([#"Weight Formula (Matr Wgt X Del Qty)"]), type number}}),
#"Step 3: Bring back Detailed Table" = #"Step 1: Detailed Table"
in
#"Step 3: Bring back Detailed Table"

[camargos88 · Accepted solution]

@Anonymous ,

Try this m code:

let
Source = Excel.Workbook(File.Contents("\\dcfile02\tdrive$\Cost Accounting\Logistics\Deep Dive\Power BI\Test\Sample Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Reordered Columns" = Table.ReorderColumns(#"Promoted Headers",{"Act.goods issue date.Act.goods issue date Level 01", "Delivery.Delivery Level 01", "Weight Formula (Matr Wgt X Del Qty)", "Sales document.Sales document Level 01", "Delivery quantity", "Material.Material Level 01", "Material.Material Level 01.Key", "PH Category*.PH Category* Level 01", "Prod. hier. level 3.Prod. hier. level 3 Level 01", "Delivery USD net val", "Sales doc. type.Sales doc. type Level 01", "Item.Item Level 01", "Column13"}),
#"Step 1: Detailed Table" =
Table.AddColumn(
Table.TransformColumnTypes(#"Reordered Columns",{{"Act.goods issue date.Act.goods issue date Level 01", type date}, {"Delivery.Delivery Level 01", type text}, {"Sales document.Sales document Level 01", type text}, {"Delivery quantity", Int64.Type}, {"Weight Formula (Matr Wgt X Del Qty)", type number}, {"Material.Material Level 01", type text}, {"Material.Material Level 01.Key", type text}, {"PH Category*.PH Category* Level 01", type text}, {"Prod. hier. level 3.Prod. hier. level 3 Level 01", type text}, {"Delivery USD net val", type number}, {"Sales doc. type.Sales doc. type Level 01", type text}, {"Item.Item Level 01", Int64.Type}, {"Column13", type any}}),
"Sum",
each let _DeliveryLevel = [Delivery.Delivery Level 01] in
List.Sum(
Table.SelectRows(
#"Reordered Columns",
each [Delivery.Delivery Level 01] = _DeliveryLevel
)[#"Weight Formula (Matr Wgt X Del Qty)"]
)
)
in
#"Step 1: Detailed Table"