extracting powerpivot data to excel using evaluate

[masplin]

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]

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]

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]

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]

I am not aware of any difference.

 

[masplin]

ok here's what I see

 

[masplin]

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

 

[Laurent C]

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]

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]

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]

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.