# Extracting static data from powerpivot without pivot tables



## masplin

I started with an excel table as follows

User ID Static1 Static2

I linked to powerpivot and created a bunch of calculated measures so powerpivot window looks like

UserID Static Static2 Calc1 Calc2 etc

I want to output the powerpivot back into excel with the same headings just as rows of data. As far as I know pivot tables cannot output values like names just a count of names or similar. So far the only way I can do it is to copy the whole of the powerpivot window into excel and then format it, but all numbers come out as text. 

Is there an intelligent way to output the powerpivot raw data?


----------



## ruve1k

If you are trying to get a PowerPivot table into Excel, then you can simply query it. The easiest way to set that up is to first create any pivot table based on you PowerPivot data. Then double-click the measures area to drill-through. This creates a table with a connection to your PowerPivot model. Then edit the command text by going to: Data > Connections > Properties > Definition tab > Command Text. Change it to something like 
	
	
	
	
	
	




		Code:
__


EVALUATE Table1

 and then refresh it.


----------



## masplin

Ah perfect thanks a lot


----------



## masplin

Is it possible to only output certain columns by this method?


----------



## ruve1k

Sure.
Use the SUMMARIZE function.
http://technet.microsoft.com/en-us/library/gg492171.aspx


----------



## masplin

Not sure I'm using that correctly as just want the contents of the table not perform any function on it. 

Tried 
	
	
	
	
	
	




		Code:
__


summarize(gyms,gyms[gymID],gyms[gym name])

As my table is called gyms. This says "An MDX statement was expected". do I need to have something that is summed?

thanks

Mike


----------



## ruve1k

Code:
__


evaluate summarize(....)


----------



## masplin

easy when you know how!!! Thanks so much all sorted now

Mike


----------



## kirchh

ruve1k said:


> If you are trying to get a PowerPivot table into Excel, then you can simply query it. The easiest way to set that up is to first create any pivot table based on you PowerPivot data. Then double-click the measures area to drill-through. This creates a table with a connection to your PowerPivot model. Then edit the command text by going to: Data > Connections > Properties > Definition tab > Command Text. Change it to something like
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Code:
> __
> 
> 
> EVALUATE Table1
> 
> and then refresh it.



I have a similar need, but I'm afraid I don't understand some parts of your instructions here. Please excuse my inexperience with PowerPivot (and, really, with Excel in general), but could you be more precise and detailed so that I can follow your directions? For example, you say to "double-click in the measures area to drill-through", but I can't figure out what the "measures area" is (told you I was inexperienced!).

Thank you very much for any assistance you can provide.


----------



## ruve1k

The area in the pivot table where the values are, as opposed to the row labels and column labels.
It might be easier to use DAX Studio to execute DAX queries against your PowerPivot model..


----------



## masplin

I started with an excel table as follows

User ID Static1 Static2

I linked to powerpivot and created a bunch of calculated measures so powerpivot window looks like

UserID Static Static2 Calc1 Calc2 etc

I want to output the powerpivot back into excel with the same headings just as rows of data. As far as I know pivot tables cannot output values like names just a count of names or similar. So far the only way I can do it is to copy the whole of the powerpivot window into excel and then format it, but all numbers come out as text. 

Is there an intelligent way to output the powerpivot raw data?


----------



## kirchh

ruve1k said:


> The area in the pivot table where the values are, as opposed to the row labels and column labels.
> It might be easier to use DAX Studio to execute DAX queries against your PowerPivot model..



Hmm, when I double-click in the area in the pivot table where the values are, I get a message saying "Show Details cannot be completed on a calculated cell." What am I doing wrong?

Thanks.


----------



## kirchh

kirchh said:


> Hmm, when I double-click in the area in the pivot table where the values are, I get a message saying "Show Details cannot be completed on a calculated cell." What am I doing wrong?
> 
> Thanks.


I think I know what's going one here -- the ability to get details on a value is not present in v10 but is there in v11. I will play with it a bit. Thanks!


----------



## kirchh

OK - I have successfully followed the procedure described in this thread to produce an Excel table which reflects a PowerPivot table by modifying the connection definition created by drilling through a value in a Pivot Table. Now, I am trying to figure out how to create that same type of connection from scratch rather than by having Excel create it in response to the drill-through. I have not been successful; I think I'm setting up the connection correctly, but I get the error "Could not open embedded PowerPivot data." Would anyone have a step-by-step procedure for defining a new connection to the embedded PowerPivot model?

Thanks.


----------



## abhinav01

kirchh said:


> I think I know what's going one here -- the ability to get details on a value is not present in v10 but is there in v11. I will play with it a bit. Thanks!


I have powerpivot v11 and I double clicked on values area in pivot table field list and nothing happens. Please let me know if I am doing anything wrong. I wanted to transfer powerpivot data as it is to excel so that I can use it for tableau as data source. Thanks


----------



## FionOctopus

Hi Hi, 

I'm facing the same problem. Instead of using pivot table double click to view the detail, I want to have a fixed filter and a sheet that contains all the data after filtering. 
So it's like I have a sheet called details, inside the sheet, I have a filter called "To be selected", I tick "Yes". below the filter, I will have all the data extracted from powerpivot with "To be selected" as "Yes" showing all the details. 

Is it possible ?


----------



## scottsen

If you want data out of power pivot, and you don't want a pivot table, the typical solution is to use "cube formulas", such as CUBEVALUE().  May peruse powerpivotpro for posts tagged cube formulas: Cube Formulas « PowerPivotPro


----------



## FionOctopus

Thank you!

I tried your approach but my situatio is different The table I would like to have is a one dimention table that have only a fixed header. Something similar to the table below: 

*ID</SPAN>**Legal Name</SPAN>**Owner</SPAN>**Rating</SPAN>**COUNTRY</SPAN>**Status</SPAN>*1235489</SPAN>Citis</SPAN>XXXX</SPAN>A</SPAN>USA</SPAN>Opening</SPAN>153654</SPAN>OCBC</SPAN>XXXX</SPAN>D</SPAN>SGP</SPAN>Closed</SPAN>456415</SPAN>HP</SPAN>XXXX</SPAN>A</SPAN>USA</SPAN>Opening</SPAN>15684</SPAN>SWISS BAKE</SPAN>XXXX</SPAN>A</SPAN>USA</SPAN>Opening</SPAN>16848</SPAN>ACCENTURE</SPAN>XXXX</SPAN>C</SPAN>USA</SPAN>Opening</SPAN>5384984</SPAN>CIB</SPAN>XXXX</SPAN>B</SPAN>USA</SPAN>Closed</SPAN>24894</SPAN>TYPO</SPAN>XXXX</SPAN>A</SPAN>USA</SPAN>Closed</SPAN>

<TBODY>

</TBODY><COLGROUP><COL><COL><COL span=2><COL span=2></COLGROUP>
My problem is I could not have all the columns displayed when I double click on the powerpivot table. In my case, the columns "ID" and "Rating" are missing. I guess it is because in my powerpivot window I mapped "ID" with another table to get the "legal Name" and "Status". Do you have any idea how can I display those mapped columns when I double click on the powerpivot value?

Thanks!


----------



## scottsen

So... just to make sure we are on the same page here.   Here is what I typically do if I just want to "show all the data in my power pivot table"

Drag every field I care about onto "Rows"... reguardless of what table it comes from (lookup table, data/fact table, whatever).
Then I format the pivot table...
 * for each field... in field settings... make sure "Subtotals is set to None", "Show item labels in tabular form" is selected, and "repeat item labels" is selected.
 * for the pivot table, turn off "show expand/collapse buttons"

And that is about it.  Will that work for you?


----------



## FionOctopus

Thank you very much scottsen, that is exactly what I want. I'm so excited now! Thanks a lot


----------



## Jlhoffner

Is it safe to assume that the resulting pivot table from this approach will have the same row limitation of Excel. In other words, if the power pivot table has 2 million records, this won't work?

-J



scottsen said:


> So... just to make sure we are on the same page here.   Here is what I typically do if I just want to "show all the data in my power pivot table"
> 
> Drag every field I care about onto "Rows"... reguardless of what table it comes from (lookup table, data/fact table, whatever).
> Then I format the pivot table...
> * for each field... in field settings... make sure "Subtotals is set to None", "Show item labels in tabular form" is selected, and "repeat item labels" is selected.
> * for the pivot table, turn off "show expand/collapse buttons"
> 
> And that is about it.  Will that work for you?


----------



## masplin

I started with an excel table as follows

User ID Static1 Static2

I linked to powerpivot and created a bunch of calculated measures so powerpivot window looks like

UserID Static Static2 Calc1 Calc2 etc

I want to output the powerpivot back into excel with the same headings just as rows of data. As far as I know pivot tables cannot output values like names just a count of names or similar. So far the only way I can do it is to copy the whole of the powerpivot window into excel and then format it, but all numbers come out as text. 

Is there an intelligent way to output the powerpivot raw data?


----------



## scottsen

Well, they were trying to show all rows by aggregating on some field.  So, if there are fewer rows to aggregate on, you would be fine.  Otherwise... ya, any time you are on excel sheets you are constrained by the row limit.


----------



## potap

abhinav01 said:


> I have powerpivot v11 and I double clicked on values area in pivot table field list and nothing happens. Please let me know if I am doing anything wrong. I wanted to transfer powerpivot data as it is to excel so that I can use it for tableau as data source. Thanks



I have the same problem! Is this supposed to work in Excel 2010?

Edit : Ok, got it to work but my table header is in this format Table1[Column1], Table1[Column2], etc. Can I get rid of the Table1?


----------

