Forum Discussion

Virtual_Ames's avatar
Virtual_Ames
Microsoft Employee
10 years ago
Solved

Easy consistent Last Refresh Time (your local time zone) solution - no matter how refreshed

If you want to make sure your viewers know how fresh the data in a report is - you may want to have a "Last Refreshed" tile showing.  The challenge is - if you publish from your desktop, you get last...
Show and Tell
Tips and Tricks
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Virtual_Ames

     

    Thanks for your sharing. That's a awesome idea to indicate a precise refresh time. :)

     

    Just some tips, no need to import the web as a source, the Power Query,also known as "M", has a "DateTimeZone.UtcNow()", you can get UTC time with that function and tweak different timezones based on UTC.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

Virtual_Ames

 

Thanks for your sharing. That's a awesome idea to indicate a precise refresh time. :)

 

Just some tips, no need to import the web as a source, the Power Query,also known as "M", has a "DateTimeZone.UtcNow()", you can get UTC time with that function and tweak different timezones based on UTC.

  • Virtual_Ames's avatar
    Virtual_Ames
    Microsoft Employee
    10 years ago

    Excellent.  Thanks.

    Question though - let's say I want the timestamp value to always reflect the timezone of the user who is generating the report. Is there a way to set that up using M? That would go one step beyond the current solution we have on the table.

     

    Brian

     

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      10 years ago

      Virtual_Ames

       

      Add a custom column in Query Editor. The 8 refers to timezone offset, it always shows the local time of +08:00 timezone.

      DateTimeZone.UtcNow()+#duration(0, 8, 0, 0)


      If it can achieve your goal, please accept it as solution.:) For any question, feel free to let me now.

  • pkoetzing's avatar
    pkoetzing
    Advocate III
    6 years ago

    Switching timezones in M-language has a very limited functionallity, as you can switch by a given number of hours only. But this doesn't help when you're located in a daylight savings regime. Pulling the proper timestamp from an external API is unfortunately still the only working solution. I'd recommend using worldtimeapi.org and e.g. the following script:

    let
    Source = Json.Document(
        Web.Contents("http://worldtimeapi.org/api/timezone/Europe/Berlin")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(
        #"Converted to Table", each ([Name] = "datetime")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Columns",{{"Value", type datetimezone}}),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed Type",{{"Value", "Europe/Berlin"}})
in
    #"Renamed Columns"
  • Virtual_Ames's avatar
    Virtual_Ames
    Microsoft Employee
    10 years ago

    Thanks - this is very helpful.

     

    Appreciated.

     

    Brian

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    What should we do with the daylight saving time (DST)?

    For example the difference between Melbourne time can be +10, but the other six month it is +11.

     

    Any automatic solution for this?

     

    Thanks