Power Query and Power BI different behavior in managing null values in concatenation

Hi everyone,

I had the need to create a Primary Key in a "Country" dimension table (concatenating the ZIP_Code and the Country_Name) because I needed to create a relation between this dimension table and the "Sales" fact table (unfortunately some ZIP codes where in common in some Countries, thus I couldn't use the ZIP_Code as Primary Key).

I did it using the Power Query editor exploiting the "&" to concatenate the two columns separated by a "," (in both "Country" and "Sales" tables).

Unfortunately, some values in the "Country_Name" were null. I went ahead the same but I obtained something different from what I expected:

As you can see from the picture, for those raws with "Country_Name" = null the created "Primary_Key_Concat" it is also null.

This creates problems in doing the relation between the two tables, as the dimension table has non-unique values (so a many-to-many relation is created).

So I deleted the "Primary_Key_Concat" column in Power Query and I created it directly in Power BI as a Calculated Column, and I saw that the expected result was reached. In this way I have been able to create the 1-to-many relation between the "Country" and "Sales" table.

Thus, it means that the management of null values in concatenation is different between Power Query and Power BI.

Now, I have reached the hoped result, but I am curious to know:

1. why the behaviors are different?

2. is there any possibility to tell Power Query how to manage null in a concatenation?

Many thanks