See a workbook's Power Query queries
Power Query fetches data from elsewhere and brings it into the workbook on every refresh. An ordinary-looking file can connect to a network folder, a website or a database, with nothing shown in the sheets.
In Excel, step by step
The list of queries
Data, Queries & Connections: the pane on the right lists each query and says when it was last refreshed.
What a query does
A double-click opens the Power Query editor; the Applied Steps pane, on the right, lays out each transformation in order.
The source, in the code
Home, Advanced Editor: the first line names where the data comes from by its function, Web.Contents for a website, File.Contents for a file, Sql.Database for a database.
Pitfalls
- A query reads a file or a website on every refresh: if the file is moved or the network is down, the refresh fails.
- Credentials for a database or SharePoint are kept per machine, not in the workbook: it opens for its author, not necessarily for a colleague.
- A query loaded as a table keeps a copy of the data in the workbook, which makes it heavier and may be as old as the last refresh.
- Nothing in the sheet flags that a column comes from a query: a value fixed by hand is overwritten on the next refresh.
Faster, on the whole file
Excel file health check lists a workbook's Power Query queries and their sources, a file, a folder, a website or a database, without opening Excel or refreshing anything. A query that reaches for data off the machine is flagged separately. Inspect the workbook gives the count in the Technical block.
Frequently asked questions
- Can I see the queries without opening the file in Excel?
- Yes: the health check reads them from the file, with each query's name and its source.
- Where is Power Query stored in the file?
- In a hidden part of the workbook, a stream named DataMashup, which holds the M code of every query. It is not in the sheets.