# extracting powerpivot data to excel using evaluate



## masplin (Mar 5, 2013)

I have been using office 2010 and powerpivot with linked excel tables. Someone helpful taught me that to extract the powerpivot data into an excel table I could double click on any cell in a pivot table then go to data/connections/properties/definaitions and change the text in "command text" box to evaluate summarize etc etc. This works absolutely perfectly.

I am just experimenting with Office 365 Pro plus Excel 2013 using a powerpivot model built from an SQL query. When I try to do the same thing I can't see anything in connections that refers to this new sheet and can be edited.  Is there some other way to extract data from the powerpivot model please?

Thanks
Mike


----------



## masplin (Mar 5, 2013)

I just found this link that explains how to do it, but doesn't make any sense to what I see

DAX Queries in Excel 2013 | The Data Specialist

He says select connection and then a table, but I only see a list of tables that I have linked manually not the tables i have got SQL connections to .  What I see is  SQL connection name, ThisWorkbookdataModel and a bunch of links to manual tables.


----------



## Laurent C (Mar 5, 2013)

Selecting ANY table should be fine since you will write a DAX query anyway.


----------



## masplin (Mar 6, 2013)

I'm still stuck. I found this post that says what you need to do

Kasper de Jonge PowerPivot Blog » Implementing histograms in Excel 2013 using DAX query tables and PowerPivot

He says "Lets go back to Excel, select the data tab, click on Existing connections and select Tables:".  He then gets a window called "existing connections" with 2 tabs. When I go into excel and click on "Data" I only "connections" not "existing connections" and if I select that I just get a window called "workbook connections". Please can someone explain what is being selected that gets me to this "existing connections window and table tab?

Thanks
Mike


----------



## Laurent C (Mar 6, 2013)

Do you have a connection called "ThisWorkbookDataModel"?


----------



## masplin (Mar 6, 2013)

yes, but properties is greyed out


----------



## masplin (Mar 11, 2013)

does anyone have any ideas as I'm completely up the creek without a paddle. i've converted my model and made some changes but can no longer extract data that feeds other parts of my company?


----------



## Laurent C (Mar 11, 2013)

Hmmm ...  I thought I had already posted an answer. So ...

My question about "ThisWorkbookDataModel" was just a regular "is the computer on?" question, to check if you had a model in it.

If I link a SQL table from PowerPivot and then go to Data > Existing Connections > Tables, then I can see links to my linked tables and to  my SQL Server Connection.

Anyway, even if you do not see your SQL connection,  you should be able to choose a linked table, re-import the data, and then edit the query by right-clicking on the table and choosing Edit DAX. You will be querying the model, not the direct source.

If you cannot re-use any exisiting table connection, then try to create a new table, and see if it works with this one.


----------



## masplin (Mar 11, 2013)

Hi Laurent. so I click on existing connections and see "select an existing connection" with 3 links powerpivotdata connection, local connection and workbook connection. Nothing like "tables"

If I right click on a table imported by SQL I just get copy or filter no choice of edit DAX?  i tried a clean workbook and imported a new table and still no sign of "edit DAX".

I must being really stupid that i can't understand this.


----------



## Laurent C (Mar 11, 2013)

Once you have gone to Data > Existing Connections, the dialog opens. They added a new tab called "Tables" there.


----------



## masplin (Mar 5, 2013)

I have been using office 2010 and powerpivot with linked excel tables. Someone helpful taught me that to extract the powerpivot data into an excel table I could double click on any cell in a pivot table then go to data/connections/properties/definaitions and change the text in "command text" box to evaluate summarize etc etc. This works absolutely perfectly.

I am just experimenting with Office 365 Pro plus Excel 2013 using a powerpivot model built from an SQL query. When I try to do the same thing I can't see anything in connections that refers to this new sheet and can be edited.  Is there some other way to extract data from the powerpivot model please?

Thanks
Mike


----------



## masplin (Mar 12, 2013)

mine doesnt look anything like that!!!!!  do I need to save the image on my pc so i can then link it to the post?


----------



## Laurent C (Mar 12, 2013)

Apparently, you cannot upload pictures directly on this forum. You can save the file elsewhere and provide the URL I chose the easy path and linked to Kasper's blog.

At that point, however, a question is necessary. Did you really download the Professional Plus version?


----------



## masplin (Mar 12, 2013)

I signed up for Office 365 pro Plus trial. I must have or I couldnt be editing the powerpivot model could I?  Does seem odd. Could there be some difference between the Office 365 version and the Office 2013 version? i'll try to upload thei mage of what i'm seeing.


----------



## Laurent C (Mar 12, 2013)

I am not aware of any difference.


----------



## masplin (Mar 12, 2013)

ok here's what I see


----------



## masplin (Mar 12, 2013)

hmm I uploaded the image in flickr and then apsted the url in the above post but doenst seem to have worked?


----------



## Laurent C (Mar 12, 2013)

The embedding did not work, but at least I could find the URL, and I might have an answer to your problem:
I guess you are looking at the dialog Existing Connections in the PowerPïvot window.

The Existing Connections dialog I am talking about is the plain old regular Excel dialog (from the Workbook). I updated my blog post.


----------



## masplin (Mar 12, 2013)

ok so in excel I click "data". I then see "connections" not "existing connections".  Now i get a box called workbook connections with entries like

LinkedTable_Advert (this a new table i added since upgrading)
ServerSWL 192.168 etc etc
ThisworkbookDataModel
Worksheetconnect_CAMCurrent.xlsx!Account-Type (these are linked tables I had in the original 2010 version and CAMcurrent was the name of the sheet. )

Now i see to be able to go into the LinkedTable properties/definition and edit the command text.  So do I just add some throw away table and then edit for my evaluate summarize?  Seems incredibly inelegant compared to what you showed?  Is this because this workbook was converted from 2010 rather than created fresh in 2013?

more worryingly do you have any idea what those last tables are that seem to be linked to the original file? I'm wondering if it is trying to pull data form a file that isn't open hence causing all my crashes and memory issues?  Shouldn't the reference be the current file name which is CAMOffice365?

Thanks

Mike


----------



## Laurent C (Mar 12, 2013)

On the left of "Connections", you should see From Access, From Web, ..., Existing Connections ... look around. If it is not there, go to your options (Quick Access Toolbar or Customize Ribbon)  to customize the quick access toolbar or the ribbon. The name of the command is "Get Data Using Existing Connection." It should be there by default, though.


----------



## Laurent C (Mar 12, 2013)

I am not sure which are "those last tables". Do you mean "ThisWorkbookDataModel" and "Worksheet_CAMCurrent.xlsx!Account-Type"? These are fine, they belong to the new data model. Or are  you worrying about the "_CAMCurrent.xlsx" part? I am pretty sure the connection does not change when you rename a workbook, but the model will link to the current file. However, you can test that by adding some data to your linked table and updating a pivot table.


----------



## masplin (Mar 5, 2013)

I have been using office 2010 and powerpivot with linked excel tables. Someone helpful taught me that to extract the powerpivot data into an excel table I could double click on any cell in a pivot table then go to data/connections/properties/definaitions and change the text in "command text" box to evaluate summarize etc etc. This works absolutely perfectly.

I am just experimenting with Office 365 Pro plus Excel 2013 using a powerpivot model built from an SQL query. When I try to do the same thing I can't see anything in connections that refers to this new sheet and can be edited.  Is there some other way to extract data from the powerpivot model please?

Thanks
Mike


----------



## masplin (Mar 12, 2013)

hmm Exisitng connections is there but greyed out?


----------



## Laurent C (Mar 12, 2013)

Screenshot?


----------



## masplin (Mar 12, 2013)

PP Connections | Flickr - Photo Sharing!

Tried various tabs and all the same.


----------



## masplin (Mar 12, 2013)

PP Connections | Flickr - Photo Sharing!

Tried various tabs and all the same.


----------



## Laurent C (Mar 12, 2013)

It looks like the current cell belongs to a pivot table or a table object. Try to select a free cell, and look again.


----------



## masplin (Mar 12, 2013)

What an idiot I am!!! Thank you so much for having the patience to point me in the right direction.

Mike


----------



## Laurent C (Mar 12, 2013)

No problem. Most issues are just about missing small things


----------

