Forum Discussion
Get hierarchical item position
Hello,
I have a complex problem (in my mind) to solve with hierachical data.
My data are like this :
| Parent | Child | Position |
| 5 | 3 | 0 |
| 5 | 4 | 0 |
| 3 | 7244 | 1 |
| 3 | 9984 | 1 |
| 3 | 158 | 1 |
| 158 | 4467 | 2 |
The [Position] column has to be filled with the position (indexed to 0 or 1) of the [Parent] value. here you can see that [Parent] 5 is parent of [Child] 3 and [Child] 3 parent of multiple [Child].
In the example, [Parent] 5 has no parent so the value is 0 and so on...
I'm able to find what I want with Excel and manual process but I don't have yet the "PBI mind" to do it...
I'm using this formula with excel :
{=IF([@Position]="NotProcessed";COUNTIFS([Position6];TRUE;[Child];[@Parent]);FALSE)}
With the result, I create a new column ei:Position7
Can someone try to help me ?
[Counter] < 100 is to end after the 100th loop.
I tested with 10,000 records, which took about 100 seconds.
With additional Table.Buffers (inside List.Generate), runtime decreased to about 60 seconds:
let Source = Table.Buffer(Data), TablesPerPosition = List.Generate( () => [Counter = -1, MergedTable = #table(0,{}), NewPart = #table({"Parent"},{{0}}), Remaining = Source], each (not Table.IsEmpty([NewPart])) and [Counter] < 100, each [Counter = [Counter] + 1, MergedTable = let Merged = Table.NestedJoin([Remaining],{"Parent"},[Remaining],{"Child"},"Data",JoinKind.LeftOuter), AddedPosition = Table.AddColumn(Merged, "Position", each if Table.IsEmpty([Data]) then Counter else null, Int64.Type), RemovedColumn = Table.RemoveColumns(AddedPosition,{"Data"}) in Table.Buffer(RemovedColumn), NewPart = Table.Buffer(Table.SelectRows(MergedTable, each ([Position] <> null))), Remaining = let Filtered = Table.SelectRows(MergedTable, each ([Position] = null)), RemovedColumn = Table.RemoveColumns(Filtered,{"Position"}) in Table.Buffer(RemovedColumn)], each [NewPart]), TablesCombined = Table.Combine(List.Skip(TablesPerPosition)) in TablesCombined
4 Replies
- MarcelBeug
Community Champion
A possible Power Query solution below.
List.Generate is used to loop over the remaining part of the table. In each iteration, the remaining part is merged with itself (with Parent and Child as keys) and the Position is added if the Merged table is empty (i.e. the Parent is not found as Child).
Table NewPart will be the records in which the Position is not null.
The new Remaining part are the records with Position null.
List.Generate returns all NewParts, including the first dummy NewPart table which is skipped when the tables are combined in the last step.
The loop continues while the remaining part still has records (and Counter < 100, just to prevent endless looping).
let Source = Table.Buffer(Data), TablesPerPosition = List.Generate( () => [Counter = -1, MergedTable = #table(0,{}), NewPart = #table({"Parent"},{{0}}), Remaining = Source], each (not Table.IsEmpty([NewPart])) and [Counter] < 100, each [Counter = [Counter] + 1, MergedTable = let Merged = Table.NestedJoin([Remaining],{"Parent"},[Remaining],{"Child"},"Data",JoinKind.LeftOuter), AddedPosition = Table.AddColumn(Merged, "Position", each if Table.IsEmpty([Data]) then Counter else null, Int64.Type), RemovedColumn = Table.RemoveColumns(AddedPosition,{"Data"}) in RemovedColumn, NewPart = Table.SelectRows(MergedTable, each ([Position] <> null)), Remaining = let Filtered = Table.SelectRows(MergedTable, each ([Position] = null)), RemovedColumn = Table.RemoveColumns(Filtered,{"Position"}) in Table.Buffer(RemovedColumn)], each [NewPart]), TablesCombined = Table.Combine(List.Skip(TablesPerPosition)) in TablesCombinedHi Marcel,
Many thanks for this clear explanation.
Regarding the criteria [counter]< 100, is it there to end the loop at the 100th loop ? or if a Position contains 100 items ?
I have another question regarding performances, I have more than 7K rows to process and 8 different positions and it take a long time to calculate. Do you have any tips to have it done quicker ?
This is definitely the good solution.
Thanks again.
- MarcelBeug
[Counter] < 100 is to end after the 100th loop.
I tested with 10,000 records, which took about 100 seconds.
With additional Table.Buffers (inside List.Generate), runtime decreased to about 60 seconds:
let Source = Table.Buffer(Data), TablesPerPosition = List.Generate( () => [Counter = -1, MergedTable = #table(0,{}), NewPart = #table({"Parent"},{{0}}), Remaining = Source], each (not Table.IsEmpty([NewPart])) and [Counter] < 100, each [Counter = [Counter] + 1, MergedTable = let Merged = Table.NestedJoin([Remaining],{"Parent"},[Remaining],{"Child"},"Data",JoinKind.LeftOuter), AddedPosition = Table.AddColumn(Merged, "Position", each if Table.IsEmpty([Data]) then Counter else null, Int64.Type), RemovedColumn = Table.RemoveColumns(AddedPosition,{"Data"}) in Table.Buffer(RemovedColumn), NewPart = Table.Buffer(Table.SelectRows(MergedTable, each ([Position] <> null))), Remaining = let Filtered = Table.SelectRows(MergedTable, each ([Position] = null)), RemovedColumn = Table.RemoveColumns(Filtered,{"Position"}) in Table.Buffer(RemovedColumn)], each [NewPart]), TablesCombined = Table.Combine(List.Skip(TablesPerPosition)) in TablesCombined
