cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kleetus51 Member
Member

Adjust blank query based on environment

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
edhans Super Contributor
Super Contributor

Re: Adjust blank query based on environment

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/201711/5/2017
3/11/201811/4/2018
3/10/201911/3/2019
3/8/202011/1/2020
3/14/202111/7/2021
3/13/202211/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.

View solution in original post

2 REPLIES 2
edhans Super Contributor
Super Contributor

Re: Adjust blank query based on environment

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/201711/5/2017
3/11/201811/4/2018
3/10/201911/3/2019
3/8/202011/1/2020
3/14/202111/7/2021
3/13/202211/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.

View solution in original post

kleetus51 Member
Member

Re: Adjust blank query based on environment

Worked perfectly, thanks!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)