Creating a relationship between two tables where one includes NULL values

[Anonymous]

Hello,

I am trying to join one table which has a set of keys + transactions with a member table containing a member key. 

Our Member Table currently contains two keys, let's call them Key 1 and Key 2. Key 2 is the master and will always be populated, as it comes from the CRM. Key 1 will not always be populated. It is only populated for members which have made a transaction. Due to the current setup of our core systems, this is how it will be.

The issue arises when I try to join the transaction table with the member table. Since Key 1 will not always be populated for a Key 2 value, there are plenty of NULL values in the Key 1 column. This creates an issue when I try to create a relationship as Power BI says it's invalid due to "duplicated values", however the only duplicated value is NULL. 

I cannot understand how Power BI can't manage to ignore NULL values in relationship. Is it not possible for me to do this join without removing the NULLs? Yes, I could probably create a many-to-many connection inbetween but that creates so much work for something that is so ridiculously simple as a LEFT JOIN. 

Examples:

Transaction Table:

Transaction Member Key	Transaction Value
TM1	100
TM2	200
TM1	100

Member Table:

Key 1	Key 2	Name
NULL	ABC123	Lorem Ipsum
TM1	DEF456	Ipsum Lorem
TM2	GHI789	Losum Iprem
NULL	123ABC	Aasd Qwerty

I want to create a relationship between Transaction Member Key and Key 1 columns. I will not be able to remove the NULL rows. 

[amitchandak]

@Anonymous , Power bi will not take null on 1 side of the table. You can use replace the value in power bi and replace it with a value like N/A

[rammy]

Hi@amitchandak 

if we replace null with N/A can i get required values from other table which was already matched this null column and values column .does it possible

[Anonymous]

Is there a particular reason for Power BI not managing to handle NULL values? 

Also, wouldn't a value like "N/A" also cause duplicates, creating a relationship that won't work?