Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.