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. The sample was downloaded 3,976 times so far (11/22/2016).

Download my sample PowerShell scripts here to learn how you can copy and paste queries from one workbook the another.

If you are interested in Power Pivot automation, there are many new related interfaces in Excel 2016 that can be found here (anything that starts with Model.*).

Are you familiar with this functionality? Share the way you used it in the comments below.

 

 

6 comments

  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