cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eFeM135 Frequent Visitor
Frequent Visitor

Sharepoint.Files returns Error for datetime fields as of March 2018 version

Yesterday I installed the March 2018 (2.56.5023.942 64-bit) version of Power BI Desktop, upgrading from February 2018 (2.55.5010.641 64-bit) version. Since then the Sharepoint.Files returns Error for many dates as the locale for sharepoint is "en-US" en the one on my desktop is "nl-BE".

Uninstalling PowerBI Desktop and installing the February 2018 version again resolved the issue.

 

In a way this is related to a post mid last year, but until now it returned text which we could transform to a proper datetime using the  = Table.TransformColumnTypes(Source, {{"Date modified", type datetime}}, "en-US") command.

7 REPLIES 7
Microsoft v-yulgu-msft
Microsoft

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

Hi @eFeM135,

 

Please try to change the Locale in desktop (File->Options and settings->Options) to fit the datetime format.

 1.PNG

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
eFeM135 Frequent Visitor
Frequent Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

This is an unacceptable workaround, as this would mean that I need to translate all the other date- and number- fields which are matching my regional settings (nl-NL) using the optional locale parameter.

 

The API from Sharepoint.Files either uses a hardcoded 'en-US' locale and returns a valid date (not a text in US-format which I need to transform like I currently do), or provides an optional locale as we have for most of the M-script variants.

 

BTW: I also need to shift the datetime with -8 hours from US-California time to CET... another 'feature' from this API. I strongly suggest to let the product team have a look at this - what I call a - BUG.

eFeM135 Frequent Visitor
Frequent Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

I'm being whether my problem is solved, with a handful people having viewed my posting... wondering whether I'm on the wrong forum or whether the issue is not understood. Assuming the latter: The first extract is in 'working' the february version, the second with the march version:

v_feb.pngv_mar.png

 

Nevertheless the february version is not 100% as is returns text in a datetimezone field, I still can work through it... but I cannot do anything with 'Error'...

Note the issue is not only regionalization but also timezone related... IMHO these should both be handled by optional parameters in the Sharepoint.Files function

Jimmytacks Occasional Visitor
Occasional Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

I have the same problem using Sharepoint.Files() in PowerQuery in Excel. I don't have the option of using older software versions.

 

This issue is that the implementer of the Sharepoint.Files() function has made the decision to try to parse the Date Modified and Date Created properties of each file, and that parsing is not adequate, for whatever reason.

 

There are 2 options that will help:

- Sharepoint.Files() outputs the text values as additional columns, so that at least we could try parsing ourselves

- Sharepoint.Files() does a better job parsing dates that are in a format such as: "M/dd/yy hh:mm AM", which is where the problem appears to be

mgp4444 Frequent Visitor
Frequent Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

I don't see any workaround -- for me, this works in PBI Desktop but when I publish to PBI Service I get blanks for date created, modified, etc.  

jitalia Visitor
Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

Feb 2019 and still this bug is present.

I found a workaround by looking at the [APIVersion], and changing it from 15 to 14

 

 

APIVersion is appended to the end of the Sharepoint file location in the "Source" step,

 

 

Wingman Visitor
Visitor

Re: Sharepoint.Files returns Error for datetime fields as of March 2018 version

I had the same. It is a clear bug.

 

Try using Sharepoint.Contents - function instead.

 

It uses Date/timezone datatype in the "Date modified" and "Date created" -columns. The navigation is a bit different and might require you to write some steps if the data is in different folders.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,642)