Performance issue at expanding or combining tablecolumn

[JVos]

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}),

[Nolock]

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}),

[JVos]

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.

[Nolock · Accepted 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