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.
business intelligist 
Hi there – I’m one of the Program Managers on the Power Query team.
Thank you for a fantastic post. The reason why you don’t see a “Load to Data Model” capability in 2010 (even though we would’ve loved to support it) is because of the fact that the Data Model/xVelocity engine is not native to Excel 2010. 2013 has the advantage that the data model is natively integrated. The “connection-only” query that you ended up using was explicitly meant to allow you to pull on a Power Query source direction from Power Pivot – so we are glad that this worked for you.
The intention behind Power Query is also for it to be a much more widely applicable data discovery/import/mashup tool – so we continue to work on simplifying the experience so that we can one day reach the masses. That of course is a journey – but one we are very serious about.
Hope you find this info useful – please don’t hesitate to reach back to us.
Faisal Mohamood | Program Manager | Data Platform Group – Microsoft
Faisal,
I find it very useful :), thank you!
I do a lot of POCs and I started using the tool primarily to massage formatted reports (Income Statements, etc.) into an un-pivoted easily consumed data sets. Now, it seems, I find more and more uses for it.
I have a feeling, from now on, I will start using Power Query exclusively as my data connections for Power Pivot as it will give me greater flexibility in case I need to switch out my raw data sources. For example if my initial load of data comes from a web page but then I decide to load the data (in the same format) from a CSV file, I have to essentially drop the power pivot data tab and reload it from a new Power Pivot connection.
It could be a pain in the neck because dropping a tab in Power Pivot drops all of the logic (calculations, relationships, etc.) with it. I just tried to create a Power Query against a web page, used it for Power Pivot, then renamed the connection, created a new connection with the original connection’s name but now against a CSV file and with some hacking around got it to source the same tab in Power Pivot but now effectively pulling data from CSV and not from Web.
I know it’s kind of a confusing explanation and I should probably do a new post with screenshots, but the bottom line is that now there is a way to change the type of data that is used for Power Pivot and it will definitely come in handy for me.
In any case, I think we are only scratching the surface with respect to how much our lives will be affected by this tool and I am excited to see it mature and get widely adopted.
Hat dies auf MS Excel | Power Pivot | DAX rebloggt und kommentierte:
Super Tipp, wie man Power Query connections in Power Pivot nutzen kann.
Great Post! This is exactly what I was looking for.. Thanks!
Can I load PowerQuery data into a PowerPivot model and then set up a PowerPivot scheduled refresh through SharePoint 2013 and have all the data in my model refresh, including the PowerQuery data ?
Thanks for any advice you may offer…
Sincerely,
craig
No on premise SharePoint doesn’t support refresh from Power Query
I recently read about and am now testing a solution to allow refresh for on-premises solution. It’s called Power Update and appears to piggy back on the windows task scheduler to perform updates of Power BI data models for a variety of Microsoft stack BI implementations…
http://www.power-planner.com/Products/ProdID/10/Power_Update
Thanks for the post, I’ve also been importing data into Power Pivot from Power Query(s). However the interface appears to limit us to one Data connection at a time. Is there an option to import all Power Query connections within the workbook at once?
Thank you for your post, I have a query and using the final outcome as base, I use reference function to create another 4 queries, bit each of that queries read the information directly from the source and not from the base query, so every time I read a lot of information instead of only 100 record that I have in my base query.
Is there any way to do that?
Regards
There is one issue I’ve run into regarding this suggestion. If you create any custom columns in your query within Power Query these custom columns do NOT get imported into Power Pivot when you use the saved Power Query connection. I’m not sure why.
Hi admin, i must say you have very interesting articles here.
Your blog should go viral. You need initial traffic
boost only. How to get it? Search for; Mertiso’s tips go
viral
Thank you very much!
I followed the steps and now I can place millions of rows in powerPivot thru power query.
Say I have a new month set of data (same order of columns) and want to add to the database that was already created in powerpivot. How do I add to the existing data source?
Your kind help is truly appreciated.