Get hierarchical item position

[niark]

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 ?

[MarcelBeug]

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
    TablesCombined

[niark]

Hi 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 · Accepted solution]

[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

[niark]

I'm more around 5 minutes than 100s. I think it comes from my machine.

thanks again for your help.