Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors