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
Anonymous
Not applicable

Return original columns after grouping

Trying again with this one after no luck.

 

I have a Table with duplicate membership numbers in, so I want to group membership number by the latest date to get only the most recent row for that member.

 

However grouping this way removes all other columns in my table including the index. 

 

I really need the index, so I want to bring this back somehow after the grouping. 

 

Attached is an image of what I want to get to:


Grouping example.png

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

Please see the below M expression, if you have any questions let me know.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzBDcAgDAN38RuJOJS2mSVi/zWg9Bfys0+ncwdRIKxiVYW2DjGKQyNuG7dP6Id95ZG+lp72nbefvP3GiG5sMfJjSsLHBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, #"Member ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Date", type date}, {"Member ID", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Member ID", "Date", "Index"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Member ID"}, {{"Date", each List.Max([Date]), type date}, {"Index", each Table.FirstN( Table.Sort( _, {"Date", Order.Descending} ), 1 )[Index], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Index", each Text.Combine(List.Transform(_, Text.From)), Int64.Type})
in
    #"Extracted Values"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



View solution in original post

Anonymous
Not applicable

@Anonymous  - The solution that @HotChilli  is a good one, if the indexes are in the required order. Otherwise, you can Merge Tables to the step prior to the Group By - something like this (Note: "Expanded Partition" is the step prior to "Grouped Rows" step:

    #"Grouped Rows" = Table.Group(#"Expanded Partition", {"ProductId"}, {{"max_date", each List.Max([SalesDateId]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ProductId", "max_date"}, #"Expanded Partition", {"ProductId", "SalesDateId"}, "Product", JoinKind.Inner),
    #"Expanded Product" = Table.ExpandTableColumn(#"Merged Queries", "Product", {"Index"}, {"Index"})
in
    #"Expanded Product"
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@Anonymous  - The solution that @HotChilli  is a good one, if the indexes are in the required order. Otherwise, you can Merge Tables to the step prior to the Group By - something like this (Note: "Expanded Partition" is the step prior to "Grouped Rows" step:

    #"Grouped Rows" = Table.Group(#"Expanded Partition", {"ProductId"}, {{"max_date", each List.Max([SalesDateId]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ProductId", "max_date"}, #"Expanded Partition", {"ProductId", "SalesDateId"}, "Product", JoinKind.Inner),
    #"Expanded Product" = Table.ExpandTableColumn(#"Merged Queries", "Product", {"Index"}, {"Index"})
in
    #"Expanded Product"
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
Anonymous
Not applicable

Thanks everyone, the grouping by two max aggregates seemed to work, so @HotChilli this was a good shout, but just for the sake of visibility and being completely sure it's de-duplicated the way I want, I have used @Mariusz suggestion which is actually what I was envisioning.

 

So thank you @Mariusz you're a genious! 

 

Jemma 🙂

Hi @Anonymous,

Always happy to help!

 

Mariusz

Anonymous
Not applicable

Hi @Mariusz  I am re-using this solution for another dataset, but I wondered whether I could bring more than just one column into the list in order to expand? 

So in the code below I pull back just one column of Index. Can I stick a few columns in there? If so, what syntax do I use?

 

= Table.Group(PreviousStep,{"Member"},{{"Date", each List.Max([Date]), type date}, {"Index", each Table.FirstN(Table.Sort(_,{"Date", Order.Descending}), 1)[Index], type list}})

Jemma

Hi @Anonymous 

 

sure, please see the adjusted code.

// new code
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzBDcAgDAN38RuJOJS2mSVi/zWg9Bfys0+ncwdRIKxiVYW2DjGKQyNuG7dP6Id95ZG+lp72nbefvP3GiG5sMfJjSsLHBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, #"Member ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Date", type date}, {"Member ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Member ID"}, {{"tbl", each Table.FirstN( Table.Sort( _, {"Date", Order.Descending} ), 1 ), type table }}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded tbl"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



Mariusz
Community Champion
Community Champion

Hi @Anonymous 

Please see the below M expression, if you have any questions let me know.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzBDcAgDAN38RuJOJS2mSVi/zWg9Bfys0+ncwdRIKxiVYW2DjGKQyNuG7dP6Id95ZG+lp72nbefvP3GiG5sMfJjSsLHBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, #"Member ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Date", type date}, {"Member ID", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Member ID", "Date", "Index"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Member ID"}, {{"Date", each List.Max([Date]), type date}, {"Index", each Table.FirstN( Table.Sort( _, {"Date", Order.Descending} ), 1 )[Index], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Index", each Text.Combine(List.Transform(_, Text.From)), Int64.Type})
in
    #"Extracted Values"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



HotChilli
Super User
Super User

You're already using 'Group By' in Power Query, grouping on the ID.

You'll have an aggregation for Max of the date.

Hit the 'advanced' radio button and Add an aggregation for Max of the Index

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.