Date Errors when loading Files from SharePoint folder into Power Query

[EithneDangan]

Hello everyone.

I would love some help.  I am using powerquery to load files from a SharePoint folder.  Any files uploaded since March 17th (St. Patricks Day - Luck of the Irish!) are coming in with Errors for Date Created and Date Modified 

Error message: - 

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
3/24/2021 4:00 PM

I have trawled through google searches and forums.  Any Help would be gratefully appreciated

e.g.  See screen shot below:

[wynhopkins]

This is a bug being investigated by Microsoft

@EithneDangan  and I investigated further and changing the SharePoint.Files step to use SharePoint.Contents and drill down to the relevant folder avoided the issue highlighted by @HotChilli with US date formatting

Hope that helps someone else

Cheers

Wyn

[HotChilli]

I don't understand your response.

Read my answer again.

Look at what power query did to the first 3 dates. It switched the day and month.  It didn't throw an error because they are still valid dates (even when switched).

This is quite a common issue (especially when importing dates from a transatlantic source)

[EithneDangan]

Thanks HotChilli

I have cleared folder and only have 1 file which I load dated March 27,2021.  File loads, but still getting the issue.  Apparently there was a Windows update.  I have reached out to some MVP's.  GET DATA from SHAREPOINT folder addeding an API 15 parameter at end of M-CODE for source.  It was suggested to remove this, which does remove date ERRORS but doesnt allow load.  I changed the API 15 to 14 and same result.  I have included details here

[v-kelly-msft]

Hi  @EithneDangan ,

Pls check whether there are some extra space at the front of your date column,see the similar thread below:

https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-couldn-t-parse-the-input-provided-as-a/...

Or refer below can help:

https://community.powerbi.com/t5/Desktop/We-couldn-t-parse-the-input-provided-as-a-DateTime-value/td...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[HotChilli]

It looks like a region settings/date format problem.  The date is being interpreted in the opposite format to the one you want (US or UK). 17 is bigger than 12 which would be a valid month so it throws the error.

So that's where you should start the investigation.

[EithneDangan]

Thank you very much your response.  I appreciate every idea.  I had tried that.  All the files in the SharePoint folder are in the exact same date format.  When I navigate to the Sharepoint file directly from within Excel it shows all the files in identical format.  However when I load them file Get Data/From SharePoint Folder the first 3 in the list below load with Date Errors.