cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eFeM135
Advocate II
Advocate II

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.

8 REPLIES 8
Wingman
Regular Visitor

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.

Sept. 2020 and this issue is still open (PBI Desktop 2.85.681.0 Windows an PoweBI Loacle is German)

 

Only Workaround for me: switching to SharePoint.Files("SHAREPOINT", [APIVersion = 15]) and than navigate to folder ....

jitalia
Frequent Visitor

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,

 

 

mgp4444
Regular Visitor

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.  

v-yulgu-msft
Microsoft
Microsoft

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.

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

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.

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

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

Top Solution Authors
Top Kudoed Authors