cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EithneDangan
Regular Visitor

Date Errors when loading Files from SharePoint folder into Power Query

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:

 

SharePoint PowerQuery Date JPG.jpg

 

6 REPLIES 6

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
Super User II
Super User II

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)

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 hereDate issues forum.jpg

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
Super User II
Super User II

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.

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.  

 

 

FIles with Dates.png

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors