2

I am using Power Query Editor in Excel for Microsoft 365.

Table A has a column ID that holds a unique identifier. This identifier is usually numeric, but it sometimes contains text. Given this, I've set this column's data type to Text.

Table B also has a column ID with the same properties as described above.

I am performing an inner join (merge) on these two tables with ID as the join column:

= Table.NestedJoin(A, {"ID"}, B, {"ID"}, "B", JoinKind.Inner)

This inner join is matching nulls, which I did not think was supposed to happen.

enter image description here

As a workaround, I've added an Applied Step to filter out null values in these columns. But, I'd like to get to the root of the problem.

How may I prevent null values from matching? Or am I incorrect in my understanding that two nulls should never match each other?

Dave
  • 1,051
  • 3
  • 19
  • 41

1 Answers1

4

According to the Power Query language specification, the equality operator is defined on null values.

enter image description here

Please also see the discussion:

https://stackoverflow.com/questions/53119042/power-query-merging-on-null-values

In summary, it looks like you should remove or replace nulls from your tables before joining if you don't want them to be considered equal.

FlexYourData
  • 6,430
  • 2
  • 6
  • 21