Inner Join 3 tables

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 👍