cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BluRonnie
Advocate I
Advocate I

Dataflows DateTime Error

I try to build a Dataflows to load an excel file into CDS. 

 

The issue I got is a date column. For all the rows in the excel, the value in that column is 24/12/2019 (as DD/MM/YYYY). Power Query has no problem to recognise it as a date format. However, when I run the dataflows, I constantly got the error message as below:

 

Load to Common Data Service failed. Details: Reason: Bad Request, Error code: 0x80040239, Message: DateTime is less than minumum value supported by CrmDateTime. Actual value: 01/01/0001 00:00:00, Minimum value supported: 01/01/1753 00:00:00.

 

I have tried to change the mapping field in CDS to Data Only, Date Time. but still the same error. 

 

I have also tried to format the column in Power Query to Date, Date/Time, or Date/Time/Timezone, still the same error. 

 

If I exclude the column in the mapping, the dataflows run successfully. 

 

Please help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
BluRonnie
Advocate I
Advocate I

I've managed to make the dataflows work by adding additional step in the Power Query:

Table.AddColumn(#"Replaced value", "New Date"each Date.ToText([Date], "yyyy/MM/dd"), type date)
and I used the "New Date" in the mapping and it worked. 
 
Just not sure why I have to use MM instead of mm. when using mm, it gives me 00 instead of 12

View solution in original post

Glad to hear you got it working. For reference, I think mm (lower-case) refers to minutes, rather than months. The documentation is a bit inconsistent on this, but the safest is to use MM (upper-case) for months, and mm (lower-case) for minutes 

View solution in original post

5 REPLIES 5
BluRonnie
Advocate I
Advocate I

I've managed to make the dataflows work by adding additional step in the Power Query:

Table.AddColumn(#"Replaced value", "New Date"each Date.ToText([Date], "yyyy/MM/dd"), type date)
and I used the "New Date" in the mapping and it worked. 
 
Just not sure why I have to use MM instead of mm. when using mm, it gives me 00 instead of 12

View solution in original post

Glad to hear you got it working. For reference, I think mm (lower-case) refers to minutes, rather than months. The documentation is a bit inconsistent on this, but the safest is to use MM (upper-case) for months, and mm (lower-case) for minutes 

View solution in original post

robertopolo
Advocate I
Advocate I

I would like to add something to the topic since I had the same error. In my case the problem is that the entity in CDS only accepts dates in American format, that is, Month / Day / Year and in my data source the format is Latin Day / Month / Year. The solution I used was to simply change the language of my browser from Spanish to English and so when I go through Power Query I don't need to make any changes in the date field, it just formats it to the source field and it works correctly.

Had the same issue. I have just changed the column data type from date to text in Power Query and it also resolved the issue in CDS data flow.

FYI, several self experiments later these are the best two options that work reliable for importing dates to CDS in my setting. Date formats in CDS and Personal settings are set to German (Switzerland). For data imports, I prepare dates in the data source in either ISO format YYYY-MM-DD or American date format MM/dd/yyyy. In both cases formatted as text.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,037)