Forum Discussion

Buzz123's avatar
Buzz123
Advocate I
8 years ago

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

  • fenixen's avatar
    fenixen
    Advocate II
    6 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.


    Regards,
    Lydia

    • Buzz123's avatar
      Buzz123
      Advocate I
      8 years ago

      Anonymous 

      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:

       

       

       

       

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        same issue for me

  • eFeM135's avatar
    eFeM135
    Advocate II
    8 years ago

    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).

  • rylach's avatar
    rylach
    Helper II
    8 years ago

    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:

     

     

    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.