PowerPivot: is it possible to use a pivot table as a data source?

[Plexus]

Hi,

I do not believe it is possible, but has anyone a workaround to use a PowerPivot pivot table as a data source in the same Workbook? I did read it can be done using PowerQuery, but I get the same error.

The error I get is:

[FONT=&quot]"The selected range is invalid. Select a valid range. (A table cannot overlap a range that contains a PivotTable report, query results, protected cells, or another table.)"

Thanks,

Plex.[/FONT]

 

[sheetspread]

You can paste the PT as values, will that work for you?

 

[Plexus]

Thanks for the reply sheetspread,

Yeah I have managed the paste new table solution, but of course there is no link back to the pivot. I require the table to be linked to enable a refresh. This is where I am struggling.
So frustrating!

 

[sheetspread]

You can record a macro to refresh and paste as values, or pivot the source data in power query which can then be the base of another query.

 

[Plexus]

I will try the powerquery option, see if I can make that work. Thanks again for your help.

 

[SpillerBD]

It's been a bit since I did a Pivot Table based on a Pivot Table (no Power Pivot) but since then I've been exposed to DAX and my impression has been that the screwy reasons I needed to stack can be solved using DAX. Maybe the same for you ?
You might ask your "need" more specifically data-wise for help in that direction.

 

[Plexus]

It's been a bit since I did a Pivot Table based on a Pivot Table (no Power Pivot) but since then I've been exposed to DAX and my impression has been that the screwy reasons I needed to stack can be solved using DAX. Maybe the same for you ?
You might ask your "need" more specifically data-wise for help in that direction.

Hi Spiller,

Yes I can achieve the same solution using DAX. However I am rather new to writing DAX and it is quite a complicated solution. The pivot stack is a simple work around. I am attempting to to write the report though as well as using the work around.

Thanks,

Plex.

 

[Plexus]

You can record a macro to refresh and paste as values, or pivot the source data in power query which can then be the base of another query.

Do you have a link/example of how to use the powerpivot pivot table as the source data in powerquery? I have not been able manage it so far.

Thanks,

Plex.

 

[sheetspread]

I meant use the source data of the pivot table, are you able to?

 

[Plexus]

I meant use the source data of the pivot table, are you able to?

No I am actually trying to use the data from the Pivot. This is what I do not think is possible.