question

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 asked Lz-3068 commented

Null = Null problem

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

99796-image.png




power-query-not-supported
image.png (12.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 edited

@RonRosenfeld-3452

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}


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @RonRosenfeld-3452

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

99908-demo.png



demo.png (14.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 edited

@RonRosenfeld-3452

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)
     )

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

OK. That makes sense as to why the workaround works.

More importantly, to me anyway, is why the null in row 5 does not behave the same as the null in row 3.

In other words,

Table.Column(combo,"A"){2}=null =>True , and
Table.Column(combo,"A"){4}=null =>True,

but

Table.AddColumn(combo, "isitNull", each [A]=null) => True for {2} and null for {4}

and that behavior changes once I expand the Table column

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
1 Vote"
RonRosenfeld-3452 answered Lz-3068 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Glad I could help @RonRosenfeld-3452

  • Thanks for checking by Buffering the table. It was late (here) when I replied and I didn't take the time

  • You're absolutely right, each try [A] = null otherwise "something" is enough

  • Note that my example with List evaluation wasn't good => Just updated it

0 Votes 0 ·