Power Query: Detect null created by Table.NestedJoin JoinKind.FullOuter

Question

With Table.NestedJoin JoinKind.FullOuter, a null may be written into columns when there is a value in the right table "key" that does not exist in the left table "key".

However, unlike a null that is in the left table because the cell is empty, this created null does not = True with the formula [column] = null.

For example:

Table1
Note the null in row 3

Table2

Joined Table
The null in row 5 was created as a result of the Join

Custom Column
added with formula =[A]=null
note the different results for the null

MCode to reproduce the above

let
    Source1 = Table.FromRecords({
        [A="a"],
        [A="b"],
        [A=null],
        [A="c"]
    }),
    type1 = Table.TransformColumnTypes(Source1,{"A", type text}),
    Source2 = Table.FromRecords({
        [A="c"],
        [A="d"]
    }),
    type2 = Table.TransformColumnTypes(Source2,{"A", type text}),

    combo = Table.NestedJoin(type1,"A",type2,"A","joined",JoinKind.FullOuter),
    #"Added Custom" = Table.AddColumn(combo, "Custom", each [A]=null)
in
    #"Added Custom"

Explanations and suggestions as to how to deal with this would be appreciated.

Edit In addition to the above, doing a Replace will also only replace the null in row 3, and not the null in row 5. Seems there is something different about these two nulls.

Note: If I Expand the table, the null in Column A will now test correctly.

Out of curiosity, what's the outcome if you use [A] is null, or even ([A] as null) = null? — Jeroen Mostert
– Jeroen Mostert, Commented May 26, 2021 at 11:49
@JeroenMostert That did not work either. One way that seemed to work with both nulls was List.Count(List.RemoveNulls({[A]}))=0, but that seems a rather awkward method, and certainly not in accord with documentation I have found. And it doesn't handle other issues, like the inability of Replace to work. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 26, 2021 at 13:05
Has all the hallmarks of a bug then. I was thinking that maybe somehow a nothing value got created, but that should have been weeded out with an explicit null conversion. A comparison with null should yield only a true or false regardless of types involved, not null, as PQ does not use SQL's three-valued logic. — Jeroen Mostert
– Jeroen Mostert, Commented May 26, 2021 at 13:11
@JeroenMostert The problem turns out to probably be a consequence of PQ's evaluation model. In any event, by forcing evaluation with a Buffer function, the two nulls behave the same. See my answer below. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 27, 2021 at 1:44

Ron Rosenfeld · Accepted Answer · 2021-05-27 02:09:01Z

Asking the same question on the Microsoft Q&A forum pointed me to the possibility of an issue with the Power Query Evaluation model and also this article on Lazy Evaluation and Query Folding in Power BI/Power Query.

By forcing evaluation of the table with Table.Buffer, both nulls now behave the same.

So:

let
    Source1 = Table.FromRecords({
        [A="a"],
        [A="b"],
        [A=null],
        [A="c"]
    }),
    type1 = Table.TransformColumnTypes(Source1,{"A", type text}),
    Source2 = Table.FromRecords({
        [A="c"],
        [A="d"]
    }),
    type2 = Table.TransformColumnTypes(Source2,{"A", type text}),

//Table.Buffer forces evaluation
    combo = Table.Buffer(Table.NestedJoin(type1,"A",type2,"A","joined",JoinKind.FullOuter)),

//IsItNull now works
    IsItNull = Table.AddColumn(combo, "[A] = null", each [A] = null)
  in
    IsItNull

It also seems to be the case that try ... otherwise will also force an evaluation. So instead of Table.Buffer, the following also works:

    ...
    combo = Table.NestedJoin(type1,"A",type2,"A","joined",JoinKind.FullOuter),
//try ... otherwise seems to force Evaluation
    IsItNull = Table.AddColumn(combo, "[A] = null", each try [A] = null otherwise null)

Quite counterintuitive -- you would expect lazy evaluation to stop being lazy once you actually compare a column value, since we clearly need it at that point :P The second approach is arguably the best in terms of giving PQ the most leeway to keep evaluation of the other columns lazy (since we only ask for the value of one), though it may not make a difference in practice.

Aleksei Zhigulin · Accepted Answer · 2021-05-27 15:36:40Z

Very interesting case. Indeed, the behaviour of last null is counterintuitive in most possible implementations. If you wish to get the same behaviour for both kinds of nulls, try this approach:

= Table.AddColumn(combo, "test", each [A] ?? 10)

Quite interesting, the similar code doesn't work:

= Table.AddColumn(combo, "test", each if [A] = null then 10 else [A])

Moreover, if we want to improved the previous code by using the first syntax we still get unexpectable result (10 instead of 20 for the last null):

= Table.AddColumn(combo, "test", each if [A] = null then 10 else [A] ?? 20)

Сurious, applying ?? operator also fixes the problem with initial column. Now there are regular nulls in A column:

= Table.AddColumn(add, "test2", each [A] = null)

So, if we don't need any calculations and just want to fix invalid nulls, we may use such code:

= Table.TransformColumns(combo, {"A", each _ ?? _})

The column doesn't matter and for joined column the result is the very same:

transform = Table.TransformColumns(combo, {"joined", each _ ?? _}),
add = Table.AddColumn(transform, "test", each [A] = null)

Problem may have to do with the Evaluation Model. See my answer below
I do not know how to correctly name the source of the problem, but fact is that after FullOuter join we have defective null, which even doesn't have a type (this becomes clear after applying this code, for example - Table.TransformColumns(combo, {"A", Value.Type})). About overcoming this problem, it may be a matter of taste, but I'd prefer the solution with ?? operator, so, I extended my answer.
I would think any operation that forces an eager evaluation would work.

Collectives™ on Stack Overflow

Power Query: Detect null created by Table.NestedJoin JoinKind.FullOuter

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related