cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Advocate I
Advocate I

Re: Dataflows DateTime Error

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

Highlighted
Responsive Resident
Responsive Resident

Re: Dataflows DateTime Error

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
Highlighted
Advocate I
Advocate I

Re: Dataflows DateTime Error

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

Highlighted
Responsive Resident
Responsive Resident

Re: Dataflows DateTime Error

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

Highlighted
Frequent Visitor

Re: Dataflows DateTime Error

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.

Highlighted
Helper I
Helper I

Re: Dataflows DateTime Error

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.

Highlighted
Helper I
Helper I

Re: Dataflows DateTime Error

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (12,219)