Forum Discussion

JVos's avatar
JVos
Helper IV
7 years ago
Solved

Performance issue at expanding or combining tablecolumn

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 ...
Need Help
Nolock's avatar
Nolock
Resident Rockstar
7 years ago

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/712958. 

 

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's avatar
JVos
Helper IV
7 years ago

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.