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

[Lesposto]

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

[Thomas_Daubert]

Hello,

You may use COALESCE function to bypass the null problem. COALESCE - DAX Guide

You can replace null with "" or other char.

[Lesposto]

Actually I've done the same steps written here in the "Merge columns to create a new column" section: https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9....

And the result was the one of the previous message.

Is there any other way to merge columns?

[HotChilli]

It would require a bit of investigation with datatypes and null values between power query and DAX.

I think if you use 'Merge Columns' feature in Power Query, it will handle it.