Adjust blank query based on environment

[Anonymous]

Hi,

I've added a blank query to my report to return a table of last refresh date/time. It appears that this query uses my local timezone (Central) to calculate this when running in PBI desktop, but when I publish to the service it is 5 hours ahead (I assume GMT). Since I want my reports in the service to display in Central time, how do I run this query in the desktop:

let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source

and this in the service:

let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()+#duration(0,-5,0,0)}})
in
Source

or is there a better way to accomplish the same thing?

[edhans · Accepted solution]

Use the following code in a blank query to adjust to your local time zone:

let
  Source = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5 + varDST,0),
  #"Converted to Table" = #table(1, {{Source}}),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "RefreshDate"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"RefreshDate", type datetimezone}})
in
  #"Changed Type"

The key is using the datetimezone feature.

Now it can still be off for daylight savings. If you don't care, remove the +varDST in the source line.

if you do care about DST, then you need to do 2 more things:

1) create a new table that has your DST stettings. I have this table in our SQL server.

dtDSTStartdtDSTEnd

3/12/2017	11/5/2017
3/11/2018	11/4/2018
3/10/2019	11/3/2019
3/8/2020	11/1/2020
3/14/2021	11/7/2021
3/13/2022	11/6/2022

This is the start/end date for DST in the US right now.

2) Then create another blank query and call it "varDST" with the following code:

let
    Source = tblDaylightSavings,
    #"Filtered Rows" = Table.SelectRows(Source, each [dtDSTStart] <= DateTime.Date(DateTime.LocalNow()) and [dtDSTEnd] > DateTime.Date(DateTime.LocalNow())),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

You can put this table anywhere. Sharepoint List, SQL server, Excel, wherever. It just counts the rows that have dates between the start/end date. It is either 1 (DST in effect) or 0, (not in DST) so it adds 1 or 0 hrs to your refresh timestamp above.

[Anonymous]

Worked perfectly, thanks!