2

How can I access the metadata associated with a PowerQuery using a PowerQuery? This data appears when one hovers over a query in the right side "Workbook Queries" list, showing fields such as "Last Refreshed", etc.

Application: I have an Excel workbook that brings together several dozen input files from various sources, which are then used by other downstream workbooks. What I would like to create is a table within this input workbook that displays the metadata for each input table's Power Query within the workbook, such as when the table was last refreshed, how many records it has, etc., for validation purposes. For example, if I know that a certain file is expected to have exactly 64 records, I could flag whenever it comes across with greater or fewer records.

I have searched extensively, including numerous blogs, tech boards, amazon.com and the following:

Introduction to Power Query (informally known as “M”) Formula Language: https://msdn.microsoft.com/en-us/library/mt270235.aspx

Power Query (Informally known as “M”) Formula Reference: https://msdn.microsoft.com/en-us/library/mt211003.aspx

8/31/2016 Update: It appears that the functionality to programmatically access Power Query metadata does not yet exist. What I would hope eventually comes down the pike for Power Query tables is something akin to the PivotTable.RefreshDate property in Excel. (It would also be nice to have that property for linked tables.) The work-around upon which I have settled is along the lines of adding a column to each Power Query as follows:

#"Added Refresh Date" = Table.AddColumn(Source, "RefreshDate", each DateTime.LocalNow())

I can then retrieve the refresh date of the query table from the field RefreshDate from any record within the table. The difficulty that I have with this method is that it increases both computation time and file size.

Improve this question

1 Answer 1

Reset to default
1

To find how many record are in the input table, have you tried importing the excel file and looking at the rowcount of the tables?

Data > Get & Transform > New Query > From File > From Workbook will give you an example of how to use the Excel.Workbook library function.

On the other hand, I don't know of any support for last refresh details in excel workbooks.

I renamed a .XLSX to .ZIP and inside xl\connections.xml there's a Microsoft.Mashup.OleDb.1 data connection with some metadata, but nothing seems to be a "last refreshed" time.

You could use Folder.Contents on the folder containing the workbook file and see the Data Modified column of the whole file, if that's enough?

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, @Carl. Some good suggestions. What I am trying to do, however, is to set up some validation stuffs specifically around the Power Query queries themselves, i.e. when the query was last refreshed, how many columns a query retrieved, how many records were returned, etc., which I may then compare with expected values for these items, e.g. set a warning flag when the last refreshed date is greater than six months ago, etc. Since Last Refreshed appears on the flyout when one hovers over the query name in right side Workbook Queries tab, I figured there must be a way to access that data.
@XLT Agreed, the data must be in the workbook or the cache folder then. On the other hand this likely isn't documented or supported metadata. Even if you can find some way to extract Last Refreshed that might break in the future...
Perhaps I should step away from the bleeding edge. Among other things, what I am seek is an M version of the PivotTable.RefreshDate property, but your point is well taken.
Its 2021, and came here to looking for an answer. Seems like MSFT has not added anything to help this.
@Sharif I'm not sure if any MSFT employees are looking at this. You could try asking on your favorite Excel forum; let us know if you find something!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.