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.