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 Kiara Grouwstra who shared with me some of the interesting work they have done using VBA and Power Query in the Excel 2016 Preview, and who has very kindly allowed me to blog about it here. Their work is much more representative of how I think most people will want to use this feature.
Kiara 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”.
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 Kiara and others which is also available in the following GitHub repository: https://github.com/KiaraGrouwstra/pquery
Thanks for posting; subscribing to comments in case of questions / bug reports. 🙂
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
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).
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. 🙂
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 …
I think you’re getting confused here between Excel’s formula language and M, the language used by Power Query. VLookUp is an Excel function and you can’t use it in an M expression. Ken Puls has a good post on how to do the equivalent of a VLookUp in Power Query here: http://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/, but depending on what you need to do the Merge button (which allows you to join two tables) might be all you need.
This is the function vlookup.m (https://github.com/tycho01/pquery) that I was speeking about … not the vlookup worksheet function 😉 … but it was to slow … ( I must re-organize and make calculations on between 100.000 and 500.000 rows x 100 columns to create graphs and indicators).
So I used Power Pivot to create a link between the 2 tables …
but I must investigate more deeply to find a solution that is fast (not the more important), with not the problem of memory limit like in Excel …
I was thinking that Power Query was an interesting solution because I can create it in Excel and if there is a problem of memory, I was able to transpose it in Power Bi (I don’t know the limit of PowerBI).
Your Website will help me !
Ahh, sorry, yes I understand now. One thing to try is to see whether Table.Buffer() or List.Buffer() can help here, as with https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/
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?
Thank you, Tycho and Chris! Searched all day for code to delete power query, now found it with bonus!
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.
I don’t know, I’m sorry – I have no experience with XLLs.
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
No, there is no way of doing this (at least not in a reliable, officially supported way).
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 !
Hmm, I don’t know I’m afraid. Sorry!
Maybe it’s a typo, but shouldn’t your first ActiveWorkbook.Protect be a .Unprotect?
Both work. By setting both arguments to false, I protect nothing and enable editing. But I guess Application.Unprotect would be clearer.
Hm… I haven’t used this much since, but I guess I sorta get it. So Power Query calculations are async (separate thread from VBA), so VBA doesn’t really get any kind of notification about when it might finish. I suppose that definitely makes it harder to automate re-locking things like this…
Hello,
To fix this problem:
1/ First, all your queriy must have the option backgroundRefresh = false.
2/ Then add in your VBA the following, after the refresh of the PQ requests:
Application.OnTime TimeValue(Now + TimeSerial(0, 0, 5)), “ProtectThisWorkbook”
where ProtectThisWorkbook is a custom VBA procedure where you call the Protect method.
For me it works like a charm. Indeed during the 5 seconds, the PQ have the time to finish its own stuff, before executing the protect method 🙂
Hi Chris, thank you for this article.
I am currently building a library of functions to work with VBA and PowerQuery.
One of the main functions is to change the load type through VBA.
In a ‘Load To Data Model’ to ‘Connection Only’ scenario, I do the following:
– delete the OLEDB connection linked to the WorkbookQuery (As the InModel is read-only)
– Add the OLEDB connection with commandtype 2
Strangely enough, when I look at the query in the query-pane, it still says ‘x rows loaded’ and the Load status is ‘Loaded To Data Model’.
When I choose the UI-option ‘Load To..’ it correctly shows ‘Only create connection’. When I click ‘OK’ without any other change, the UI seems to refresh it, and then correctly shows it as ‘Connection only’
The macro-recorder records nothing for that last bit…
I tried refreshing the workbookconnection, but that does not change it.
Any ideas on
a) why the query pane is not synchronised until I go through the UI and press OK
b) what happens in the background when I click on OK?
Thanks,
Wouter
Hi, I cannot get the delete queries code to run. it gets stuck on tryMsgBox and i get the following error “Compile error: Sub or Function not defined”
When I try to download the .xlsm file from the link provided, I am getting below error. Can you please help with the file?
“{“error”:{“code”:”generalException”,”message”:”General Exception While Processing”}}”
my apologies, could you try the following link?
https://1drv.ms/x/s!AjFffgoO_-9rgSSi7X6s7pOUhVb1?e=Iqjv6x