cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
heytherejem Member
Member

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

Accepted Solutions
Super User
Super User

Re: Return original columns after grouping

Hi @heytherejem 

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

Super User
Super User

Re: Return original columns after grouping

@heytherejem  - 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
HotChilli New Contributor
New Contributor

Re: Return original columns after grouping

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

Super User
Super User

Re: Return original columns after grouping

Hi @heytherejem 

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

Super User
Super User

Re: Return original columns after grouping

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

heytherejem Member
Member

Re: Return original columns after grouping

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 🙂

Super User
Super User

Re: Return original columns after grouping

Hi @heytherejem,

Always happy to help!

 

Mariusz

heytherejem Member
Member

Re: Return original columns after grouping

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

Super User
Super User

Re: Return original columns after grouping

Hi @heytherejem 

 

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



Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 139 members 1,728 guests
Please welcome our newest community members: