Load Power Query directly to Power Pivot in Excel 2010

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:

image

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:

image

  • Select your Query and click Properties
  • Click the Definition tab

Now you’ll be looking at something like this:

image

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

image

  • Name your table
  • Paste your Connection String in the box

image

  • Click Next –> Next –> Finish –> Close

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

image

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.

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

  1. 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!!!

  2. 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?

  3. 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. 🙁

  4. For copying the connection string, after selecting anywhere within the string, Ctl-A should select the whole thing, followed by CTL-C to copy.

  5. 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.

  6. 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.)

  7. 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

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. 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.

  14. 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.

  15. 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

  16. 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).

  17. 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?

  18. 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.

  19. Hi John,

    Yes, I would think if you returned to loading PQ --> Worksheet --> PowerPivot that you'd be fine until the update comes out.

  20. 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.

  21. 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.

  22. 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?

  23. 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.

  24. Thanks for the suggestion. To be honest, I've got mixed reviews using that method, especially around updates when things change.

Leave a Reply

Your email address will not be published. Required fields are marked *