Forum Discussion
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?
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.
dtDSTStartdtDSTEnd3/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.
2 Replies
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.
dtDSTStartdtDSTEnd3/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.