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

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/

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

    amitchandak 

    That solution only works when you refresh the report manually. I'm looking to grab the scheduled refresh from the dataset in the service and bring it into the desktop report.

    • mariussve1's avatar
      mariussve1
      Icon for Solution Sage rankSolution Sage
      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

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

        I did have to make one change:

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