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

Solution
The code giving me problems was run against a date
and a datetime
column. Since date
s are sorted before datetime
s, 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.
Good thought – it’s annoying that there’s no mismatched type error, though.