Forum Discussion

nleuck_101's avatar
nleuck_101
Icon for Continued Contributor rankContinued Contributor
4 years ago
Solved

Last Refresh date/time from Power Service in Power BI Desktop report

Hello All,

 

Is it possible to get the last refresh date/time on the dataset in the service added to a Power BI desktop report? I was trying to use this article https://excelguru.ca/display-last-refreshed-date-in-power-bi/ but the website for the time api is no longer available.

 

Any help would be greatly appreicated!

 

Thanks,

Need Help
  • mariussve1's avatar
    mariussve1
    4 years ago

    Could you just create a custom column in Power Query and use:
    DateTime.LocalNow()

    #"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow())

     

    Then format it as DateTime

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})

     

    This should then show wich time the table was refreshed as it will update the column with current date and time.


    M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSlaK1YlWMkJiGyOxTWDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dummy = _t, Abc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dummy", Int64.Type}, {"Abc", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
    in
    #"Changed Type1"

     

    Marius

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    First add a blank query in the Power Query Editor

    let
    Source = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())
in
    Source

    Then add this measure to your model and to visualize it put the measure in a card visual:

    Last Refresh =
VAR GetLastRefresh =
    FIRSTDATE ( 'LastRefresh'[LastRefresh] )
RETURN
    "Last refreshed on: " & FORMAT ( GetLastRefresh, "dd mmmm yyyy" ) & " "
        & REPLACE (
            FORMAT ( GetLastRefresh, "HH:mm:ss AMPM" ),
            1,
            2,
            HOUR ( GetLastRefresh ) + 2
        )

    Please note time is set to UTC+2 (Daylight Savings Time) or UTC+1 - this will display incorrect in Power BI Desktop but correct in Power BI Service (after refresh in Power BI Service). The last parameter in this Measure (+2) needs to be updated when change to/from Daylight Savings Time.

    • nleuck_101's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      4 years ago

      Anonymous 

      This did not work. I try refreshing the dataset in Power BI Service and the time doesn't change.

      • mariussve1's avatar
        mariussve1
        Icon for Solution Sage rankSolution Sage
        4 years ago

        In the top right corner in service, when you are in the report, you should se this:

         

        Try push the refrehs button I have ringed out in red. This should work, but maybe some cache issues?

        Marius

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    nleuck_101 , The power query code should work. Which time api is no longer available ?

     

    Similar solution -https://askgarth.com/blog/add-the-last-refreshed-date-and-time/

  • pthapa's avatar
    pthapa
    Icon for Post Patron rankPost Patron
    2 years ago

    Hello all,

    I went through most of the advices but I still not sure that the outcome is correct.

    No matter either we create a dax (Last Refresh = NOW()) or new query (=DateTime.LocalNow()), both will give same results; however they only reflect the local time.

    Our goal is to let the users know when the last dataset was refreshed, not when the visual was last refreshed.

    For insatance my dataset refreshes at 6:00am, 12:00pm and 4:00pm. The users will not exactly know that what time the dataset was refreshed. I will tell you the reason because the logic we are using to add the card visual to show the last refresh time will always show the local date/time whenever the user opens the report. So, this date/time is not the same as when the sementic model was refreshed. Does that make sense?

    I don't belive that PBI has such feature available to show the actual time of dataset refresh as of now.

    Hope PBI team will bring this feature in future updates.

    Expert, please add your comments on this, I might be wrong.

    Thanks,

    pthapa

  • raishubham955's avatar
    raishubham955
    New Member
    1 year ago

    To show last refresh date/time you can use Power Query 

     

    • Open Power Query Editor (Transform Data).
    • Click New Source → Blank Query.
    • Open Advanced Editor (View → Advanced Editor).
    • Paste the following M code:
    • let
      Source = DateTime.LocalNow()
      in
      Source

    • Click Close & Apply.
    • Rename this table to "Last Refresh".
    • Use it in a card visual.

     

    • MarkPalmberg's avatar
      MarkPalmberg
      Icon for Kudo Commander rankKudo Commander
      1 year ago

      Yes, those are the Google search results. But DateTime.LocalNow() still returns UTC datetime when published to the service, for whatever reason. Maybe it's a tenant setting we're missing, or something.