Date bug with Sharepoint file sources

[Sascha1]

As mentioned in these threads:

- http://community.powerbi.com/t5/Integrations-with-Files-and/Date-issues-with-Sharepoint-file-sources...

- https://community.powerbi.com/t5/Desktop/Sharepoint-folder-date-problem/m-p/382489/highlight/false#M...

PowerBI has a bug since the March release when it comes to reading files from Sharepoint and using the date fields provided by Sharepoint. This not only affects the desktop but also the service.

[eFeM135]

I reported this issue on 13th of March (here). Downgrading indeed 'resolves' the issue.
Now I'm getting the 'please upgrade Power BI Desktop' messages, I just followed the installation process again and still have the issue. Version 2.56.5023.1021 64-bit.

I will downgrade again and hope to get full resolution in the next release. Sharepoint/OneDrive is a Microsoft product and PowerBI should work well with that.

Note the issue is not only returning Error, but also having datetimezone offsets due to wrong assumptions on the datatimezone returned in the Date Modified field of Sharepoint.Files API... I would be glad to get rid of the work around to transform the information returned to get it matched with the datetime in UMT (or my timezone CET)...

[eFeM135]

@Sascha1,

No sense anymore in downgrading Power BI Desktop, as the published version will now also crash on the Power BI Server ("We couldn't parse the input provided as a DateTimeZone value").

As a workaround, I added a line to power query [xxx = Table.ReplaceErrorValues(xxx, {{"Date modified", null}}),] and added the [try ... otherwise null] to logic depending on [Date Modified] (I needed to shift the datetime to UMT since I got Californian time) to still get through the scheduled refresh, accepting that the fact this field really is meaningless. Also sorting on the [Date Modified] field is useless now...

Is it that difficult that the Sharepoint.Files() function returns a true datetimezone field for [Date Modified], rather than a text field (up to last February 2018 release) using the sharepoint settings (localization and timezone)?

[v-qiuyu-msft]

Hi all, 

In my environment, the SharePoint site region is US and local machine which has Power BI desktop run is French, getting data use SharePoint folder data source, the [Date modified] column doesn't contain errors. The Power BI desktop version is the latest one 2.56.5023.1021. 

In your scenario, please try to run the same desktop version as ours then test again. If issue persists, would you please share detail steps for us to reproduce the issue? 

Best Regards,
Qiuyun Yu 

[eFeM135]

@v-qiuyu-msft,

I had put the information in my original post, but here you are with the requested information. The date settings for France are identical to the ones of Belgium (neighbouring country), and the timezone shift UStoBE is simular to yours of HKtoFR.

Desktop regional settingsPowerBI Desktop informationSharepoint file sync and PowerQuery date modifiedSharepoint regional settingsSharepoint file information

[eFeM135]

@Vicky_Song,

I see you changed the status to 'Needing Info', reason why I inform you I provided the required information. I'm thinking on asking the sharepoint admin to change the regional settings for his site, but find this will potentially resolve my issue but not serve the Power BI community.

I will wait with this change request another week to give the product team the ability to improve their produt.

[Anonymous]

I have had the issue regarding dates where the Power BI sharepoint connector is doing some type transformations that cannot be edited. 

The issue for me arrises when the sharepoint site and power bi are in different regions that have different date formats. Changing either Power BI or the sharepoint site fixed the issue for me, however this is not possible in all cases. Ideally microsoft can change the sharepoint connector so that is just imports the sharepoint site data as all or text data type so then the end user can set the data types the same way most other connectors operate.

[eFeM135]

As a matter of follow-up:

Date modified issue with Sharepoint.Files function persists in April 2018 version

Had a funny thought that going to the cloud the desktop settings still matter so much. I hope that product designers will become more aware of internationalization in the near future... or may be we can convince everybody to change all the system settings to UMT.

[vancromy]

I'm also experiencing the same behaviour since the update. I'm still on March 2018 version (on Desktop) and refreshing a report on BI Desktop works fine but attempting to do this on BI Server always fails claiming a #datetimezone error. Shame we can't just opt to not import the date modified and date created fields...

I've just gone in and changed the API Version clause in the source line (advanced editor) to 14 instead of 15 and that's done the trick. Saves the hassle of having to downgrading PBI as others have suggested. 🙂

API Version 14 brings through dates in this format: DD/MM/YYYY HH:MM:SS

API Version 15 brings through dates in this format: DD/MM/YYYY HH:MM:SS +/-HH:MM

If I'm honest I really like that Version 15 brings through the +/- HH:MM data because it would make conversions between UTC/GMT and BST much easier but that seems to break the datetime field when refreshing on BI Servers in Europe.

[eFeM135]

@vancromy

The issue described in this post is already in the API Version 15 context, but addresses an issue when the  datetime regional setting of the sharepoint is different from the one in the pbix model (typically being the one of your computer).

I must assume you have no issues because both entities are following the DD/MM/YYYY HH:MM format.