many to many due to blank

[powerbiexpert22]

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

[Angith_Nair]

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.

[powerbiexpert22]

Hi @Angith_Nair ,

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

[v-xingshen-msft · 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:

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.

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:

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!

、

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]

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