What is Power Query’s time zone?

Recently I had an issue with what DateTime.LocalNow function returns in Power Query. The behavior of the function was more or less well-known (or so I thought), yet the results surprised me. I decided to dig deeper, and in this blog post I’m sharing my findings.

Background

In one of my reports, I needed to get today’s date (in Sydney, Australia) — a fairly common task. I was aware that DateTime.LocalNow returns different values depending on the machine where it’s evaluated, and if I wanted to make sure I got the current date in Sydney, I may need to make some adjustments.

Since my report used on-premises data, and the gateway was located in Sydney, I thought that means DateTime.LocalNow would use the gateway machine’s local date and time, and I’d get the value for Sydney. At least that’s how I remember it working in the past. I was wrong!

Documentation

As you may know, two hours of troubleshooting may save you five minutes of reading documentation.

I decided to check Rick de Groot’s Power Query How, which usually provides more details than Microsoft documentation. Power Query How is the equivalent of DAX Guide for Power Query.

Here’s what Microsoft Learn has to say about DateTime.LocalNow:

While it’s not wrong, I was looking for a bit more. Compare this to what Power Query How says:

Power Query How’s entry has more details. I saw the following note there:

An important point to note is that the DateTime.LocalNow function gives you the datetime value associated with the system on which the query is executed. This means if your dataset is refreshed in Ireland, but you’re in the Netherlands, the function will yield the time in Ireland.

This wasn’t precise enough to address my issue, so I explored a bit further. There also was a link to a related article on Power Query How, where I saw the following:

This was clearly not my experience! My Power BI tenant was in Australia, yet I was getting UTC date and time, despite using an on-premises data gateway located in Sydney.

Investigation

I decided to do some Chris Webb-style investigation. Chris has been blogging every week for more than ten years straight, and it’s possible he wrote on the subject, I just didn’t find it.

First, to ensure I was using my gateway, I created a trivial text file that contained letter A only, and I connected to the file in Power Query:

Then I created another query called Time Zone that used TimeZone.Current to return the name of the current time zone.

Note: I decided to use TimeZone.Current for clarity purposes, and the results were essentially the same compared to my using DateTime.LocalNow or DateTime.FixedLocalNow.

This is the query I used:

#table(
    type table [Time Zone = text],
    {{TimeZone.Current}}
)

This is what I saw on my computer:

I went a step further and added the time zone from the Time Zone query to query A:

Nothing special except I used Table.FirstValue because I didn’t want to bother with expanding a nested table, and I ascribed the data type by using the fourth argument of Table.AddColumn. In this case it was safe to do; in general, you should be careful, as Lars Schreiber writes in his blog post: “Ascribed types in M: why to generally avoid them“.

Finally, I created a new card with two values in my Power BI report before publishing:

Results

I went to the Power BI service and refreshed the semantic model. What did I see?

Even though I’m using the TimeZone.Current function in the Time Zone query, what matters is not where the function is used in the code but where it’s evaluated:

  • The Time Zone query didn’t need to use the on-premises data gateway and the result was the time zone of the Power BI service’s Power Query, which is always UTC, regardless of where your tenant is.
  • Query A used the on-premises gateway and due to lazy evaluation of Power Query it evaluated TimeZone.Current after the gateway was used, resulting in the gateway’s time zone name.

Conclusion Plot twist: in a dataflow

I decided to do a similar test in a dataflow. After I ported the Time Zone query to a dataflow and used my gateway, I saw this:

This was somewhat surprising because in my semantic model that used a gateway, the same query was showing UTC. This dataflow query doesn’t use an on-premises data gateway, and yet it shows my gateway’s time zone. Without a gateway, it was showing UTC.

Because query A would be a computed table (requires Premium or Fabric) and on-premises execution of computed tables isn’t supported (based on the refresh error message that I got anyway), I had to create a third query, Merged A, where I combined two queries. I renamed the original A query to Just A.

For good measure, I used TimeZone.Current in both Just A and Merged A queries:

After I connected to the dataflow in Power BI Desktop, this is what I saw in the Merged A query:

This was a bit surprising for two reasons:

  1. When editing the dataflow, in the query preview of the Time Zone query, I saw AUS Eastern Standard Time.
  2. In the Merged A query, even in the new column, which was created after getting on-premises data, we see UTC.

Here’s how I understand the results:

  1. Time Zone in Just A was evaluated when using the on-premises data gateway, so it shows AUS Eastern Standard Time — no surprises here.
  2. Time Zone from Query, despite what the query preview was showing, did not use the gateway, so it returns UTC, consistent with the semantic model test. Because Merged A is a computed table, Time Zone and Just A are cached first, then merged, resulting in different time zones.
  3. Time Zone in This Query happens in the computed table, and because it’s not done on premises, we again get UTC.

When you change a gateway in Power Query Online, you see the following message:

I guess that the gateway doesn’t apply to all queries when refreshing a dataflow?

Are you wondering what Merged A would show in a semantic model? I’ll show you, together with the previous card visual:

The results are consistent with the previous semantic model test.

And what if in my dataflow I didn’t use the enhanced compute engine? I disabled loading of the Just A and Time Zone queries, and here’s what I saw after getting data from the dataflow:

Now there’s no caching, and the results are the same as in semantic model.

Conclusion

As you can see, the answer to the original question — What is Power Query’s time zone? — is not simple. You’ve got to consider whether you’re using a semantic model or a dataflow, and if you’re using a dataflow, whether you’re using the enhanced compute engine. You also need to consider whether your query uses on-premises data.

In case you just want a query that returns the current date and time in a time zone of your choice, independent of an on-premises data gateway, you can create a query that takes into account daylight savings (DST). Here’s an example for the Sydney time zone, where DST ends on the first Sunday of April and starts on the first Sunday of October:

let
    DateTimeZoneUtcNow = DateTimeZone.UtcNow(),
    DateTimeZone10Now = DateTimeZone.SwitchZone(DateTimeZoneUtcNow, 10),
    DateTime10Now = DateTimeZone.RemoveZone(DateTimeZone10Now),
    AprilFools3am = #datetime(Date.Year(DateTime10Now), 4, 1, 3, 0, 0),
    DayOfWeek = Date.DayOfWeek(AprilFools3am, Day.Sunday),
    AddDays = if DayOfWeek = 0 then 0 else 7 - DayOfWeek,
    DaylightSavingEnd = Date.AddDays(AprilFools3am, AddDays),
    DaylightSavingStart = Date.AddDays(DaylightSavingEnd, 182),
    TimezoneAdd = if (DateTime10Now < DaylightSavingEnd or DateTime10Now >= DaylightSavingStart) then 1 else 0,
    CurrentSydDateTime = DateTime10Now + #duration(0, TimezoneAdd, 0, 0)
in
    CurrentSydDateTime

You can adjust the query to suit your time zone.

Brief explanation: many daylight saving dates are based on “first Sunday in April” sort of rule, and because they’re always after February, the number of days in between is always constant. If a date falls between start/end times, you add an hour.

If for whatever reason, you need to know the current date and time in some time zone in DAX, the M query above can be adapted into a DAX formula, like Sandeep Pawar did in a blog post.

Happy querying!