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

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.