Forum Discussion
Helper IINot able to join data from 2 tables in my report
I have an existing Power BI report which is pulling data from 1 table (let's say table A). I have made quite a few modifications to the data to create that report. So I've added a lot of measures and some columns to arrive at the report that I have today.
Now I need to add some data into that report from a different table (let's say table B). The join between these 2 tables is a plan_id column with many to many cardinality. I added the new table and established that relationship in the data model.
Sample Table A data -
| Plan Sponsor Name | Plan Sponsor Source | Plan ID | Plan Name | Plan Source | Investment Name | Investment Source | Effective dt | Plan Filing dt |
| ABC | A | 11 | Plan 1 | A | Investment 1.1 | A | 12/31/2020 | 12/31/2020 |
| ABC | A | 11 | Plan 1 | A | Investment 1.2 | A | 01/31/2021 | 01/31/2021 |
| ABC | A | 11 | Plan 1 | A | Investment 1.3 | B | 01/31/2021 | 01/31/2021 |
| ABC | A | 11 | Plan 1 | A | Investment 1.4 | B | 01/31/2021 | 01/31/2021 |
| ABC | A | 11 | Plan 1 | A | Investment 1.5 | C | 02/28/2022 | 02/28/2022 |
| ABC | A | 11 | Plan 1 | A | Investment 1.6 | C | 02/28/2022 | 02/28/2022 |
| ABC | A | 21 | Plan 2 | B | Investment 2.1 | B | 12/31/2020 | 12/31/2020 |
| ABC | A | 21 | Plan 2 | B | Investment 2.2 | B | 12/31/2020 | 12/31/2020 |
| ABC | A | 21 | Plan 2 | B | Investment 2.3 | B | 12/31/2020 | 12/31/2020 |
| CDE | A | 31 | Plan 3 | B | Investment 3.1 | B | 12/31/2020 | 12/31/2020 |
| CDE | A | 31 | Plan 3 | B | Investment 3.2 | B | 12/31/2020 | 12/31/2020 |
| EFG | C | 41 | Plan 4 | C | Investment 4.1 | C | 01/31/2021 | 01/31/2021 |
| EFG | C | 41 | Plan 4 | C | Investment 4.2 | C | 01/31/2021 | 01/31/2021 |
| GHI | B | 51 | Plan 5 | A | Investment 5.1 | B | 01/31/2021 | 01/31/2021 |
| GHI | B | 51 | Plan 5 | A | Investment 5.2 | A | 01/31/2021 | 01/31/2021 |
| GHI | B | 51 | Plan 5 | A | Investment 5.3 | C | 02/28/2022 | 02/28/2022 |
| PWI1 | A | 6 | PWIP1 | B | 01/31/2021 | 03/31/2023 | ||
| PWP2 | B | 7 | PWIP2 | B | 02/28/2022 | 02/28/2023 |
Table B has information about plan service providers for the plans in Table A.
Sample Table B data -
| Plan ID | Service Provider Name | Provider Role |
| 11 | Provider1 | Role 1 |
| 11 | Provider1 | Role 2 |
| 11 | Provider2 | Role 2 |
| 11 | Provider2 | Role 3 |
| 11 | Provider3 | Role 4 |
| 11 | Provider3 | Role 5 |
| 21 | Provider1 | Role 1 |
| 21 | Provider 4 | Role 2 |
| 21 | Provider3 | Role 3 |
| 31 | Provider1 | Role 1 |
| 31 | Provider1 | Role 2 |
| 41 | Provider3 | Role 1 |
| 41 | Provider 4 | Role 2 |
| 51 | Provider2 | Role 2 |
| 51 | Provider2 | Role 3 |
| 51 | Provider2 | Role 4 |
So I added another page to the report with plan name & its corresponding plan service providers. But the table did not show the data from Table B:
When I looked at the data model I saw the following error:
Can somebody please help me with this?
Changing the existing query for table A to join it with table B will make my report too big (~18M rows), so I don't want to do that. Also, as I mentioned earlier, I have made quite a few changes to the data from table A, so if I change that query now, it is messing up my report big time as all the columns that I added in power BI are getting lost.
Hi Pp110784 ,
Based on the error description, try to review the relationship. Besides, review the query process.
You can view the following document to learn more information.
Solved: how to resolve Power BI error - the key didn't mat... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Pp110784 ,
Based on my testing, please try the following methods again:
1.Create the simple tables.
2.Create the relationship between the two tables.
3.Drag the plan name and plan service providers into the table visual. The result is shown below.
Besides, try to check the relationship and column data.
You can also view the following documents to learn more information.
Many-to-many relationship guidance - Power BI | Microsoft Learn
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Pp110784That's what I'm doing now. It's not working. If you can let me know what might be the probable causes of the error ' The key didn't match any rows in the table.', that will help. Thanks!
Hi Pp110784 ,
Based on the error description, try to review the relationship. Besides, review the query process.
You can view the following document to learn more information.
Solved: how to resolve Power BI error - the key didn't mat... - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.