SharePoint List Refresh Changing Time to UTC

[stillmakinnoise]

Hi all, 

I have developed a set of dashboards from a SharePoint Online List, but I am struggling with some Date/Time issues when refreshing the data in Power BI Service, and I could really use some help. 

The date/time data from our SharePoint Online List is imported into the PBI Desktop Query as a UTC date/time. I was able to address this issue fairly easily by adding a simple Local Time column. However, when I publish the report and establish a refresh schedule, the time reverts to UTC in Power BI Service on the very first refresh. 

Has anyone else experienced this type of issue when utilizing a SharePoint Online List? I'd really like my team to be able to see the most up-to-date operational data and an accurate time is key to that. 

Additionally, while I haven't noticed it in my most recent troubleshooting, I have noticed in the past that the original UTC date/time column in the PBI Desktop Query changes to be reflective of the local timezone (i.e. no longer showing 00:00, but showing -06:00) while retraining the UTC time (i.e. the UTC date/time is February 2, 2018 at 7:23 AM, and the Date/Time/Timezone column will indicate 2/2/2018 7:23:00 AM -06:00 instead of +00:00). 

I'd be very grateful to anyone who could provide guidance on this issue. Is it an underlying issue in the SharePoint list that needs to be addressed? I have tried the refresh on multiple Reports using the same SharePoint Online List and all result in the same time discrepancy. The issue with the PBI Desktop query is odd, but certainly not my primary concern. 

[Anonymous]

I am guessing you have already seen this post: https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337

If not, hope it helps!

[GilbertQ]

Hi @stillmakinnoise

I can confirm that all the Power BI Servers are set to UCT, and what happens is if you are using the DateTime in your query to get it from the local server (which in this case will be the Power BI Servers) it will always be UTC.

I have a blog post explaining the details, as well as how to overcome this by setting your timezone into the Power Query Editor

https://www.fourmoo.com/2017/10/03/power-bi-did-you-know-all-power-bi-services-servers-are-in-utc-no...

[stillmakinnoise]

Hello @GilbertQ,

I appreciate your reply, however I don't quite understand how the solution in your blog applies to the scenario I outlined in my initial post; maybe my understanding is incomplete. You confirm the Power BI servers have a default UTC paramter, but it's not necessarily clear how I address the issue within my dataset. 

After following the steps in your linked post, I followed on to your Daylight Savings Time post, but it's not clear which query I should amend the M within, and when I attempt to do so in my base Sharepoint Online table I am left with no EOF and the added language doesn't apply. 

[GilbertQ]

Hi there, sorry for the confusion.

What I was trying to show you, is that you will need to modify your date time column in your dataset to reflect the timezone that you are in. So that when Power BI refreshes the dataset, it will then apply the correct timezone so that the dates and times appear correct on the reports.