Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWorked perfectly, thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.