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.
