When importing a OData v4 compliant data feed that includes DateTime or DateTimeOffset properties as a Dataflow, import errors are received such as:
"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"
This can be reproduced by using the Northwind reference V4 source from the OData specification:
Process to reproduce:
Create new Dataflow (with any name)
Use "https://services.odata.org/V4/Northwind/Northwind.svc/" as the source, click "Next"
Choose the "Employees" endpoint. Note that the data loads correctly into PowerQuery, and click "Transform Data"
Click "Next" on the transform data screen
Choose "Load to new entity" on the Map entities screen, leaving all the defaults in place and choose Next
Click "Create" to create the import
After a few moments, observe that the data feed import has failed. There does not appear to be any workaround for this problem on the OData side.
Hi @27pchrisl This looks like a permission problem. I have 2 environments for testing it. My personal environment I'm the administrator and I was able to follow the steps you provided without any issues. Data Flow has been created successfully.
In the second environment I'm not the owner or the administrator and I'm getting this issue after few seconds:
There was an error while creating this entity. Details: Failed to create entity with logical name cr38e_employees and object type code -1. Exception: Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed. User: 99324e48-7394-ea11-a811-000d3a579806, PrivilegeName: prvCreateEntity, PrivilegeId: 341e3ebf-74b8-4335-84f3-7f617bb7d081, Required Depth: Basic, BusinessUnitId: a47f0694-a911-e911-a98f-000d3a1c9b20, MetadataCache Privileges Count: 1731, User Privileges Count: 692 at Microsoft.Crm.BusinessEntities.SecurityLibrary.ThrowPrivilegeDeniedException(IUser user, RolePrivilege rolePrivilege, ExecutionContext context) at Microsoft.Crm.BusinessEntities.SecurityLibrary.GetMaxPrivilegeDepthForUserAcrossBusinessUnitsInternal(Guid user, Guid privilege, ExecutionContext context, Guid& businessUnitId) at Microsoft.Crm.BusinessEntities.SecurityLibrary.CheckPrivilegeGlobalDepth(Guid user, Guid privilege, ExecutionContext context) at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass35_0.<CreateInternalHelper>b__1() at Microsoft.Crm.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName) at Microsoft.Crm.Metadata.EntityService.CreateInternalHelper(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context). [Entity: Employees]
I'm not getting any issue related to the date.
Hi @danvarga, appreciate your looking into this!
Can you confirm that your Dataflow imports successfully, and in the CSV log generated in the "Refresh history" after import there are no errors, and that the CSV indicates that 9 records were correctly upserted?
I get 2 records upserted, and 7 non-importable with the error message I provided previously. I have attached a screenshot of the two import runs on this particular flow (both showing the same errors), and the CSV I got back from the import log (converted to xlsx, since I cannot attach CSV here).
We are based in the UK and have encountered the same problem with Dataflows/Power Query and dates not in the US format. The work around that solves the problem for us is, in Power Query, to change Options -> Project Options ->Locale from English (United Kingdom) to English (United States).
It doesn't make logical sense, but it solves the issue. If you are in the US or using US format dates, please ignore this suggestion.
I should add that it is dates where the first characters in UK format (Day) are greater than 12. So, 29/12/2019 fails, but 08/07/2020 is OK because Dataflow thinks 08 is the month.
Yes, changing the Locale to US does cause the import to succeed. So that's good news! Thanks @LooseChippings
I believe it points to an issue somewhere downstream then, since OData DateTimeOffsets are always in ISO8601 format and don't change format based on locale. It would look like it's a problem with the service that PowerQuery is pushing into, since PQ itself does load the dates correctly as "Date/Time/Zone". PowerBI also has no issue with the feed when using that on the desktop.
Is that your understanding as well?
Yes. It is not OData and occurs when using any Dataflow connector. This is not new. We first encountered the problem around three years ago.
We have British Summer Time, when the clocks go back one hour between March and October, and PowerBI does not recognise this change. So 2020-08-07 00:00:00 becomes 2020-08-06 11:00:00 in Power BI. This is also a known issue and we add one hour to dates in Power Query.
Check out the News & Announcements to learn more.
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.