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-re...
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      mariussve1  is right, you may need to refresh the report in Power BI service as well after refreshing the dataset.