Parse error querying SharePoint Online list using 2.0 Implementation

[dantshin]

I'm getting I'm getting an error in date/time fields with SharePoint Online list, this is when using the 2.0 (Beta) implementation.

Note: I don't have this issue when using the 1.0 implementation (which is a bit slower).

The error mesage I'm getting is:

DataFormat.Error: We couldn't parse the input provided as a DateTime value.

Details:

16/11/2020 18:04

I've tried changing the locale (to English UK), but no luck so far.

[talk2frank]

none of this works for me sadly, its just broken as the parse is hidden in the dataload rather than allowing me to manage it myself

[talk2frank]

sasme problem but my system is full uk locale and still wont do anything but fail to parse and shows an error

[dantshin · Accepted solution]

Today, I refreshed my query, and the issue has magically fixed itself.
I believe this is an issue caused by my locale settings: I checked the following

1. Workstation: I had reset them all to English (Canada) and specified the Long Date format to be dd MMM yyyy. My workstation has also gone through a shutdown and restart cycle.
2. M365: I've also ensured my M365 language and regional settings are set to English (UK)
3. SPO site (datasource): Set the Regional Settings to ensure English (UK) is also set (I have Site collection admin rights to the site)

[V-lianl-msft]

Hi @dantshin ,

Could you provide an image of the dates so that we are able to see if there are any issues directly.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dantshin]

Hi Liang,

Here you go:

[mahoneypat]

Add a Replace Values step on that column replacing the space with a "T" before you try to change to DateTime.

Regards,

Pat

[dantshin]

Hi Pat,

Unfortunately, that doesn't work. Even before I can change the type, Power Query is already determining the column type - it already knows it is a Date/Time type.

Here is a screenshot in PowerQuery right after I've selected the list (in the Navigation step). I haven't had a chance to change the type yet.

I do wonder if it's something to do with the locale - as some dates do make it through (if either month or date is 12 or less) - but I have already forced the locale to be en-GB (English-UK)