Not able to join data from 2 tables in my report

[Pp110784]

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.

[Anonymous]

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.

[Pp110784]

That'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!

[Anonymous · Accepted solution]

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.

[Pp110784]

My problem was similar to this. Somehow the first query that I had used in my report to pull data was not working properly. It was not referencing the table with it's name, instead as 'Query1'. So I had to edit the query and pull the data again. This time I saw the table was being referenced with it's table name. Then I joined the 2nd table with it and it worked fine. Had to re do my report because of it, but at least we won't face any issues in the future because of this.