Power BI Blog: Refreshing Time on Power BI Service – Part 1

Welcome to our Power BI blogs.  Over the next two weeks, we will show you how to manage time-refreshing on Power BI Service.

When you manage dashboards on Power BI Service and try to refresh anything involving Date/Time values, you will discover that Power BI Service uses Coordinated Universal Time (UTC) instead of your local time.  You might work around this by manually adding on the necessary time-zone adjustments (e.g. UTC+8 for Beijing) in your visual measures or in the Power Query M code, but it can get messy where daylight saving is relevant.

In the following example, we prepared two Date/Time variables, one with a measure, =NOW(), and one with an M function, =DateTime.LocalNow().    

They are matching our local Sydney date and time on Power BI Desktop, but once we publish the report to Power BI Service,    

we go back ten [10] hours in time and have the standard UTC now.

We will demonstrate a couple of methods to tackle the problem, and also how to manage daylight saving.

Obtain Date and Time from a Web API

It’s not rocket science that if we get data from SydneyTime.com we will obtain the local time for Sydney.  A drawback is that it can cost extra time to obtain web-based data.  Also, robustness of the data source needs to be considered.

For this example, we will use WorldTimeAPI.org.  When we enter the URL    

in Power BI to Get Data From Web,    

Power BI will use the M function combination Json.Document(Web.Contents()) to return a record.    

Drilling down datetime provides as a single text value.    

Then we can convert it to a table, rename it and change the data type to Date/Time/Timezone.    

The text string obtained from WorldTimeAPI.org can’t be changed to Date/Time type directly, so we need the above step.  However, this action also introduces time zone information back into the data and we’ll need to remove that.  We can use the DateTimeZone.RemoveZone() function:

After that, we can change the data type to Date/Time:    

It should be noted that this Date/Time value will be refreshed every time Power Query is refreshed (by calling the API), no matter we are using Power BI Desktop or Power BI Service.

That’s it for this week.  Next week we will show you an alternative approach using Power Query M functions.  Please stay tuned.  For more thoughts and insights, please visit http://www.sumproduct.com.