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
Highlighted
edhans New Contributor
New 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
Highlighted
edhans New Contributor
New 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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 478 members 3,484 guests
Please welcome our newest community members: