I have to admit, I like Power Query more and more every day. Even though on one hand sometimes I feel that the tool will only appeal to a very specific type of power users, on the other hand, I can’t help but feel that the tool is so good that I wonder if it has a potential to dramatically redefine where we typically think power users world ends and the world of IT begins.
There was, however, one little thing that left some bad taste in my mouth when I tried to apply the tool in a real life enterprise scenario. Power Query and Power Pivot are the only two Power BI features that are actually made available in Excel 2010 (i.e., they don’t require Excel 2013). I have been using them in Excel 2013 for a while and one of my favorite features was Power Query’s ability to source files out of a folder, concatenate them together and then load them into a Power Pivot bypassing 1M row limit in Excel.
The unfortunate thing when I started going through the same exercise with a customer was that the client only had a 32-bit version of Excel 2010 (and frankly, I have to say that probably 90% of enterprise customers still fall under this category). To my disappointment the Load to Data Model option that I was accustomed to in Excel 2013 was not available in Excel 2010.
The only work around that I found was to limit the data set to about half of the files, load data to worksheet and then use Linked Table feature of Power Pivot to add it to my model. Obviously less than elegant solution.
Yesterday, I was shown a much better way.
In one of my previous posts I demonstrated how to use a web page as a Power Query data source.
As I went through my Power Query design, I would always either have Load to worksheet or Load to Data Model checked. However, apparently, it is totally fine to have both of them unchecked, in which case (well, I guess rather in any case), the Power Query is saved as a Workbook connection and can be accessed by clicking on Data->Connections
Now, we can either use it to create a regular pivot table by clicking on Existing Connections in the Data section of the Ribbon and then picking the right connection from the list
And then selecting Pivot Table or Pivot Chart
Or, one can go to Power Pivot, use Existing Connections option as well and then use that Power Query connection as source.
This is a much more elegant solution as it does not require the intermediate load of all the data into an Excel table. Had I known that trick before, I would not have to delete half the files from the folder and my guess is that the load time would have been cut in half as well.
Another interesting observation, if this Power Query connection is used for a Pivot table, then right clicking on the pivot table and selecting Refresh, executes the Power Query on the data source and refreshes the data in the table.