Return original columns after grouping

[Anonymous]

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:

[Mariusz · Accepted solution]

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.

[Anonymous · Accepted solution]

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

[Anonymous]

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 🙂

[Mariusz]

Hi @Anonymous,

Always happy to help!

Mariusz

[Anonymous]

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

[Mariusz]

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 · Accepted solution]

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.

[HotChilli]

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