Inner Join 3 tables

[joseph_Dev]

Hello, 

I have 2 queries:

1) ActivePipelines (its column of unique IDs is 'Pipeline ID')

2) ActiveBacklogs (its column of unique IDs is 'new_backlogid').

The relationship between Pipeline and Cashflow, and between Backlog and Cashlow is in both cases 1-Many.

I have a query named ActiveCashflows. It contains many columns including cra1c_backlog_cashflow and cra1c_pipeline_cashflow (the IDs from ActiveBacklogs and ActivePipelines respectively).

In ActiveCashflows, if column cra1c_backlog_cashflow contains data, then cra1c_pipeline_cashflow is null, and vice versa. They never both contain data and they're never both null. How do I for every row in ActiveCashflows, if cra1c_backlog_cashflow is not null, add the related row from ActiveBacklogs (and its columns), and if cra1c_pipeline_cashflow is not null, add the related row from ActivePipelines (and its columns)

I tried:

let
    // Join ActiveCashflows with ActiveBacklogs based on "cra1c_backlog_cashflow"
    JoinedBacklogs = Table.NestedJoin(ActiveCashflows, {"cra1c_backlog_cashflow"}, ActiveBacklogs, {"new_backlogid"}, "Backlogs", JoinKind.LeftOuter),

    // Expand the nested table for ActiveBacklogs
    ExpandedBacklogs = Table.ExpandTableColumn(JoinedBacklogs, "Backlogs", Table.ColumnNames(ActiveBacklogs)),

    // Join ActiveCashflows with ActivePipelines based on "cra1c_pipeline_cashflow"
    JoinedPipelines = Table.NestedJoin(ActiveCashflows, {"cra1c_pipeline_cashflow"}, ActivePipelines, {"Pipeline ID"}, "Pipelines", JoinKind.LeftOuter),

    // Expand the nested table for ActivePipelines
    ExpandedPipelines = Table.ExpandTableColumn(JoinedPipelines, "Pipelines", Table.ColumnNames(ActivePipelines)),

    // Combine the results
    Combined = Table.Combine({ExpandedBacklogs, ExpandedPipelines})
in
    Combined

 but, i am ending up with double the rows I have in ActiveCashflows (i have 212, but getting 424 from this query)

Thank you in advance for any assistance.

[AnalyticsWizard · Accepted solution]

@joseph_Dev 

It looks like the issue with your approach is that you're joining `ActiveCashflows` twice with `ActiveBacklogs` and `ActivePipelines`, and then combining the results, which duplicates each row in `ActiveCashflows`. To correctly join these tables so that each row in `ActiveCashflows` is extended with either related `ActiveBacklogs` or `ActivePipelines` data (but not both), you can follow these steps in Power Query:

1. Perform a conditional join in a single step instead of creating separate joined tables and then combining them. You can use `Table.AddColumn` to add a new column which conditionally determines which table to join to based on whether the `cra1c_backlog_cashflow` or `cra1c_pipeline_cashflow` is not null.

2. Here is the adjusted M code to achieve this:

let
// Source Tables
ActiveCashflows = ...,
ActiveBacklogs = ...,
ActivePipelines = ...,

// Add a conditional column to determine the join key and the join table dynamically
PreparedCashflows = Table.AddColumn(ActiveCashflows, "JoinData", each
if [cra1c_backlog_cashflow] <> null then
{ [cra1c_backlog_cashflow], "Backlog" }
else
{ [cra1c_pipeline_cashflow], "Pipeline" }
),

// Custom function to dynamically join tables
JoinTables = (row) =>
let
JoinKey = row[JoinData]{0},
JoinType = row[JoinData]{1},
JoinedTable = if JoinType = "Backlog" then
Table.NestedJoin({row}, {"cra1c_backlog_cashflow"}, ActiveBacklogs, {"new_backlogid"}, "JoinedData", JoinKind.LeftOuter)
else
Table.NestedJoin({row}, {"cra1c_pipeline_cashflow"}, ActivePipelines, {"Pipeline ID"}, "JoinedData", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(JoinedTable, "JoinedData", Table.ColumnNames(JoinType = "Backlog" ? ActiveBacklogs : ActivePipelines))
in
Expanded{0}, // Return the first (and only) row expanded

// Apply the custom function to each row
JoinedAndExpanded = Table.TransformRows(PreparedCashflows, JoinTables),

// Convert the list of records back to a table
FinalTable = Table.FromRecords(JoinedAndExpanded)
in
FinalTable

3. Explanation:
- **PreparedCashflows**: This step adds a column to `ActiveCashflows` containing both the join key and an indicator of which table to join.
- **JoinTables**: This is a custom function that performs a dynamic nested join based on the data in each row.
- **JoinedAndExpanded**: Applies the `JoinTables` function to each row of `ActiveCashflows`.
- **FinalTable**: Converts the list of records generated by `JoinedAndExpanded` back into a table format.

This approach ensures that each row in `ActiveCashflows` is extended only once, either with data from `ActiveBacklogs` or `ActivePipelines`, depending on the content of the `cra1c_backlog_cashflow` and `cra1c_pipeline_cashflow` fields.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍