Grouping on 1 value and keeping other fields in POWER QUERY

[JMSNYC]

Hi.

This is my issue:

Product	Delivery Date Changed On	Old Delivery Date
A	2/1/20	5/15/20
A	3/1/20	5/20/20
A	4/1/20	5/16/20

The output I am looking for is 

• 1 row for Product A, with
• min value for column 2, and
• ONLY respective value for column 3, i.e. 5/15/2020.

Hence Grouping Min and then All Rows do not give me the answer.

Any suggestions? Table function ? List function?

Thank you for your help

[Jimmy801 · Accepted solution]

Hello @JMSNYC 

you can use Table.Group and use a Table.SelectRows in it.

Here a complete example. Check out the step #"Grouped rows" that does the trick

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLSN9Q3MgAyTPUNTUGsWB2IhDFCwsgAWcIESYcZWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Delivery Date Changed On" = _t, #"Old Delivery Date" = _t]),
    DateFormat = Table.TransformColumns
    (
        Source,
        {
            {
                "Delivery Date Changed On",
                each Date.From(_,"en-US"),
                type date
            },
            {
                "Old Delivery Date",
                each Date.From(_,"en-US"),
                type date
            }
        }
    ),
    #"Grouped Rows" = Table.Group(DateFormat, {"Product"}, {{"Min Date", each Table.SelectRows(_, (sel)=> sel[Delivery Date Changed On]= List.Min(_[Delivery Date Changed On])), type table [Product=text, Delivery Date Changed On=date, Old Delivery Date=date]}}),
    #"Expanded Min Date" = Table.ExpandTableColumn(#"Grouped Rows", "Min Date", {"Delivery Date Changed On", "Old Delivery Date"}, {"Delivery Date Changed On", "Old Delivery Date"})
in
    #"Expanded Min Date"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[JMSNYC]

This is AMAZING !! Works perfectly. Thanks a lot and many kudos.

[AllisonKennedy]

@JMSNYC
I have moved this post to Power Query forum so the Power Query experts will see it. 🙂

You should be able to achieve this by duplicating your table. Then use a Group by Product Name - select Min Date. Finally merge the old table back into the new table using both Product Name and Date columns as the matching columns.

This will not be very efficient, but does what you have asked.