Forum Discussion

Kelz's avatar
Kelz
Advocate I
4 years ago

Changing Last Refresh date to local Time

Hi Everyone, 

 

I am aware this question gets asked alot and I have tried all the solutions i could find on google and it didnt seem to work as I kept getting errors and/or Im missing a step somewhere.

 

I have the last refresh date on my report, on my desktop dashboard it shows as my local time, but on the report in power bi web, it shows 13 hours behind (UTC time?)

 

I need to show my refresh date/time in NZDT, can somebody please walk/talk me through what I need to do to change my date to show NZ time? 

 

thanks

 

Kelly

Need Help

8 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    4 years ago

    Kelz 

    This is how I convert to Eastern Time:

     

    LastRefreshET = FORMAT(LastRefresh[LastRefreshUTC]-(5/24),"m/dd/yyyy hh:nn")&" ET"

     

     

    For NZ, I believe you would need to + x/24.  I believe x = 13 as you stated above.

    Regards,

    • KNP's avatar
      KNP
      Super User
      4 years ago

      Unfortunately, this doesn't deal with the daylight savings issue.

       

    • Kelz's avatar
      Kelz
      Advocate I
      4 years ago

      Hi, 

      thanks for your quick reply, my "last refresh" table and column did not show up in the formula bar - is there another area where I can input this?

       

      the source is:

      = #table({"Last refresh"}, {{DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())}})

       

      and only shows 

       

       

      These are the refresh fields I have 

       

      • rsbin's avatar
        rsbin
        Community Champion
        4 years ago

        Kelz,

        I am using a Measure so not sure why YourTable[YourColumn] is not showing up.

        Make sure you are typing in your references correctly.

        But, I do recommend going with KNP's solution as it automatically corrects for Daylight and Standard Time.  I use the following Power Query script as well.  This one was a little wonky at first, but haven't had any issues in the last few months.  Replace "America / Chicago" with a more suitable location.

         

        let
    Source = Json.Document(
        Web.Contents("http://worldtimeapi.org/api/timezone/America/Chicago")),
    #"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", "Central Time"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns", {{"Central Time", type text}}, "en-US"), "Central Time", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Central Time.1", "Central Time.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Central Time.1", type datetime}, {"Central Time.2", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Central Time.1", "Central Time"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Central Time.2"})
in
    #"Removed Columns1"

         

        KNP's suggested site looks like it may be more stable.

        Regards,

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi Kelly,

     

    The best way I've found so far is to ensure you're getting your date/time from a web call.

    This ensures it displays correctly in desktop and service.

     

    Head over to here (https://timezonedb.com/register) and register for a free account. 

    Once registered, you'll have an API key on this page. (https://timezonedb.com/account)

     

    Use the below code (paste into the advanced editor of a blank query and replace MYKEY in the Source step with your API key)

    let
  Source = Xml.Tables(
    Web.Contents(
      "http://api.timezonedb.com/v2.1/get-time-zone?key=MYKEY&format=xml&by=zone&zone=Pacific/Auckland"
    )
  ),
  #"Removed Other Columns" = Table.SelectColumns(Source, {"formatted"}),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Removed Other Columns",
    {{"formatted", type datetime}}
  ),
  #"Extracted Date" = Table.TransformColumns(
    #"Changed Type",
    {{"formatted", DateTime.Date, type date}}
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Extracted Date", {{"formatted", "today"}}),
  today = #"Renamed Columns"{0}[today]
in
  today

     Let me know if you have any questions.

     

    • Kelz's avatar
      Kelz
      Advocate I
      4 years ago

      Hi, 

       

      How do I connect that "today" field to convert my last refresh date into NZDT? does it automatically change something in the background? or do I need to do a measure?

       

      thanks 

       

      kelly

      • KNP's avatar
        KNP
        Super User
        4 years ago

        Kelz , 

         

        Not sure if we were posting questions/responses at the same time so not sure where you're at.

         

        Given your last refresh is just using a DateTime.LocalNow() I think all you're after is removing a few steps from my original code.

        (note I changed it slightly so you can paste your API key into the variable at the top)

        let
  MYKEY = "REPLACETHISWITHYOURAPIKEYKEEPTHEQUOTES",
  Source = Xml.Tables(
    Web.Contents(
      "http://api.timezonedb.com/v2.1/get-time-zone?key="&MYKEY&"&format=xml&by=zone&zone=Pacific/Auckland"
    )
  ),
  #"Removed Other Columns" = Table.SelectColumns(Source, {"formatted"}),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Removed Other Columns",
    {{"formatted", type datetime}}
  )
in
    #"Changed Type"

         

        If you're just after the date time at the time of refresh in NZDT then this should give you that.