Load Power Query directly to Power Pivot in Excel 2010

2015-04-29
Ken Puls
Categories: Excel, Power Pivot, Power Query

One of the cool features in Excel 2013’s Power Query is being able to load to the Data Model (PowerPivot) directly. But Excel 2010 doesn’t appear to have this feature. Interestingly, you can still load Power Query directly to Power Pivot in Excel 2010, it just takes a bit of a careful workaround.

Let’s look at the required steps

Step 1: Create Your Connection

First, I’m going to load in the content of a text file. So I:

Go to Power Query –> From File –> From Text
I browsed to the file I needed, and imported it into Power Query
I do whatever cleanup is needed and name the query Sales
Next, we go to the Home tab –> Close and Load –> Close and Load To…

And here’s the important part:

Choose “Only Create Connection” –> Load

And I’ve now got a basic connection to my sales table without landing it in a worksheet:

Step 2: Grab the Connection String

Now, here comes the secret. We need to get the connection string that Excel uses to connect to the Power Query. Here’s how:

Go to the Data tab –> Connections

In there, you’ll see the name of your new connection:

Select your Query and click Properties
Click the Definition tab

Now you’ll be looking at something like this:

Notice that this query is actually an OLE DB Query that is simply “SELECT * FROM [Sales]” That seems easy to work with. But the key for us is the connection string shown (#2 in the image above).

Select the ENTIRE connection string
Press CTRL + C to copy it
Click Cancel

Note: Make sure you start at “Provider=” and highlight all the way to the end. (It’s much longer than what you see in that little box.)

Load Power Query directly to Power Pivot

Finally, we’re going to pull this into Power Pivot. To do this:

Go to the Power Pivot tab –> PowerPivot Window
From Other Sources –> Others (OLEDB/ODBC) –> Next

Name your table
Paste your Connection String in the box

Click Next –> Next –> Finish –> Close

And voila! We have our Power Query linked directly into Power Pivot in Excel 2010!

Just remember… if you do this, NEVER modify this table in Power Pivot. Always go back to modify the table in the Power Query stage. Failure to do so could set the table into a non-refreshable state.

37 thoughts on “Load Power Query directly to Power Pivot in Excel 2010”

Dominik Petri on April 29, 2015 at 5:13 am said:

Hi Ken,
why don't you use the "Existing Connections" in Power Pivot to grab the Power Query connection? Does it make a difference?
Best regards from Germany
Dominik-who-is-looking-forward-to-your-PQ-book!!!
ruve1k on April 29, 2015 at 6:47 am said:

Ken, great post! This technique seems promising.
However, when I tried it with a SQL Server source, any subsequent modification to the PQ query changed the connection string and therefore caused the Power Pivot refresh to fail. If I would edit the PP connection to use the new connection string then it would refresh successfully again. Not so elegant if it's necessary to update the connection string after each modification to the Power Query. What did you experience after modifying the PQ for an Excel or Text source?
Ken Puls on April 29, 2015 at 8:11 am said:

Damn, you're right. That's not ideal at all. I guess I never tested this with updating it. Fragile at best.

I just tried to work around this by using a referencing a staging query as well, but still no joy. If the base query is changed, you're pooched. 🙁
Ken Puls on April 29, 2015 at 8:14 am said:

Hey Dominik,

Honestly, never looked in there. That's much easier!
AlexJ on April 29, 2015 at 12:28 pm said:

For copying the connection string, after selecting anywhere within the string, Ctl-A should select the whole thing, followed by CTL-C to copy.
chrisj on May 7, 2015 at 1:06 am said:

In my case, the query string keeps being refreshed even if I don't touch Pq nor Pp. Only the "Data Source=$EmbeddedMashup(2cec9d4b-dfd8-45ea-bae1-52153623fa56)" part changes.
So each time I open the wb, I have to copy-paste the new connection string so that Pp can refresh...
Do you guys experience the same issue or does the string only changes when you actually touch Pq or Pp ? Btw I pull data from an excel wb.
Ken Puls on May 7, 2015 at 7:45 am said:

Hi Chris,

That sounds a bit odd to me. In my experience the string only changes if something in the underlying PQ setup changes. PP doesn't exert any influence over the PQ connection string, but every time ANYTHING changes inside the PQ M code, the string gets rebuilt. (Not variables, but if you change the underlying text that makes up the M query.)
chrisj on May 7, 2015 at 1:17 pm said:

Hi Ken,
thanks for the answer !

Yeah it's a bit odd... I tried once more just to make sure, and the same thing happened: Unable to refresh in PowerPivot. It does it even if I only type a 1 in a new sheet.
The only way I found was to unactivate the PowerQuery addin before saving my workbook. That way the query is still functional when I open the wb back.
I think that no matter what, PQ does its stuff in the background and changes the string (the part that contains the actual "name" of the query and not the rest ) when I save the wb. I now have to control my ctrl+s addiction!
I tried with a Json Api as well and PQ has the same behaviour so it's not linked to my xlsx "databases".
My office/PQ/PP might be broken so I'll try a clean install of office next week and if the problem persists I will submit a ticket to MS because it looks like an easy fix to me !

Also Ken, I'm not sure what you mean in the last paragraph of your post (the real one): "NEVER modify this table in Power Pivot". Did you mean PowerQuery ? I'm getting confused!

Sorry for the novel, cheers
Ken Puls on May 7, 2015 at 1:19 pm said:

Hi Chris,

What I mean is this: If you used Power Query to create your table and load it to the data model, never modify the column names in Power Pivot, always go back to Power Query. If you modify the table in Power Pivot it sets it into a state that can't be properly updated.
chrisj on May 7, 2015 at 1:53 pm said:

Hi Ken,
Ok. I did not experience that problem though. I can rename, erase and add columns in PowerPivot just fine even with data pulled from PowerQuery. As long as I save my workbook when PowerQuery is shut down, I can refresh everything nicely.
Ken Puls on May 7, 2015 at 1:56 pm said:

Don't do it! Next time you try to modify the Power Query you'll find that you can't, and the only way to fix it is to delete the entire table and start over.
chrisj on May 7, 2015 at 3:00 pm said:

Ok I see what you mean !
But you can still copy-paste the new query link in the "existing connections" in PowerPivot and then go to "Table Properties" to retrieve your "new" columns. But maybe I'm missing something as my data isn't that complex.
Charlie on May 25, 2015 at 10:43 am said:

I have been banging my head against the wall this past week over this problem - I can not believe power query and power pivot were not meant to be used together.

I find that all I need to do is save the workbook and the connection strings are corrupted such that I have to copy/paste reload them (and there are a lot of connections to reload)

Other than turning off the powerquery addin, is there any other solution to this? I just can't see people using power query and power pivot together with this problem.

--Charlie
Ken Puls on May 25, 2015 at 10:46 am said:

Hey Charlie,

Out of curiosity, are your queries static, or are you using variables to modify the queries at run time? Honestly, neither should trigger a rebuild of the connection string (only an actual modification to the M query should do that.) I'm just trying to come up with some idea of what is happening to trigger that.
Ozeroth on May 26, 2015 at 4:08 am said:

I can reproduce the problem Charlie and chrisj are having
Specifically, in Excel 2010, when I load a Power Query table to PowerPivot using Existing Connections, then simply save the workbook, the connection string somehow changes and I receive an error message if I try to refresh the table in PowerPivot.

It appears the latest Power Query update (version 2.22) is to blame! I recall a similar issue in a version a few months back.

If I revert Power Query to version 2.21, this issue disappears and I can happily save/reopen the workbook and refresh in PowerPivot.

Hopefully this is resolved in the next Power Query update. I will be submitting a frown.
Ken Puls on May 26, 2015 at 7:59 am said:

Ozeroth, thanks for this. I'll report this directly to the Power Query team as well.
Charlie on May 26, 2015 at 11:12 am said:

Ozeroth - thanks - you are a life saver - although difficult, I migrated back to the previous version and now the connections are retained, even after saving to a new name - wow - what a relief
Ken Puls on May 27, 2015 at 10:33 am said:

Charlie and Ozeroth,

Just a heads up. I spoke with someone on the Power Query team and they've confirmed not only that they broke this, but also that the fix should be out in the next update (2.23.x).
Ozeroth on May 27, 2015 at 1:24 pm said:

Thanks Ken, that's great news!
Pablo on May 28, 2015 at 5:16 am said:

Thanks everyone. I can also reproduce the issue. I am working with Excel 2010 and Power Query 2.22. I was going nuts with the broken connection, thinking that it was my mistake. Any idea when PQ 2.23 will be out?
Ken Puls on May 28, 2015 at 6:21 am said:

Sometime in early June I would expect. The release cycle is monthly, so it shouldn't be long.
John Y on May 31, 2015 at 4:47 pm said:

Thanks everyone - same experience as Pablo for me. I was just getting confident using PQ connections, so on May 27 I converted my PQ from loading to worksheet to using connections for my PP data spreadsheets. Then I was having these same experiences above and trying to fix this for days - thanks for the post. I do wonder if while I wait for 2.23 that if I reverted back to loading tables into the worksheet that I could stay on 2.22 instead of 2.21.
Ken Puls on June 1, 2015 at 8:02 am said:

Hi John,

Yes, I would think if you returned to loading PQ --> Worksheet --> PowerPivot that you'd be fine until the update comes out.
Imke Feldmann on June 15, 2015 at 5:37 am said:

Update is out and at least it fixed the problems I had with 2010 🙂
Ken Puls on June 15, 2015 at 6:52 am said:

Great Imke, thanks for confirming!
Nik on August 6, 2015 at 12:32 pm said:

Hi, what is the benefit to going through PowerQuery first and then loading to PowerPivot, as opposed to just loading through PowerPivot? I see that PowerQuery lets you easily pre-filter the data, but this could be accomplished directly in PowerPivot using SQL.

Just downloaded both add-ons so please excuse if I'm missing the obvious benefits.
Ken Puls on August 6, 2015 at 12:45 pm said:

Hi Nik,

It's about the flexibility of being able to transform and combine data sets that aren't ready to consume. If you have SQL skills and access to databases, then by all means go that route. If you don't though, or if you need to stitch a database view together with web sourced data, then Power Query is your friend.
Ted Murphy on August 21, 2015 at 10:08 am said:

Re Dominik Petri's suggestion for picking up the Connection Details from within Power Pivot ... it works great the first time around.

However, when I went back to Power Query and added a new column (concatenating 2 existing columns) I got the dreaded error message ...

More Details:
OLE DB or ODBC error: The query 'sales_reps' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..

I had not made any changes in the Power Pivot Window.

Do I need to do your Cut & Paste of the Connection String at this stage?
Ken Puls on August 21, 2015 at 11:21 am said:

Hi Ted,

Unfortunately, yes. Any time you modify the base query in Excel 2010 you need to do both of these:
-Copy and paste the connection string
-Edit the table properties in Power Pivot and just click Save (making no changes)

It's frustrating, but works.
Christian Vinter on January 23, 2016 at 11:41 pm said:

I think you need to add a comment on the top of the page to say there are smarter ways. As mentioned in the comments or here http://businessintelligist.com/2014/02/07/how-to-load-power-query-directly-into-power-pivot/
Ken Puls on January 25, 2016 at 9:42 am said:

Thanks for the suggestion. To be honest, I've got mixed reviews using that method, especially around updates when things change.
Krishna on June 2, 2016 at 6:44 am said:

Hello Ken,

Thank you. Your method here is the only one working for me. When I use existing connections method I am getting zero rows into PowerPivot. Any idea what I could be doing wrong?

Krishna
Ken Puls on June 2, 2016 at 7:03 am said:

Hi Krishna, honestly, I'm not sure. But if this method is working, I'd stick with it and wouldn't chase the other since neither are officially supported by Microsoft.
Jos on September 27, 2016 at 2:05 am said:

I've had two problems:
Problem 1: Updating data from Power Query with power Pivot. Solved by this article.
Problem 2: When I run the query, I want to keep old data, and new data should be added. Solved by https://blog.crossjoin.co.uk/2013/05/13/accumulating-data-in-an-excel-table-using-data-explorer-and-powerpivot/.

However, those two solutions won't work together. Do you know how to refresh data with PowerPivot, and KEEP the old data?
Ken Puls on November 6, 2016 at 7:46 am said:

Unfortunately the only way I know of to do this is to cook up VBA that pushes the newly loaded data to a database, then grabs your report data from there. It's a bit of a pain to set up, but will work.
Prashant Panchal on March 4, 2018 at 1:36 am said:

when you have made any changes in Power Query, the refresh would generate an error in Power Pivot, I tried copying connection address of revised query connection to PP window but still, it does not work? any suggestions?
Ken Puls on March 4, 2018 at 9:02 am said:

Well, if you've made any changes to the table in Power Pivot to the table you built in Power Query, then the only thing you can do is rebuild the model. (That breaks it completely and permanently.)

It's been a long time since I've done this with Excel 2010, but if I recall correctly, you had to copy the connection string, then go an modify the table inside Power Pivot to add/remove any new columns that were added/removed from the Power Query.