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]

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]

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

 

[masplin]

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]

Do you have a connection called "ThisWorkbookDataModel"?

 

[masplin]

yes, but properties is greyed out

 

[masplin]

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]

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]

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]

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