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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

        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