Index by subgroups with repeated values

[Tom_G]

Hello,

I have a dataset like the below.

Van number	Calling ID	Index
133456	1345	1
133456	1346	2
133456	1347	3
133456	1347	3
133456	1347	3
133457	1348	1
133457	1349	2
133457	1349	2

I'd like to add the index column above in power query. I found the solution in Dax but as the table is large, the memory maxes out before the query is complete. I think power query is better suited for this. There are some posts of how to do this in power query but as i'm quite new to it, i'm not sure how to make the source code work in my model. 

Could someone provide a step by step guide in terms of what to click on that I could follow? 

Thanks a lot!

[BA_Pete]

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

[serpiva64 · Accepted solution]

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

[Tom_G]

Thanks very much, I followed the video at the link you provided and it worked! https://wmfexcel.com/2020/12/12/dense-ranking-with-power-query-unexpected-behavior-and-workarounds/

[Anonymous]

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