CODEWITHSUNDEEP
powerquery
Thirupathi Rao G
Thirupathi Rao G

Reputation: 11

How to extract Queries metadata from Power Query Editor

I am looking for a way to extract power queries metadata from power query editor to spreadsheet or word for documentation purposes to understand the transformations or formulas applied in each query present in power query editor.

I have read different comments in other sites including renaming .XLSX to .ZIP and inside xl\connections.xml there's a Microsoft.Mashup.OleDb.1 data connection with some metadata but I am not successful in extracting the queries metadata. I am looking for any automated process to extract power queries transformation data into spreadsheet outside of power query. Any suggestions or ideas will be great help for me.

Upvotes: 1

Views: 824

Answers (2)

lumiweb
lumiweb

Reputation: 25

Note that a quick way to get all queries is to go in the power Query Editor, select all queries you want in the left panel > right click > copy. Than you can paste it easily in a text editor (and on netepadd++ there is even way to add manually the formatting )

Upvotes: 0

Wedge
Wedge

Reputation: 1826

You can access the code underlying any Power Query in Excel through the Queries object that is part of the workbook. It's in "Formula" property of the Query object. You can also get the name of the Query with the "Name" property. It just gives you the code as plain text, so it would be up to you to apply any context to that.

for i = 1 to ThisWorkbook.Queries.Count
 ThisWorkbook.Queries(i).Name
 ThisWorkbook.Queries(i).Formula
next

Note this only works in Excel 2016 or later. Older versions of Excel where PQ is installed as an add-in can't access PQ through VBA. I'm also unaware of any method to extract information on the dependencies between Queries within a workbook (though with consistent naming conventions you could pretty easily build this yourself I figure).

Upvotes: 1

Related Questions