The Oldest Is Not Always the Minimum: Power Query Date-Based Values May Not Compare the Way You’d Expect

, , ,

The task seemed simple enough: Determine the earliest date between two columns, so that this value could be used when generating a date dimension.

A little M code that took the minimum of each column, then the minimum of those minimums seemed to meet the need until—ouch—I discovered it wasn’t finding the earliest date. Why?

let
  OrderCreatedMin = List.Min(SomeTable[OrderCreated]),
  TransactionReceivedMin = List.Min(SomeTable[TransactionReceived]),
  OverallMin = Date.From(List.Min({ OrderCreatedMin, TransactionReceivedMin }))
in
  OverallMin

What do you think? Do you see any problems with the above?

Syntactically, the code is valid. It runs without raising an error, and in some cases will produce the expected value. Not in my case, though. In my situation, one column was of type datetime and the other was of type date—and therein lay the problem.

In Power Query, date-based values (date, datetime and datetimezone) do not sort across types by date. To you and me, #datetime(2020, 1, 25, 1, 1, 1) is less than #date(2023, 1, 25)—but not to Power Query.

Technical Details

In the M language, relational operators can only be used between values of the same type or null.

// works, returning true, as expected.
#date(2020, 1, 25) < #date(2023, 1, 25)
// doesn't work
// instead of returning false, it errors as the values being compared are of two different types
#datetime(2020, 1, 25, 1, 1, 1) < #date(2023, 1, 25) 

However, the Power Query standard library functions that can compare scalar values to compute mins and maxes (like List.Min and List.Max), sort (like List.Sort and Table.Sort) or otherwise perform comparisons (like Value.Compare), tend to be more graceful when encountering values of different types.

While the official rules used are (to my knowledge) undocumented, from what I can tell:

  • First, values are sorted by type.
  • Then, within each type, the values of that type are sorted normally.

Specifically, for date-based values, date values are sorted before datetime values, then come datetimezone values.

let
    Data = { 
        #date(2020, 1, 25), 
        #datetime(2020, 1, 25, 1, 1, 1), 
        #datetimezone(2020, 1, 25, 1, 1, 1, 0, 0), 
        #date(2023, 1, 25), 
        #datetime(2023, 1, 25, 1, 1, 1), 
        #datetimezone(2023, 1, 25, 1, 1, 1, 0, 0)
    },
    Sorted = List.Sort(Data)
in
    Sorted
Screenshot showing sorted list of date-based values

Solution

The code giving me problems was run against a date and a datetime column. Since dates are sorted before datetimes, the earliest date column value was always being returned, even when the datetime column contained a value that came earlier on the calendar.

The fix? Easy! Simply ensure that all values being compared are of the same type. In my case, I needed to convert the computed min of the datetime column to a date before performing the final “pick the min between the two column minimums” comparison.

To implement the fix, if we assume that OrderCreated is of type date and TransactionReceived is of datetime, all that’s needed is simply to move Date.From up one line. This way, the min value computed over TransactionReceived is converted to a date before it is stored in TransactionReceivedMin. With this change, the OverallMin‘s List.Min is now being done on two date values, so works as expected.

let
  OrderCreatedMin = List.Min(SomeTable[OrderCreated]),
  TransactionReceivedMin = Date.From(List.Min(SomeTable[TransactionReceived])),
  OverallMin = List.Min({ OrderCreatedMin, TransactionReceivedMin })
in
  OverallMin

Moral of the Lesson

Beware when comparing a set of values of different types, even if to us humans the values seem like they should naturally compare. If you’re comparing values of different types, most likely, a precursor type conversion is missing.

This is particularly relevant for date-based values, as it is easy for us to assume that they will automatically be sorted by calendar date, regardless of their specific types. Not so.

One thought on “The Oldest Is Not Always the Minimum: Power Query Date-Based Values May Not Compare the Way You’d Expect

Leave a Reply

Your email address will not be published. Required fields are marked *