Forum Discussion
Inner Join 3 tables
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
Combinedbut, 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.
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 FinalTable3. 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 👍
1 Reply
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 FinalTable3. 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 👍