Power Query M Path Depth

[Anonymous]

Dear Community,

I would greatly appreciate if you could help me point in the right direction to resolve the following scenario:
- I have 2 Tables, of which 1 Table is self-referencing:

- Each row in the self-referencing table could have 0, 1 or multiple relationships to other rows within the same Table.

- The 2 Tables combined represent a Flow Chart leading to a 'Target' R or T

- From each row, called 'Step', there could be 1 or multiple alternative paths to reach the Target R or T

- I need to find the minimum depth (a count of the least amount of path-traversals) from each of the Step to either Target R or Target T.

My data is as follows:

Table1

Target	Step_Pre
R	Hb
T	De

Table2

Step	Step_Previous
Cc	Ac
Db	Ac
Db	Ba
Eb	Bb
Fa	Ca
Fb	Cc
Fb	Dd
Gb	Ea
Gc	Eb
Hb	Fb
Hb	Gc
Ab	Aa
Ac	Ab
Ad	Ac
Ae	Ad
Bb	Ba
Bc	Bb
Bd	Bc
Be	Bd
Cb	Ca
Cc	Cb
Cd	Cc
Ce	Cd
Db	Da
Dc	Db
Dd	Dc
De	Dd
Eb	Ea
Ec	Eb
Ed	Ec
Ee	Ed
Fb	Fa
Fc	Fb
Fd	Fc
Fe	Fd
Gb	Ga
Gc	Gb
Gd	Gc
Ge	Gd
Hb	Ha
Hc	Hb
Hd	Hc
He	Hd

Visualized this looks as follows:

How would I proceed to achieve this in PowerQuery?
Thank you

[Anonymous]

Thank you! This is excellent and spot-on. It does exactly what I was looking for.

One follow up question, expanding my original question:
- In theory, it will be possible that during data entry, by mistake, a circular reference will be generated.

For example:

Step	Step_Previous
Ab	Aa
Ac	Ab
Aa	Ac

- If I try this, the current query will give me a 'Stack Overflow error', after running for a minute or so.

- It is not straight forward to say which of the relations that would cause this circular reference is the one that is wrong. I, somehow, need to be able to test if there is a circular reference, which triggers a warning, with an indication which steps are involved.
- Desired output:
Warning Table

Warning type	Steps Involved
Circular Reference	Aa|Ab|Ac

- I would like to prevent the 'Stack Overflow error' to occur, by only running this query if no circular reference exists.  (Warning Table is empty)

Any suggestions to help develop a query that can check for a circular reference before perfoming the actual Path calculation, would be greatly appreciated.

[wdx223_Daniel]

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RefTbl=Table.Buffer(#table(Table.ColumnNames(Source1),Table.ToRows(Source2))&Source1),
    fx=(s,r)=>
        let
            a=Table.ToRows(Table.SelectRows(RefTbl,each [Step]=s)),
            ffx=(m,n)=>if m=null then n else if Text.EndsWith(m,"circular reference") then m else List.Sort({m,n},each List.Count(Text.Split(_,"|"))){0},
            test=List.Accumulate(a,r,(x,y)=>let aa=Record.FieldOrDefault(x,y{0},y{0}) in if List.Contains(Text.Split(aa,"|"),y{1}) then Record.TransformFields(x,{y{1},each _&"|circular reference"}) else @Fx(y{1},Record.TransformFields(x,{y{1},each ffx(_,aa&"|"&y{1})},2))),
            b=List.TransformMany(a,each {r},(x,y)=>@fx(x{1},y&{s}))
        in if a={} then r else test,
    Custom1 = #table(Table.ColumnNames(Source2)&{"Path","Depth"},List.TransformMany(Table.ToRows(Source2),each Table.ToRows(Record.ToTable(fx(_{0},[]))),(x,y)=>{x{0}}&y&{List.Count(Text.Split(y{1},"|"))-1}))
in
    Custom1

[Anonymous]

wdx223_Daniel, You have been of great help.
It took some adjustment to make it work with my actual data. But I have it working.
While developing the Power BI vizualisations based on this data, I now realize that instead of the 'Minimum amount of path-traversals, I will be needing to find the maximum
To rephrase the riginal question:
- I need to find the maximum depth (a count of the maximum amount of path-traversals) from each of the Step to either Target R or Target T.

Could you help and point out how I can adjust the query you original provided, so it gives the maximum?

Many thanks in advance

[wdx223_Daniel · Accepted solution]

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RefTbl=Table.Buffer(#table(Table.ColumnNames(Source1),Table.ToRows(Source2))&Source1),
    fx=(s,r)=>
        let
            a=Table.ToRows(Table.SelectRows(RefTbl,each [Step]=s)),
            ffx=(m,n)=>if m=null then n else List.Sort({m,n},each List.Count(Text.Split(_,"|"))){0},
            b=List.Accumulate(a,r,(x,y)=>@fx(y{1},Record.TransformFields(x,{y{1},each ffx(_,Record.FieldOrDefault(x,y{0},y{0})&"|"&y{1})},2)))
        in if a={} then r else b,
    Custom1 = #table(Table.ColumnNames(Source2)&{"Path","Depth"},List.TransformMany(Table.ToRows(Source2),each Table.ToRows(Record.ToTable(fx(_{0},[]))),(x,y)=>{x{0}}&y&{List.Count(Text.Split(y{1},"|"))-1}))
in
    Custom1