Finding All Recursive Connections in a Relationship Table

To solve the recursive relationship mapping problem in Power BI, we can use Power Query to build a function that iterates through the relationships table and recursively finds all connected IDs for each starting ID, both direct and indirect. This can be done by first defining a custom recursive function that identifies all direct connections for a given ID, then recursively retrieving all connected IDs for each of those direct connections. Once the function is set up, we apply it to each row in the relationship table, generating a new column that contains all connected IDs. We then expand this list into individual rows, ensuring that each connection is represented separately. Finally, we filter out cases where the ID is the same as the connected ID and load the transformed data into Power BI for further analysis, such as visualizing connections in a Sankey chart.

Here's the full Power Query M code to achieve this:

let
    // Function to find all related IDs recursively
    GetConnectedIDs = (startID as text, relationsTable as table) =>
    let
        // Get the direct connections for the startID
        DirectConnections = Table.SelectRows(relationsTable, each [ID] = startID),
        // Extract the ToIDs from the DirectConnections
        DirectToIDs = DirectConnections[ToID],
        // Recursively get connected IDs from those direct ToIDs
        RecursiveConnections = List.Transform(DirectToIDs, each GetConnectedIDs(_, relationsTable)),
        // Combine the direct and recursive connections
        AllConnections = List.Distinct(DirectToIDs & List.Combine(RecursiveConnections))
    in
        AllConnections,

    // Load the original relationships table
    Source = Table_Relations,
    
    // Add a column that applies the recursive function to each ID
    AddConnectedIDsColumn = Table.AddColumn(Source, "ConnectedIDs", each GetConnectedIDs([ID], Source)),
    
    // Expand the list of connected IDs into individual rows
    ExpandConnectedIDs = Table.ExpandListColumn(AddConnectedIDsColumn, "ConnectedIDs"),
    
    // Filter out rows where the ID equals the ConnectedID
    FilteredRows = Table.SelectRows(ExpandConnectedIDs, each [ID] <> [ConnectedIDs])
in
    FilteredRows

 This query will generate a table with all connected IDs for each starting ID, excluding the ID itself, and ready for use in visualizations like Sankey charts.