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
Frequent Visitor

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (69,034)