How to Determine Last Refresh of Power Query in Source Workbook

Question

Context:

I have a complex network of Excel workbooks. The main Excel workbook links to several source workbooks with Excel's Power Query (the query links to a folder where the source files are stored). Each source workbook has their own Data Model and each table in each source workbook is appended to form the Main Data Table. It is the Main Data Table that is connected to in the Main workbook. A small schematic to help visualise the structure:

Main Workbook -> Source Folder -> Source Files

Now, in order for the Main Workbook to retrieve the latest data from the source files, the source files Data Model needs to be refreshed - so the latest data can be output to the Main Data Table to feed into the Main Workbook.

Question:

Is there any way to pull through the last refresh date of each source file into the Main Workbook? I am aware there is away to pull through Last Date Modified which is just the last date the workbook was saved. But is there a way to pull through the last date the Data Model was refreshed in each source file?

Answer 1

You can include a timestamp as part of a Data Model using an M code query with

DateTime.LocalNow()

This gets updated exactly when the model is refreshed.

You can use this in an independent table or as a custom column on existing queries.