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.
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,""})),"|"))
Solved! Go to 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.
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.
Thanks, this has helped greatly! Much appreciated.
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.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |