How to get the last date and time when Pivot Table was refreshed?

Question

I have some tables in my excel workbook that uses external sources for stock prices. I have been trying to use =TODAY() in a measure and display it in a pivot table.

This solution is inaccurate as it shows the date and time when the refresh finished rather when the refresh was initiated/started. Sometimes the refresh can be quite long, so I feel this is not accurate enough :)

I would like to avoid VBA.

Any DAX/Power query ideas or solution that shows the last refreshed more accurately?

Answer 1

I'm very open for other solutions :).

The solution I use to get the initiated time the last refreshed was executed, is a small query which also acts as an external table:

1. I start with creating a new query

2. Add the first part to get the local date and time by pasting the following code in the function bar and press enter

   = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())

3. Then convert query to a table (in the transform tab)

4. Then you can rename the column.

---

You could also just create a "Blank Query" and then go to "View" -> "Advanced Editor" and paste the following code:

let
    Source = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow()),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refreshed Date And Time"}})
in
    #"Renamed Columns"

---

End Result:

Where the first one is a the query above and the below is a measurement mentioned in the question.