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.
