Forum Discussion
Solved
Recursive query to derive indirect relationships
I have a table with direct relations between so-called 'routings' (let's say: process steps). Now I want to derive all indirect relations. For example when routing 1 directly preceeds 2, 2 directly p...
Nolock: I worked out the solution to prevent endless loops. In short: the 'froms' that are already done, are remember in a list and not offered to be done in a next recursion. At the end of the query self-referencing transitions are removed.
let Source = Excel.CurrentWorkbook(), tmpInput = Source{[Name="tmpInput"]}[Content], ChangedType = Table.TransformColumnTypes(tmpInput,{{"From", Int64.Type}, {"To", Int64.Type}, {"RelationType", type text}}), // get list of all descendants fnTransitiveRelationList = (sourceTbl as table, curToBeDoneList as list, alreadyDoneList as list) as list => let curNumber = List.First(curToBeDoneList), rowsStartingWithCurNumber = Table.SelectRows(sourceTbl, each [From] = curNumber), alreadyDoneList = List.Combine({alreadyDoneList, {curNumber}}), result = if Table.IsEmpty(rowsStartingWithCurNumber) and List.IsEmpty(curToBeDoneList) then {} else let toList = Table.Column(rowsStartingWithCurNumber, "To"), nextToBeDoneList = List.Distinct( List.Combine( { List.RemoveFirstN(curToBeDoneList, 1), toList } ) ), nextToBeDoneListNoAlreadyDone = List.Difference(nextToBeDoneList,alreadyDoneList), recursiveResultList = @fnTransitiveRelationList(sourceTbl, nextToBeDoneListNoAlreadyDone, alreadyDoneList), curRecursiveResultList = List.Distinct( List.Combine( { toList, recursiveResultList } ) ) in curRecursiveResultList in result, // create a table from all descendants with a from column and relation type = indirect fnTransitiveRelationTable = (sourceTbl as table, from as number) as table => let recursiveList = fnTransitiveRelationList(sourceTbl, {from},{}), recordList = List.Transform(recursiveList, each [From = from, To = _, RelationType = "Indirect"]), result = Table.FromRecords(recordList) in result, // add a column TableOfDescendants TableOfDescendents = Table.AddColumn(ChangedType, "TableOfDescendents", each fnTransitiveRelationTable(ChangedType, [From])), // combine input table with new descendants TableOfAllDescentantsTables = Table.Combine({ChangedType, Table.Combine(TableOfDescendents[TableOfDescendents])}), // distinct on columns From and To Result = Table.SelectRows(Table.Distinct(TableOfAllDescentantsTables, {"From", "To"}), each [From] <> [To]) in Result
Hi JVos,
I've written the recursive part of the task and you have now a list of all descendent for every row.Unfortunately I can't finish it now because of time pressure. I'll continue tomorrow if you don't finish it by yourself till then.
EDIT: The working solution with comments. It creates a list of all descendents for every row and then merges the result with the origin table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYpfMotTkEqVYHYiQMaoQSIUJqhBIhSmqEIhrhioE4ppjClkgCcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [From = _t, To = _t, RelationType = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"From", Int64.Type}, {"To", Int64.Type}, {"RelationType", type text}}),
// get list of all descendants
fnTransitiveRelationList = (sourceTbl as table, curToBeDoneList as list) as list =>
let
curNumber = List.First(curToBeDoneList),
rowsStartingWithCurNumber = Table.SelectRows(sourceTbl, each [From] = curNumber),
result =
if Table.IsEmpty(rowsStartingWithCurNumber) and List.IsEmpty(curToBeDoneList) then
{}
else
let
toList = Table.Column(rowsStartingWithCurNumber, "To"),
nextToBeDoneList = List.Distinct(
List.Combine(
{
List.RemoveFirstN(curToBeDoneList, 1),
toList
}
)
),
recursiveResultList = @fnTransitiveRelationList(sourceTbl, nextToBeDoneList),
curRecursiveResultList = List.Distinct(
List.Combine(
{
toList,
recursiveResultList
}
)
)
in
curRecursiveResultList
in
result,
// create a table from all descendants with a from column and relation type = indirect
fnTransitiveRelationTable = (sourceTbl as table, from as number) as table =>
let
recursiveList = fnTransitiveRelationList(sourceTbl, {from}),
recordList = List.Transform(recursiveList, each [From = from, To = _, RelationType = "Indirect"]),
result = Table.FromRecords(recordList)
in
result,
// add a column TableOfDescendants
TableOfDescendents = Table.AddColumn(ChangedType, "TableOfDescendents", each fnTransitiveRelationTable(ChangedType, [From])),
// combine input table with new descendants
TableOfAllDescentantsTables = Table.Combine({ChangedType, Table.Combine(TableOfDescendents[TableOfDescendents])}),
// distinct on columns From and To
Result = Table.Distinct(TableOfAllDescentantsTables, {"From", "To"})
in
Result