Reply
powerbiexpert22
Post Prodigy
Post Prodigy

many to many due to blank

i am getting many to many relationship due to presence of blank values in column, how to resolve this issue? 

1 ACCEPTED SOLUTION

Hi @powerbiexpert22 ,
According to your description, you want to remove the blank values to make the many-to-many relationship disappear, right?
Then we can remove our blank values in our power query as shown below:

vxingshenmsft_0-1731476186744.png

vxingshenmsft_1-1731476211407.png
The following is an example of Angith_Nair's approach to the situation where there is a unique value in one table and a blank value in another table:
We convert the blank value to an alternative value that exists and defaults to a unique key, and then we can avoid many-to-many relationships when combining with tables that have duplicate values.

vxingshenmsft_2-1731479321413.pngvxingshenmsft_4-1731479494293.png

 

vxingshenmsft_3-1731479351099.png

Lastly, if you have blank values in both tables and you don't want to delete any data, then we can use a bridged table to do this:

vxingshenmsft_5-1731479659851.png

There are unique keys in the bridge table to help you better handle your unwanted many-to-many relationships, if you still have questions you can check out my pbix file, I also found a related document for you, I hope to help you!

vxingshenmsft_6-1731480029414.png

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 




 

 

 

View solution in original post

4 REPLIES 4
Angith_Nair
Continued Contributor
Continued Contributor

Hi @powerbiexpert22 

Try to replace the blank values with a single dummy value and append that dummy value in the one side table. Now the cardinality will be one to many.

Hi @Angith_Nair ,

can you explain with example , I am unable to understand this completely

Hi @powerbiexpert22 ,
According to your description, you want to remove the blank values to make the many-to-many relationship disappear, right?
Then we can remove our blank values in our power query as shown below:

vxingshenmsft_0-1731476186744.png

vxingshenmsft_1-1731476211407.png
The following is an example of Angith_Nair's approach to the situation where there is a unique value in one table and a blank value in another table:
We convert the blank value to an alternative value that exists and defaults to a unique key, and then we can avoid many-to-many relationships when combining with tables that have duplicate values.

vxingshenmsft_2-1731479321413.pngvxingshenmsft_4-1731479494293.png

 

vxingshenmsft_3-1731479351099.png

Lastly, if you have blank values in both tables and you don't want to delete any data, then we can use a bridged table to do this:

vxingshenmsft_5-1731479659851.png

There are unique keys in the bridge table to help you better handle your unwanted many-to-many relationships, if you still have questions you can check out my pbix file, I also found a related document for you, I hope to help you!

vxingshenmsft_6-1731480029414.png

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 




 

 

 

lukiz84
Memorable Member
Memorable Member

remove the blanks? either via powerquery or directly at the source

avatar user

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)