Find the Loop

[PANDAmonium]

Hi All,

I have a dataflow that transform a parent child table to a table where you can select the parent and filter to their children/grandchildren, etc.

So from:

Parent | Child

A | B

B | C

B | D

To:

Parent | Child

A | A

A | B

A | C

A | D

B | C

B | D

That way if you select a parent, you filter to them and all the nodes under them. Recently it's been timing out; from 13m successful to +1hr failed). The data load time is instant since it's coming from another dataflow, so it's all the time is spent on transformation steps. 

I need to identify the loop and also limit the number of times the M function iterates through. Here's the step causing the issue. Tried a couple things that didn't work or would still time out. If posting the full query would help, let me know. Thanks!

Table.AddColumn(#"Child | Renamed Columns", "Path", each let
mytable=#"Child | Renamed Columns",p="Parent",c="Child"
in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|"))

[v-xiaosun-msft · Accepted solution]

Hi @PANDAmonium ,

If the relationships in your data are ambiguous, i.e. items stand in parent-child as well as child-parent-relationship to each other, and endless loop would occur. 

You can reference the following document to create "EndlessLoops" and "IsEndlessLoop" in order to check the loop.

And then create a custom column.

Custom1 = if IsEndlessLoop then [Message= "The data is in an endless loop. Check Table in  ""Endless Loop""", #" Endless Loop"= LastIteration] meta [ResultsSoFar = RemainingResults] else IsLeaf
in
    Custom1

Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI – The BIccountant

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PANDAmonium]

Thanks, this has helped greatly! Much appreciated.