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
Hi Tom_G ,
I think you need to do a double-group, first to remove but retain duplicates (this allows the index to repeat later), then also to create the distinction between vans (this allows the index to restart on a new van number).
Paste this into a new blank query to follow the steps I took:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NjE1U9IBMkxMlWJ1UEXMMETMSRMxh4hYYIhYYhOJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Van number" = _t, #"Calling ID" = _t]),
groupRows1 = Table.Group(Source, {"Van number", "Calling ID"}, {{"data1", each _, type table [Van number=nullable number, Calling ID=nullable number]}}),
groupRows2 = Table.Group(groupRows1, {"Van number"}, {{"data2", each _, type table [Van number=nullable text, Calling ID=nullable text, data1=table]}}),
addNestedIndex = Table.TransformColumns(groupRows2, {"data2", each Table.AddIndexColumn(_, "Index", 1, 1)}),
expandData1andIndex = Table.ExpandTableColumn(addNestedIndex, "data2", {"data1", "Index"}, {"data1", "Index"}),
expandCallingID = Table.ExpandTableColumn(expandData1andIndex, "data1", {"Calling ID"}, {"Calling ID"})
in
expandCallingID
Summary:
1) groupRows1 = Group on both [Van number] and [Calling ID] with All Rows aggregate. This gets rid of duplicates but keeps them in All Rows so we can force the index to repeat the same number for the same van/id.
2) groupRows2 = Group again, but just on [Van number]. This lets us restart our index for each different van.
3) addNestedIndex = Add a nested index to the tables in [data2].
4) expandData1andIndex = Expand [data1] and [Index] from first level of nested tables ( [data2] ) to show indexed vans.
5) expandCallingID = Expand [Calling ID] from next level of nested tables ( [data1] ) to create duplicate index values for identical values of [Calling ID] under each [Van number].
This gives the following output:
Pete