Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:


Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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



  • Anonymous's avatar
    Anonymous
    7 years ago

    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

7 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    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

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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



  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous,

        Always happy to help!

         

        Mariusz