Forum Discussion
2 data sources joined with a Key whose value may be different at some instances
I personally would not use the text "NULL" in the fields and that can be confusing vs a legitimate NULL, which is really a BLANK() in DAX. Use something else, like "None." If you are using a real null (blank()) then you can still create the relationship but you'll have values show up on one side and not the other, and can make some of the visuals have blanks where you have things like item numbers or whatever.
As to case sensitivity, it depends. DAX is not case sensitive. "None" = "NONE" = "noNE"
Power Query though is case sensitive. You would want to wrap such columns with Text.Lower(), Text.Upper, or Text.Proper() to have the same case on each side of a merge.
If that is what you see in DAX, that is the text Null and null, not a real null. A real null is blank.
If you type that in Power Query, it shows up green, showing it is a keyword:
Then shows up in italics in the Power Query window
Then shows up blank in the DAX model, and would equate to the BLANK() function, and can be trapped with IsBlank(Table[Field]), or Table[Field] = BLANK() would evaluate to TRUE().
So that Null and null are text if that is DAX. If it is Power Query it is also text, as a legit null shows up as null there.
All null values will join a the same thing in Power Query, and all BLANK() in DAX will form a filter relationship with other BLANK() values.
Hi edhans : Thanks for your response! 🙂 I am now thinking if I have to create this "bridging" table with ALL "Pipeline" names between the 2 sources [table 1 & 2] then probably I also add another column- 'Scheduler' since that the main info I need from table 2! Then I won't need table 2! It will be a lot of manual work to enter all those info, but may be worth it! Best, Sohana
Community ChampionSounds good Anonymous - you are starting to see how important a good model is. I think a good model is half the battle. So much of the garbage DAX I've written over the years would have been much better and easier if I had a better understanding of modeling back then. 😁
Please mark one of these posts as the solution so others can find it and know it is resolved.
Hi edhans : Good day! 🙂 As I am buildin the bridge table [the middle one], I find there are some 'Pipeline' in table1 don't match with that in table2 and also vice versa. 1st column is associated with the database table that has different events info and the 2nd column is for scheduler info.
1. Can I use 'NULL' for those values where I don't have any matching value rather than keeping the cell empty? power BI is case-insenstive whether I use "NULL' or 'null', right?
2. Will it create error when trying to use this bridge table with null values
3. will i still miss events/ scheduler info for the NULL values?
Much appreciated in advance! Best, Sohana
- edhans
I personally would not use the text "NULL" in the fields and that can be confusing vs a legitimate NULL, which is really a BLANK() in DAX. Use something else, like "None." If you are using a real null (blank()) then you can still create the relationship but you'll have values show up on one side and not the other, and can make some of the visuals have blanks where you have things like item numbers or whatever.
As to case sensitivity, it depends. DAX is not case sensitive. "None" = "NONE" = "noNE"
Power Query though is case sensitive. You would want to wrap such columns with Text.Lower(), Text.Upper, or Text.Proper() to have the same case on each side of a merge.
Hi edhans thanks for the response. I actually meant NULL/null rather than text 'NULL', sorry for the ''/ ""! Since I am trying to join table1 and the bridge table [on Column, ‘Genscape Pipeline’] to pull events info and then join the bridge table with table2 on ‘Endur Pipeline’: if I have Null or keep it blank on any of the 2 columns – I will have same results? Many thanks in advance.