Power Query/Excel 2016 VBA Examples

In Excel 2016, Power Query is no longer an Excel add-in but a native feature of Excel, and what’s more, you can now use VBA to create and manage Power Query queries.

I’ve found two sources of information about how to use VBA with Power Query in Excel 2016. First, there are some code samples on the Technet Gallery here:
https://gallery.technet.microsoft.com/VBA-to-automate-Power-956a52d1#content
…and Gil Raviv, a Program Manager at Microsoft, has also asked for feedback on this functionality on this thread:
https://social.technet.microsoft.com/Forums/en-US/1eac9c36-b6e4-48f0-a51a-fa92b24cf1d9/vba-and-power-query-in-excel-2016-preview-lets-get-started?forum=powerquery

Secondly, I was contacted recently by Tycho Grouwstra who shared with me some of the interesting work he has done using VBA and Power Query in the Excel 2016 Preview, and who has very kindly allowed me to blog about it here. His work is much more representative of how I think most people will want to use this feature.

Tycho sent me a .xlsm file containing all of the VBA code, which you can download here. Obviously the code only works in the Excel 2016 Preview, but you can still open the file and look at the code in Excel 2013. However if you’re worried about downloading a workbook with macros in, I extracted the code to a text document which you can see here. If you want to copy the code to use in your own workbook, you’ll need to go to the VBA Editor, select Tools/References and add a reference to “Microsoft ActiveX Data Objects 6.1 Library”.

image

The VBA code includes examples of how to:

  • Delete all the Power Query queries in a workbook
  • Export/import the M code for all queries to/from another Excel workbook
  • Export/import the M code for all queries to text files
  • Refresh all the Power Query queries in the workbook
  • Load a query to an Excel table

A few bugs/features in the Preview are also pointed out, namely:

  • Imported queries don’t always show up in the Workbook Queries pane; the workaround is to close and reopen the workbook
  • Functions aren’t recognised as functions (ie they don’t have the fx icon) until you open the Query Editor and the Close & Load
  • Query groups aren’t supported yet – which is a bit of an oversight, in my opinion, but the forums thread linked to above indicates it won’t be addressed before RTM unfortunately
  • Loading the output of a query into an Excel table using the code given here doesn’t seem to have the same result as loading a query to a table in the worksheet using the Power Query UI: it creates a blue, rather than green, table that doesn’t always retain row order.

I can imagine a lot of serious Power Query users will create workbooks containing a library of their most useful queries and functions, and use VBA code to copy these queries and functions into new workbooks as and when necessary. We’ll have to wait and see what Microsoft’s plans for sharing Power Query queries are, whether they’ll go beyond what’s already been seen in Office 365 Power BI, whether they will be part of a bigger bundle of services and what the cost will be.

Incidentally, the sample workbook contains a lot of interesting, generally useful Power Query queries and functions written by Tycho and others which is also available in the following GitHub repository: https://github.com/tycho01/pquery

26 thoughts on “Power Query/Excel 2016 VBA Examples

  1. Hi,
    Thanks for this sharing. I really needed it.
    But I have some little problems :
    – I have a error on each query created, but the data are loaded
    – With loading into worksheet only parameter, the first query is ok but after this one they appear like “connexion only”, whereas the refresh data works.
    Otherwise, I like this new powerful feature.

    Rémi Fortin

  2. Hi Remi,

    I’m now also seeing a “Unexpector Error — Value does not fall within the expected range” on each query. Is that what you got as well?

    I hadn’t really seen this before, and it seems irrespective of the query content… I’m thinking it may well be something about the current Power Query build. Not sure we can do much here, but I just tried the “Send Frown” to send Microsoft the bug report.

    I’m trying to reproduce your second (connection only) issue, but not having much luck there… To confirm, you’re using the ‘(Re)Load to Sheet’ button multiple times, but after the first they fail regardless of what queries you used, correct? For me repeated use still seems functional, using either different queries or the same one.

    I did notice that ‘(Re)Load to Sheet’ button doesn’t actually reload existing queries, but instead adds more copies to new sheets instead. Just fixed that at least, even though I haven’t been able to address the issues you found yet: current copy can be found here (https://onedrive.live.com/redir?resid=6befff0e0a7e5f31%21164).

  3. Thanks for response.
    The error message is exactly as you said, but it doesn’t prevent the workbook from creating queries.

    Forgive me about the second problem, I poorly explained it.
    I have my macro which add one Power Query function and 4 other requests which use the function and load into worksheet only. The first one loaded correctly, but the others appears like connection only request. So that’s just a visual problem because a refresh correctly refresh all of them.

    Rémi

    • Right, got it. I think that’s the known issue I mentioned in there, right?

      This: “Loading the output of a query into an Excel table using the code given here doesn’t seem to have the same result as loading a query to a table in the worksheet using the Power Query UI: it creates a blue, rather than green, table that doesn’t always retain row order.”

      I think that one might be an issue with the VBA options currently available — I originally used the macro recorder to see how to load a query from VBA (documentation wasn’t available yet), but running the recorded code never yielded the same result (always blue table instead of green). I’m hoping Microsoft will fix that, or someone else will correct me and figure out a better way…

      I uploaded a small update by the way (same link), adding export buttons for a single specified query, as well as adding a dropdown for the query selection (Excel data validation: list). Minor, but may well improve user-friendliness. 🙂

  4. Hi,
    Thank you for your work about Power Query !
    I’m trying to use the vlookup function :
    In the Power Query Editor, I have a Query named TabGTA, and I try vlookup with this example :
    = vlookup(1000, “TabGTA”, 2, false) … : but “TabGTA” is a wrong syntax (I’ve tried a lot)
    I think there is a solution (I’m an absolute beginner in M langage)
    Thank you for your help

    • I found so I reply to myself :

      In my Query TabGTA the first column was not typed :
      Source = Excel.CurrentWorkbook(){[Name=”TabGTA”]}[Content]
      I type It with :
      Source = Excel.CurrentWorkbook(){[Name=”TabGTA”]}[Content],
      #”Type modifié” = Table.TransformColumnTypes(Source,{{“Code GTA”, type text}})
      now in an other Query I can add a calculated column : with vlookup([Code GTA], TabGTA, 2, false) :
      = Table.AddColumn(#”Type modifié”, “GTA”, each vlookup([Code GTA], TabGTA, 2, false))

      But it’s very slow … I will try to join the table …

  5. Disclaimer, I haven’t really used this M-based vlookup function — I wanted to give it exposure / bundle the useful things I’d found/made in M. It may well be the native versions are faster.
    If that’s the case, is there a chance the native vlookup/index functions might work for your use-case?

  6. Thank you Chris, the VBA sample was very interesting but I have a question. Do you believe is possible to build an an XLL Excel add-in doing what you have done in VBA. I developed in visual studio a custom Excel add-in doing some stuffs but I’m not able to find how to add the correct reference to power query. I’m wondering if is possible or it can be called just from VBA and not in vb.net as all other Excel features.

  7. Hi Chris, interesting article! However, since the introduction of the Power BI service and the PBI Desktop, is there a way we can programmatically generate .pbix files instead of power query through Excel VBA?

    I’m working on some BI automation at the moment, and VBA combined with Power Query definitely makes my life easier, but now I would like to create something similar that outputs .pbix files with the corresponding M-queries.

    Thanks in advance,
    Bas

  8. Hi there !

    Very useful ideas. I’ve run into a little problem; I’m trying to refresh all queries and then relock the workbook with this code :

    Sub refresh_queries()

    ActiveWorkbook.Protect Password:=”password”, Structure:=False, Windows:=False ‘Unprotect
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Protect Password:=”password”, Structure:=True, Windows:=False ‘Protect

    End Sub

    Unfortunately, the workbook locks back before the refresh is over (or rather, i’m stuck with the message “Download did not complete.” on every query). I’ve tried adding stuff :
    – Application.Wait
    – Application.Calculate
    – looping through a fake calculation

    So far I haven’t found the solution. Any thoughts ?

    Thanks !

Leave a Reply to antonio Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s