Beyond Excel's (BXL's) Add-in:

PQ Helper 2.0

How do I save and reuse Power Query queries and functions?

I found this question back in 2015 on Microsoft's TechNet forum. The answer really didn't address reuse. I had the same need, so I created my own tools.

Then in Dec. of 2021, a forum poster asked how to, with VBA, import a query, run it, then delete it so the workbook wouldn't open with the dreaded SECURITY ALERT! The tools I created back in 2015 addressed his needs.

So I decided to update my tools and give them to the forum poster - and while I was at it, give them to all my Excel friends as a Christmas gift.

While this was designed initially for my own use when I play the role of VBA developer, it is also useful for non-VBA users as it does things more efficiently than what Microsoft offers natively in Excel.

So what exactly does it do?

Power Query functions are spread across two of Excel’s ribbon tabs: Data and Queries. This tool places them in one place along with BXL enhancements.

It adds enhanced importing of queries and functions. This tool loads as many as we can select – all at once. Excel’s Queries & Connections icon (on Data tab) only loads one at a time.

It adds enhanced connection management by revealing all connections Excel doesn't and giving us the option to delete some or all of them and avoid the dreaded SECURITY ALERT!

NEW in 2.0

Add Query option

New icon has a menu to facilitate creating queries over workbook tables and named ranges

• Load some or all Excel tables into Power Query (PQ) without opening PQ.

• Load named ranges into Power Query. Designed to facilitate importing Dynamic Array models into Power BI.

PQ Assemblies option

New icon has menu to create frequently needed functions in PQ

• Add a properties table and PQ function to facilitate parameter passing between Excel and PQ

• Add a function to generate dynamic tables in PQ, link the table to a parameter in a properties table, then add the dynamic table to the workbook

Why does anyone need this?

This add-in is ideal if you:

• Work for a multinational corporation and frequently need currency exchange rates which are updated continuously on the web. Create the query once, export it, then import it as many times as needed into other workbooks.

• Use functions in Power Query, you can save your function to a single location, and when you, or anyone on your team need the most current version of it, import it to your workbook.

• Need to document you queries, you can export them and import the text file that holds your query into MS Word.

• Write M code, you may prefer writing code a different editor with features like version control, rather than Power Query's Advanced Editor.

• Want to modify queries with VBA, you can use my Power Query library (modPQ) to import M code, modify it, and create connections, query tables, or add queries to the data model.

How do I get this?

It's available free from Eloquens.com.