Group by function in Power Query

[Anonymous]

I have a mock data table like this:

Item	Purchase Order number	Ship date	Quantity
A	ANR1	3/2/2020	4
A	ANR2	3/5/2020	5
B	ANR5	6/5/2020	3
B	ANR9	5/4/2020	4
B	ANR4	4/23/2020	4
C	ANR8	5/12/2020	12

I want to group by Item, then get me the earliest Ship date, and the accordingly quantity of that shipment. Result should look like this:

Item	Ship date	Quantity
A	3/2/2020	4
B	4/23/2020	4
C	5/12/2020	12

Group by function in Power Query let you do all the aggregating fun stuff. I do need that to get out the Min (Ship date), but after that i just want to grab the Qty without any aggregation ( to prevent case that there are 2 orders ship on same date with different quantity). Any advice? Thanks in advance!

[ImkeF · Accepted solution]

Hi @Anonymous ,

Doing it via grouping is a bit clunky in my eyes, so you can also check out the version via duplicate removal:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
    GroupWithAddedPartition = Table.Group(#"Changed Type", {"Item"}, {{"Earliest Date", each List.Min([Ship date]), type date}, {"ItemPartition", each _, type table [Item=text, Purchase Order number=text, Ship date=date, Quantity=number]}}),
    FilterOnEarliestDate = Table.AddColumn(GroupWithAddedPartition, "FilterEarliestDate", each Table.SelectRows([ItemPartition], (x) => x[Ship date] = [Earliest Date])),
    GetQuantity = Table.AddColumn(FilterOnEarliestDate, "GrabQuantityColumn", each [FilterEarliestDate][Quantity]{0}),
    Cleanup = Table.RemoveColumns(GetQuantity,{"ItemPartition", "FilterEarliestDate"})
in
    Cleanup

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
    SortByShipDate = Table.Buffer( Table.Sort(#"Changed Type",{{"Ship date", Order.Ascending}}) ),
    #"Removed Duplicates" = Table.Distinct(SortByShipDate, {"Item"})
in
    #"Removed Duplicates"

[Anonymous]

Hi @ImkeF 

Superb! Works like a charm!

Since you're here 😆 What do you suggest to not only sort out the date that is the earliest ship date, but also filter that that date is a future ship date (>=today). I thought about adding a column with DateTime.LocalNow(), then compare ship date with that and mark each line either "Past shipment" or "Future shipment" and then filter for just "Future shipment" and from there use your solution to get the earliest. Any word of advice? Thanks alot!

[ImkeF]

Hi @Anonymous ,

yes, that would be a solution.

But you could also "simply" filter out future ship dates on the date column itself with relative date filters. Same effect: sned just future transactions to the subsequent steps, but no need for additional column. But that might be only cosmetic and doesn't effect the query performance.