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?

 

[kirchh]

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]

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]

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:
[TABLE="width: 399"]
<TBODY>[TR]
[TD]ID</SPAN>[/TD]
[TD]Legal Name</SPAN>[/TD]
[TD]Owner</SPAN>[/TD]
[TD]Rating</SPAN>[/TD]
[TD]COUNTRY</SPAN>[/TD]
[TD]Status</SPAN>[/TD]
[/TR]
[TR]
[TD]1235489</SPAN>[/TD]
[TD]Citis</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Opening</SPAN>[/TD]
[/TR]
[TR]
[TD]153654</SPAN>[/TD]
[TD]OCBC</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]SGP</SPAN>[/TD]
[TD]Closed</SPAN>[/TD]
[/TR]
[TR]
[TD]456415</SPAN>[/TD]
[TD]HP</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Opening</SPAN>[/TD]
[/TR]
[TR]
[TD]15684</SPAN>[/TD]
[TD]SWISS BAKE</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Opening</SPAN>[/TD]
[/TR]
[TR]
[TD]16848</SPAN>[/TD]
[TD]ACCENTURE</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Opening</SPAN>[/TD]
[/TR]
[TR]
[TD]5384984</SPAN>[/TD]
[TD]CIB</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Closed</SPAN>[/TD]
[/TR]
[TR]
[TD]24894</SPAN>[/TD]
[TD]TYPO</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]USA</SPAN>[/TD]
[TD]Closed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL span=2></COLGROUP>[/TABLE]

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

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?