Forum Discussion
Index by subgroups with repeated values
Hi,
this reply is inspired by the article –
Dense Ranking with Power Query – Unexpected Behavior and Workarounds
written by wmfexcel
and by the article:
Nested Calculations In Power Query
written by Chris Webb.
to obtain this:
You need:
- first create a query in which you remove duplicates and rank
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NjE1U9IBMkxMQZRSrA6qKIgywhA1B1LG5ImaQ0QtUG2Dilqi2oYqGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Van number" = _t, #"Calling ID" = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Van number", Int64.Type}, {"Calling ID", Int64.Type}, {"Index", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Calling ID"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Van number"}, {{"AllRows", each _, type table [Van number=nullable number, Calling ID=nullable number]}}),
RankFunction = (tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Calling ID", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
//Apply that function to the AllRows column
AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_)}),
#"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"Calling ID", "Rank"}, {"Calling ID", "Rank"})
in
#"Expanded AllRows"- then in the original query (the one where you have duplicates) you have only to merge the second one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NjE1U9IBMkxMQZRSrA6qKIgywhA1B1LG5ImaQ0QtUG2Dilqi2oYqGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Van number" = _t, #"Calling ID" = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Van number", Int64.Type}, {"Calling ID", Int64.Type}, {"Index", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Calling ID"}, RemovedDuplicates, {"Calling ID"}, "Table (3)", JoinKind.LeftOuter),
#"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"Rank"}, {"Rank.1"})
in
#"Expanded Table (3)"and that's done
The easiest way to do this is to click Group By, choose Calling ID, and select the All Rows aggregation. Sort your Calling ID column ascending, add an index column from the GUI starting a 1, then expand your All Rows column. Click the fill down button, choose the index column. And voila, you are done!
--Nate