Use VBA to Automate Power Query scenarios in Excel 2016

In case you missed this important functionality in Excel 2016, today’s short blog post is written to raise some attention to a wonderful feature. You can write VBA code or PowerShell scripts to automate the creation of queries, change their M expression and copy queries from one workbook to another.

Here are few common scenarios you can achieve with automation:

  1. Manage queries, copy, delete or search for specific query name, description or M formula.
  2. Toggle queries’ data sources from staging to production environments.
  3. Automatically manipulate the M expression on massive number of queries.

Download my sample workbook here to learn how to manage queries, load them to the worksheet or Data Model.

In the screenshot above, I set Load to Data Model to TRUE and clicked Create. The result is a new query that loads the M code in the Query Formula into the data model.

You can explore other VBA use-case in my sample PowerShell scripts here and learn how you can copy and paste queries from one workbook the another.

The Powershell scripts demonstrate how to automate Power Query and distribute Power Query queries from one workbook to another.

The attached ZIP file contains:

  1. Source workbook – src.xlsx
  2. Destination workbook – dst.xlsx
  3. Powershell script that copies all Power Query queries from workbook src.xlsx to dst.xlsx
  4. Powershell script that deletes all Power Query queries on workbook dst.xlsx


  1. nickburns42 Reply

    I’ve been using this code for a while now, it’s been really great!
    Is there anyway to get the status of the query to give a visual representation of the query refresh using the QueryTable.Refreshing method?

  2. HungryForKnowledge Reply

    Sadly the links seem no longer to be active. Is there any chance this could get reposted/updated?

Leave a Reply