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.
