Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |