Null = Null problem

Question

When a table is created, an empty cell will be assigned null. Also, when creating a nested joined table with joinkind.fullouter, null will also be written into the table for rows that are present in the right table but not in the left.

The first null responds as documented where [column] = null will return True or False, and Value.Type will return: Type Null.

However, the null returned as a result of the Table.Join(......,JoinKind.FullOuter) does not respond this way.

What is going on?
How can I test for null when the null is created by the Table.Join method?

Note that if I expand the table, the null will now test correctly.

To demonstrate the problem, run the below code.

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, "test [a]=null", each [A]=null),  
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "test Value.Type([A])", each Value.Type([A]))  
in  
    #"Added Custom1"  

In the screenshot, row 3 was created directly; row 5 is the result of a Nested Join operation

Answer 1

@Ron Rosenfeld

I'm not 100% sure but think (& this makes sense to me) that this is due to the Evaluation model where Tables, Lists, Records and let expressions are lazily evaluated

In addition to the doc. I would suggest you read Lazy Evaluation & Query Folding in Power BI / Power Query

Another example with a List:

let  
     Source = List.Select({1,5,10,error 1,20},  
         each _ <= 20  
     ),         // Error  
     FirstTwo = List.FirstN(Source, 2)     
 in  
     FirstTwo  // {1,5}  

Answer 2

That's it!

I buffered the combined Table, forcing evaluation, and [A]=null now evaluates to True or False for all instances.

combo = Table.Buffer(Table.NestedJoin(type1,"A",type2,"A","joined",JoinKind.FullOuter)),
IsItNull = Table.AddColumn(combo, "Test [A] is null", each [A] = null)

Thank you for helping me understand this.

Perhaps it is the case the try...otherwise also forces evaluation. Logical.From is not really needed.

Simply: IsItNull = Table.AddColumn(combo, "[A] = null", each try [A] = null otherwise null) will also do the job.

Answer 3

Hi @Ron Rosenfeld

Works for you (How can I test for null when the null is created by the Table.Join method?)?

let  
    Source1 = Table.FromRecords(  
        { [A="a"], [A="b"], [A=null], [A="c"] },  
        type table [A=text]  
    ),  
    Source2 = Table.FromRecords(  
        { [A="c"], [A="d"] },  
        type table [A=text]  
    ),  
    JoinedFull = Table.NestedJoin(Source1,"A", Source2,"A",  
        "joined", JoinKind.FullOuter  
    ),  
    IsItNull = Table.AddColumn(JoinedFull, "Test [A] is null", each  
        try Logical.From([A] is null) otherwise null, type logical  
    )  
in  
    IsItNull  

Answer 4

Thank you, Lz

I see that it works, and is a lot more succinct than my workaround, but can you explain why it works?

I see that Logical.From([A] is null) returns null for row 5.
Why does using try .... otherwise null coerce it to True?

Answer 5

@Ron Rosenfeld

Not easy to explain. Let's see...
Look at function Logical.From. It returns a Logical from the value argument (expect if the latter is null). In this case the value arg. is "[A] is null" (a Logical test)

With try SomethingLogic otherwise SomethingElse you get a logical in any case. I could have written:

try Logical.From([A] is null) otherwise "Hello Ron" // Works  

On a blank query enter:

=try (1+1=3) otherwise "Bad" // FALSE (not the word "Bad")  

Hope this helps & clarifies things

EDIT and if you wonder why otherwise is used given it seems unecessary, try :):

    IsItNull = Table.AddColumn(JoinedFull, "Test [A] is null", each  
        try Logical.From([A] is null)  
    )