Date Errors when loading Files from SharePoint folder into Power Query

Question

Hello everyone.&nbsp;I would love some help.&nbsp; I am using powerquery to load files from a SharePoint folder.&nbsp; Any files uploaded since March 17th (St. Patricks Day - Luck of the Irish!) are coming in with Errors for Date Created and Date Modified&nbsp;&nbsp;Error message: -&nbsp;DataFormat.Error: We couldn't parse the input provided as a DateTime value.Details:3/24/2021 4:00 PM&nbsp;I have trawled through google searches and forums.&nbsp; Any Help would be gratefully appreciatede.g.&nbsp; See screen shot below:&nbsp;&nbsp;

wynhopkins · Answer

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 · Answer

It looks like a region settings/date format problem.&nbsp; 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 · Answer

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

hotchilli · Answer

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.&nbsp; 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 · Answer

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

Forum Discussion

Date Errors when loading Files from SharePoint folder into Power Query

6 Replies

Related content

OData Error When Connecting to SharePoint Online

Getting Error Cannot load model

Error loading Power BI report

Error in Dataflow Gen 2 loading from Excel file on SharePoint Online

Error when loading SharePoint List merged with OnPremises data