Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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"
@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"
@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"
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
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"
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"
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |