cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Buzz123
New Member

Date issues with Sharepoint file sources

The March update (2.56.5023.861 64-bit) got installed this morning.  Unfortunately, it's broken the ability for the Power BI Desktop Query to understand dates from a sharepoint folder source.  This file was working correctly last night (before the update).

 

The first step of the query is:

Source = SharePoint.Files("https://<ourplace>.sharepoint.com/sites/<oursite>/", [ApiVersion = 15]),

 

The 5th and 6th columns are date modified and date created respectively.  All fields in these columns are filled with Error.  The details are: 

DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value.
Details:
21/06/2016 9:49 a.m.

 

Here in New Zealand, the date format is dd/mm/yyyy, and Regional Settings, Locale for import is set to English (New Zealand).  I've also checked the locale settings on the sharepoint site and it is the same.

 

I've tried changing the format of the column in the query editor, but it still reports the same error.  

 

I've tried going back to a previous version of desktop, but I got an error indicating I couldn't open the newer file with the older version.

 

I've replicated the error on another computer.

 

So, any ideas?

 

Michael

 

 

15 REPLIES 15
fenixen
Advocate II
Advocate II

any chance the bug will actually get fixed?  The bug is in excel as well, changing to API version 14 gives me blank results on my query... and I guess it could cause other problems. 

rylach
Helper II
Helper II

Similar issue, getting list of files from SharePoint I get the created and updated dates in different locales, I don't know what it depends on:

 

obraz.png

 

Also, changing the locale (Options-> Current File -> Regional settings) in Power BI Desktop doesn't seem to affect the way the data is refreshed in Power BI Service (I get data refresh errors).

 

Any suggestions ?

 

R.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
eFeM135
Advocate II
Advocate II

@Buzz123,
Deinstalling the Power BI Desktop, and then installing the older version will work... at least with me. Luckily I'm keeping the versions I'm installing in my DSL (Definitive Software Library).

 

= SharePoint.Files("https://my.sharepoint.com/sites/sitename/", [ApiVersion = 15])

 

change APIVersion in the source line from 15 to 14.

 

= SharePoint.Files("https://my.sharepoint.com/sites/sitename/", [ApiVersion = 14])

 

the ApiVersion 14 works with dates other than US

uzzysan
Frequent Visitor

Smiley Very Happy It worked! (changing the API version from 15 to 14). Thank You @ozhug !

thank you, it works fine for me !!!

v-yuezhe-msft
Microsoft
Microsoft

@Buzz123,

Right click your column in Query Editor of Power BI Desktop, select "Change Type->Using Locale", then change type to DateTimeZone. I can successfully convert the data type in March update(2.56.5023.861 64-bit) of Power BI Desktop.
2.JPG1.JPG3.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft 

Thanks for your instructions, but it's not working for me. 

 

Even before I "change type, using locale.." there is an error reported in the dates columns from the sharepoint.files line:

PowerBIerror.jpg

 

 

 

 

so I believe there's something broken when getting the data via the sharepoint.files command.  I can get other date columns to behave correctly, and I've managed to replace what I was after with a sharepoint.tables query.  The sharepoint.tables query takes a while to run, so I'd prefer to go back to the sharepoint.files command.

 

Anything else I could try?

 

Michael

 

 

@Buzz123,

I suspect that there are extra spaces in the date values after using sharepoint.files command to import data. Please remove these extra spaces following the guide in the similar thread below and change data type.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Same issue. Was working fine before, now its not. I tried the changing data tyapes and removing spaces. Nothing seems to be working. The error happens at the first step when the data is being read.

When I opened up Power BI desktop to have another look at this, I got notfied that a new version was available. Hoping that it would be sorted, I instaled the new version (2.56.5023.942), but the problem is still there.

 

As mentioned above, the error occurs in the first line of the query (source = ...). The Date Modified and Date Created fields are defaulting to a Date/Time type data type as expected. However the cells in both columns show an error. Selecting an individual cell shows this error:

DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value.
Details:
6/03/2018 9:05 a.m.

 

Changing type to anything else (including text, and date/time/timezone with locale set to English (NZ)), still returns the same error. So, I can't get in to manipulate the date field.

 

(I've just tried to do the same thing in Excel, and the data loads correctly...

 

QueryInExcel.jpg

 

 

 

 

 

 

 

 

 

 

 

So, that would seem to indicate that there's a problem with the query engine in Power BI desktop.)

 

 

Any ideas?

 

Michael

Same issue here. I downgraded to the previous version and it works ok again.

Actually, I reported this issue on 13th of March with a silly proposal to change regional settings... they should know what the impact is on power queries... look here

I have the same problem.  Working before.  not working now.  shows an error when at the SOURCE step.   change local to Welsh, which matches the exact format of the data 15/02/2017 13:06 but the error persists.

same issue for me

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors