Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMSNYC
Helper III
Helper III

Grouping on 1 value and keeping other fields in POWER QUERY

Hi.

 

This is my issue:

ProductDelivery Date Changed OnOld Delivery Date
A2/1/205/15/20
A3/1/205/20/20
A4/1/205/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

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

 

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

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

 

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

AllisonKennedy
Super User
Super User

@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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors