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.
I have a table with the following structure:
- ROUTING_ID_From
- ROUTING_ID_To
- DirectOrIndirect (= "Direct" for all records)
- TableOfDescendents - this is a tablecolumn, containing the first three fields, DirectOrIndirect = "Indirect" for all records)
(The contents of TableOfDescendents is the result of a function that for each record processes the main table and calculates indirect relations. It's not a list, but a table.)
Now I want to combine the 'main table' with the contents of all the tablecolumns. Performance is OK until about 600 records in the main table (in that test case the average number of records in the 'sub tables' was 12).
How can this best be done? Here is the code I use:
TableOfDescendents = Table.AddColumn(MainTable, "TableOfDescendents", each fnTransitiveRelationTable(MainTable, [ROUTING_ID_From])),
CombinedDescendents = Table.Combine(TableOfDescendents[TableOfDescendents]), TableOfAllDescentantsTables = Table.Combine({MainTable, CombinedDescendents}),
Solved! Go to Solution.
Hi @JVos,
you're right, sorry, my bad 😞
Please read my new blog post, it should help with the performance: https://community.powerbi.com/t5/Community-Blog/Transitive-Closure-in-Power-Query/ba-p/782678
Hi @JVos,
the code should look like the example below if you still use the solution from https://community.powerbi.com/t5/Power-Query/Recursive-query-to-derive-indirect-relationships/td-p/7....
TableOfDescendents = Table.AddColumn(MainTable, "TableOfDescendents", each fnTransitiveRelationTable(MainTable, [ROUTING_ID_From])), TableOfAllDescentantsTables = Table.Combine({MainTable, TableOfDescendents}),
If it is still slow, try the following:
TableOfDescendents = Table.Buffer(Table.AddColumn(MainTable, "TableOfDescendents", each fnTransitiveRelationTable(MainTable, [ROUTING_ID_From]))), TableOfAllDescentantsTables = Table.Combine({MainTable, TableOfDescendents}),
Hi @Nolock,
Your proposal omits the indirect relations in the final result. Note that the initial code - in the solution you refer to - was:
TableOfDescendents = Table.AddColumn(ChangedType, "TableOfDescendents", each fnTransitiveRelationTable(ChangedType, [From])), TableOfAllDescentantsTables = Table.Combine({ChangedType, Table.Combine(TableOfDescendents[TableOfDescendents])}),
To see what happens step-by-step, I broke the last line into two lines of code:
TableOfDescendents = Table.AddColumn(ChangedType, "TableOfDescendents", each fnTransitiveRelationTable(ChangedType, [ROUTING_ID_From])), CombinedDescendents = Table.Combine(TableOfDescendents[TableOfDescendents]), TableOfAllDescentantsTables = Table.Combine({ChangedType, CombinedDescendents}),
Now you propose in fact to remove to combine TableOfDescendents[TableOfDescendents], which makes that the indirect relations are not in the end result.
Hi @JVos,
you're right, sorry, my bad 😞
Please read my new blog post, it should help with the performance: https://community.powerbi.com/t5/Community-Blog/Transitive-Closure-in-Power-Query/ba-p/782678
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.