Power BI Refresh Date/Time

Alex Wong

From today's Power BI User Group, I saw @Rebecca Sundquist using Power Query's M code of get local time to get a table of last refreshed date/time for the Power BI. I remember there was reason why I didn't do that, and can't remember.

Now, I remember, so I want to put this out here so others can be aware, as well as for myself to remember why I had to do something more complex. @Erik Leaver maybe somehow post this along with the recording for others to see?

Using DateTime.LocalNow() only “works” while you are on Power BI Desktop, as the Desktop knowns your local time zone. When published to the Power BI Service, it operates in UTC time.

Another method is to do timezone hours adjustment, that works well IF you are at a location that doesn't have daylight savings, otherwise, every time daylight savings change, you have to account for the +1 and -1. So that's not workable for me.

Instead I use a web service that will give me the date/time I need for the timezone and it auto accounts for daylight savings: https://worldtimeapi.org/api/timezone/America/New_York

Below is the Power Query M-code for doing this.

let
Source = Json.Document(Web.Contents("https://worldtimeapi.org/api/timezone/America/New_York")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"abbreviation", type text}, {"client_ip", type text}, {"datetime", type datetimezone}, {"day_of_week", Int64.Type}, {"day_of_year", Int64.Type}, {"dst", type logical}, {"dst_from", type datetime}, {"dst_offset", Int64.Type}, {"dst_until", type datetime}, {"raw_offset", Int64.Type}, {"timezone", type text}, {"unixtime", Int64.Type}, {"utc_datetime", type datetime}, {"utc_offset", type duration}, {"week_number", Int64.Type}})
in
#"Changed Type"

Rebecca Sundquist

@Alex Wong, you are right! I thought the Service used the time zone for the tenant's location, but I was wrong. I tested, and Power BI Service is using UTC as documented: DateTime.LocalNow - PowerQuery M | Microsoft Learn.

Thank you for sharing your solution that works with daylight savings! The worldtimeapi is simpler than the custom function I found here: How-To: Convert UTC to Your Local Time Zone in Pow... - Microsoft Fabric Community.

Unfortunately, I cannot access the worldtimeapi from my work laptop, though it works for me from my phone. It must be blocked by my organization. If only it were simple!

Alex Wong

@Rebecca Sundquist
WorldTimeAPI has been failing for me lately and when looking into their website, while it is simple quick and useful, there is no reliability guarantee.

Thinking back at what else I can do, I found a more self sustaining path: make your own web API.

Power Automate flow using HTTP trigger can be used for this, very simple and effective, ONE trigger and ONE action flow.

The datetime property to return has this expression: convertFromUtc(utcNow(), 'Eastern Standard Time'). The “Eastern Standard Time” timezone auto-corrects with daylight savings (UTC-4 or UTC-5).

I also added a timezone and abbreviation property, though really not needed, but gives the datetime a little more “context”.

In Power BI:

Alex Wong

@Rebecca Sundquist
added 2 more good properties (using Compose to convert to ET first so 1 more action):

convertFromUtc(utcNow(), 'Eastern Standard Time')

Body:

{
"datetime": "@{outputs('Compose_DateTime_Converted_ET')}",
"day_of_week": @{dayOfWeek(outputs('Compose_DateTime_Converted_ET'))},
"day_of_year": @{dayOfYear(outputs('Compose_DateTime_Converted_ET'))},
"timezone": "Eastern Standard Time",
"abbreviation": "ET"
}

Schema:

{

"type": "object",

"properties": {

"datetime": {

"type": "string"

},

"day_of_week": {

"type": "integer"

},

"day_of_year": {

"type": "integer"

},

"timezone": {

"type": "string"

},

"abbreviation": {

"type": "string"

}

}

}