Condition to Test Date

I am using Power Query to create a status column that checks the date against a specified date, like so:

However, this gives me the following error:

Expression.Error: We cannot convert the value null to type Logical.
Details:
    Value=
    Type=[Type]

The column does contain empty cells, which I want to report as "null" in the new column. I then tried the following logic, and it errors out as well:

Then I moved the null test to the top, and it finally works:

Why Does Order Matter?

Why does the third query produce the expected results but not the first one? This seems bizarre to me, so if there is something I am missing please let me know.

CodePudding user response：

M is using lazy evaluation in the if statement. If the first statement is true, then it doesn't even bother evaluating the other conditions.

let
    Source = Table.FromList(sample, Splitter.SplitByNothing(),
        type table[Date = nullable date], null, ExtraValues.Error),

    sample = {
        #date(2020, 1, 1),
        "text", null,
        #date(2024, 1, 1)
    },
    filter = #date(2022, 1, 1),

    FirstTry = Table.AddColumn(
        Source , "Comparison", each filter > [Date], Logical.Type),

    WithFallback = Table.AddColumn(FirstTry, "WithFallback",
        each try
            filter > [Date]
            catch (e) => e[Message], type text),
    
    WithPreservedDatatype = Table.AddColumn(WithFallback, "PreserveColumnType",
        each try
            filter > [Date]
            catch (e) => null meta [ Reason = e[Message] ],
            type logical)
in
    WithPreservedDatatype

things to note

• the query steps are "out of order", which is totally valid. ( above sample was referenced "before" its line )
• Errors are propagated so an error on step4 could actually be step2. Just keep going up until you find it.
• the schema says column [Date] is type date -- but it's actually type any.

What you need is to call Table.TransformColumnTypes to convert and assert datatypes

= Table.TransformColumnTypes( Source,{{"Date", type date}})

Now row 2 will correctly show an error, because text couldn't convert into a date