Power BI with Dates in Sharepoint Lists - UTC and Timezone Issues

[Em_Jay_Jay]

Hi Community,

I have read quite a few posts on challenges with dates, UTC and timezones using Power BI with Sharepoint Lists.  And I am still struggling to get my head around it to be honest.

I am currently switching a report from the Sharepoint 2.0 connector to the odata API to take advantage of speed, individual column selection and internal names and am encountering the problem.  It should also be noted that this report can be run against a number of different Sharepoint Sites (one per Project), some of which are run out of Europe, some out of the US.  I am in the UK if that makes any difference.

Example – Project/Site 1 - Europe

Sharepoint    Sharepoint 2.0         odata API (text)                 odata API converted to date/time/timezone
28/02/2023   28/02/2023 00:00    2023-02-27T23:00:00Z     27/02/2023 23:00:00 +00:00

31/03/2023   31/03/2023 00:00    2023-03-30T22:00:00Z     30/03/2023 22:00:00 +00:00

My assumption here is that it has stored as UTC, which is -2 hours in Summer, -1 hours in Winter.  The date part with the odata API is 1 day earlier than Sharepoint.

Example – Project/Site 2 – US

Sharepoint     Sharepoint 2.0        odata API (text)                 odata API converted to date/time/timezone
10/10/2022   10/10/2022 00:00    2022-10-10T20:00:00Z     10/10/2022 20:00:00 +00:00
14/11/2022   14/11/2022 00:00    2022-11-14T22:00:00Z     14/11/2022 22:00:00 +00:00

Not entirely sure on this one.  If the record has been entered in the US, is UTC now +8 or +10?  The date part with the odata API is the same as Sharepoint.

Ultimately, I just want Power BI to show the same date as the date that appears in Sharepoint.  I’m not really bothered about the time element.  I’m just not sure if there is a solution to cover all scenarios.

Please comment if you have any suggestions.  Many thanks.

[Em_Jay_Jay]

I ran some further tests and I am still quite confused as to how Power BI Desktop 2.0 Connector adjusts the UTC time.

I tested with a Personal 365 Sharepoint List as follows:

Sharepoint List Date/Time Entered                                                                       20/07/2023 00:00

Power BI Desktop 2.0 Connector                                                                          20/07/2023 00:00

Power BI Desktop 1.0 Connector                                                                          20/07/2023 07:00

Power BI Desktop odata/API (Text)                                                                       2023-07-20T07:00:00Z

Power BI Desktop odata/API (Converted to Date/Time/Timeone)                       20/07/2023 07:00:00 +00:00

Power BI Desktop odata/API (Converted ToLocal)                                               20/07/2023 08:00:00 +01:00

The first thing I didn’t understand here was the 7 hour UTC offset.  However, after some reading, for Personal 365 it seems to take Regional Settings from OneDrive, which in my case (not sure why) were set to “UTC -8 Pacific”.  So, given the date is Summer time, it becomes 7 hours.  So, UTC time is fine based on that logic.  Microsoft documentation suggests that the Sharepoint 2.0 Connector is simply doing a “ToLocal” to adjust, and to replicate this if using 1.0 or API, but as can be seen from my example, this does not work.  This clearly takes the local PC Timezone – in my case this is “UTC +0”, but again, adjusted for Summer time, the ToLocal is correct at 08:00 based on the UTC.

So, the big question here, is how does Power BI Desktop 2.0 Connector know to make the adjustment based on the Sharepoint Timezone setting – in my example “UTC -8 Pacific”.  And how do you replicate this in 1.0 or the odata API without hardcoding.

[rubayatyasmin]

SharePoint regional setting. This is why your SharePoint 2.0 connector is showing the same time as what's displayed in SharePoint.

As you've already found out, using the OData API, the timestamp is returned in UTC. Therefore, the behavior you're observing is expected, because the API does not know the SharePoint site's regional setting and your local machine's time zone setting. Therefore, it cannot automatically apply the correct time zone offset as the SharePoint List connector does.

To handle this in Power BI when using the SharePoint OData API, you need to:

1. Understand the SharePoint site's regional setting and note the time zone.
2. Determine your own local time zone setting.
3. Calculate the difference between your SharePoint site time zone and your local time zone.
4. Add or subtract this difference from the timestamp in Power BI, depending on whether your local time zone is ahead or behind the SharePoint time zone.

While the solution is not elegant and does involve some manual steps, it's the only workaround currently available as Power BI does not automatically handle time zone differences when using the OData API to connect to SharePoint.

As for 1.0 see this doc. https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-online-list#connect-to-a-sharepo...

[Em_Jay_Jay]

Thanks.  I will do some thinking about how to adjust the time based on the Sharepoint Regional Settings.  I had seen the document on 1.0 and it seems to assume that the Sharepoint and Local Timezones are the same.  The "ToLocal" adjusts the UTC based on the local PC timezone - if the Sharepoint Timezone is different it doesn't work of course.  

[rubayatyasmin]

you are right. 

Happy to help. 

[Em_Jay_Jay]

I did just have one further example which is confusing me even further (if that is even possible).  From the discussion/investigation it seems like Sharepoint and the 2.0 connector display the date/time based on UTC adjusted by the Regional Settings of the Sharepoint Site – this explains the results shown in the earlier examples.

I also have another site, with Regional Settings set as UTC+1, where I get the following expected results:

Sharepoint and 2.0 Connector

Date1      31/03/2023 00:00:00

Date2      28/02/2023 00:00:00

1.0 Connector

Date1      30/03/2023 22:00:00

Date2      27/02/2023 23:00:00

API

Date1      2023-03-30T22:00:00Z

Date2      2023-02-27T23:00:00Z

So, Date1 adjusts by 2 hours with UTC+1 and Summer Time, Date2 adjusts by 1 hours with base UTC+1.  All fine.

However, on a different site, with the Regional Settings also set as UTC+1, I get the following results:

Sharepoint and 2.0 Connector

Date1      10/10/2022 00:00:00

Date2      14/11/2022 00:00:00

1.0 Connector

Date1      10/10/2022 20:00:00

Date2      14/11/2022 22:00:00

API

Date1      2022-10-10T20:00:00Z

Date2      2022-11-14T22:00:00Z

So, the October record of 00:00:00 is UTC-8, the November record of 00:00:00 is UTC-10.  So this is definitely not adjusting based on UTC+1, and also the 2 hour variance between the two is odd.  If it was a Timezone with Daylight Savings then I would only expect a 1 hour variance.

Is there anything else which could be impacting this strange adjustment?  Many thanks 🙂

[rubayatyasmin]

there are a few factors that could contribute to the discrepancies in the SharePoint and API timestamps you're experiencing.

1. User's regional settings: SharePoint adjusts times based on the regional settings of the site, as you've mentioned. However, it's also important to note that individual users can have their own regional settings that override the site settings. If these users have created or modified an item, the timestamp could reflect their regional settings, not the site's.

2. Daylight Saving Time: Daylight Saving Time (DST) could be impacting these timestamps. In many regions, DST causes the clock to go forward 1 hour in spring (often in March) and go back 1 hour in autumn (often in October). These changes could explain why the October record is UTC-8 and the November record is UTC-10, which includes 1 hour DST adjustment plus an unexpected 1 hour difference. But the DST adjustment should only affect the times if the location follows DST, which may not apply to a UTC+1 setting depending on the specific region.

3. Server Time Settings: The servers that host the SharePoint environment or the connectors might have their own time settings, which could interfere with the times you're seeing.

4. Cache or synchronization issues: Sometimes, old data might be cached or there might be delays in data synchronization, causing inconsistencies in timestamps.

also is it possible that your sharepoint has third party integration that is causing the problems?

[Em_Jay_Jay]

Thanks again for all of your help.  There are clearly a lot of elements that could be involved.  What is interesting for me though is that Sharepoint (and the 2.0 Connector in Power BI), still manage to adjust all the "random" UTC times back to 00:00, which is how they were entered.   I wonder if that algorithm would really take into account all of those possible elements and know to adjust UTC on one date/row by +1 hour, another by +2 hours, another by -8 hours, another by -10, when all are based on UTC+1 (at least in the Site Settings).   

[rubayatyasmin]

about the algorithm, it's upto MS to take this into account. Try create an issue. See if that helps. 

[rubayatyasmin]

Hi, @Em_Jay_Jay 

This is indeed a common problem when working with SharePoint dates, the issue being that SharePoint internally stores all its dates and times in UTC format. When SharePoint displays a date, it is automatically adjusted to the local timezone of the site where the date is displayed.

The issue arises when connecting SharePoint to Power BI through an API or connector, as Power BI fetches the data in UTC format. This is what is causing the discrepancies you are seeing between SharePoint and Power BI.

To solve this issue, you need to add an adjustment in Power BI to convert the UTC time to the correct local timezone. You can create a new column in your Power BI dataset that applies a timezone conversion to the datetime field.

If you want to display just the date and are not concerned about the time, you can simply take the date part of the DateTime value. But you need to add a day to the dates that got shifted to the previous day because of the UTC conversion.

example code

NewDate =
IF (
HOUR ( YourTable[OriginalDateTime] ) >= 22,
YourTable[OriginalDateTime] + 1,
YourTable[OriginalDateTime]
)