Forum Discussion

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

    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