Datasets not accepting valid datetimes from Mashup Engine / Power Query

[Anonymous]

When I create a new .pbix file with Power BI Desktop and create a new blank query with M expression:

let
Source = {
try #datetime(1, 1, 0, 23, 59, 59.999999),
#datetime(1, 1, 1, 0, 0, 0),
#datetime(99, 12, 31, 23, 59, 59.9999999),
#datetime(100, 1, 1, 0, 0, 0),
#datetime(9999, 12, 31, 23, 59, 59.9999999),
try #datetime(10000, 1, 1, 0, 0, 0)
},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}})
in
#"Changed Type"

I get the result I expect within the Power Query UI:

However when I load the table to model:

2 datetime values that were fine in Power Query are now blank. I suspect that all datetimes prior to 0100-01-01 00:00:00 are no longer considered valid datetimes while Power Query documentation states that these should be valid.

https://docs.microsoft.com/en-us/powerquery-m/sharpdatetime

In Power BI Desktop these invalid dates are handled silently but in the Power BI Service/AAS they cause datasets to no longer be refreshable without adding error-handling.

Would it be possible to again allow datetimes between 0001-01-01 00:00:00 and 9999-12-31 23:59:59?

[v-yingjl]

Have submited this issue internal to confirm(ICM: 314557558), would update here as soon as possbile if there is any progress about it.

Best Regards,
Community Support Team _ Yingjie Li

[Anonymous]

@v-yingjl Thank you for replying, any news on this bug?

[v-yingjl]

Update from PG team:

All the behavior described here is by design: the only date within our supported range in the 9999 one and it's propagating to the model correctly. The ask here seems to be a feature request to expand the range of valid dates to minimize the chance of errors when doing refresh in the service.

Best Regards,
Community Support Team _ Yingjie Li

[Anonymous]

Thank you for getting back to me, @v-yingjl. However, the documentation on #datetime states:

Creates a datetime value from numbers representing the year, month, day, hour, minute, and (fractional) second. Raises an error if these conditions are not true:

• 1 ≤ year ≤ 9999
• 1 ≤ month ≤ 12
• 1 ≤ day ≤ 31
• 0 ≤ hour ≤ 23
• 0 ≤ minute ≤ 59
• 0 ≤ second < 60

And should thus allow any datetime between 0001-01-01 00:00:00 and 9999-12-31 23:59:59.

0100-01-01 00:00:00 correctly propagates to the model so how can only 9999-12-31 23:59:59 be in the supported date range? It's the datetimes < 0100-01-01 00:00:00 that don't propagate to the model when the criteria in the documentation state they should.

Could you please shed more light on how this would be a request for a new feature in the service when Power BI Desktop version 2.106.582.0 64-bit (June 2022) shows this behavior?