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



## Plexus (Aug 22, 2016)

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 (Aug 22, 2016)

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


----------



## Plexus (Aug 22, 2016)

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 (Aug 22, 2016)

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 (Aug 22, 2016)

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


----------



## SpillerBD (Aug 22, 2016)

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 (Aug 23, 2016)

SpillerBD said:


> 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 (Aug 25, 2016)

sheetspread said:


> 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 (Aug 25, 2016)

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


----------



## Plexus (Aug 25, 2016)

sheetspread said:


> 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.


----------



## Plexus (Aug 22, 2016)

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 (Aug 25, 2016)

I'm pretty sure you can't do this directly. Do you not have the source data? Unpivoting the table is another option (either via Power Query or regular Excel).


----------



## Plexus (Aug 25, 2016)

sheetspread said:


> I'm pretty sure you can't do this directly. Do you not have the source data? Unpivoting the table is another option (either via Power Query or regular Excel).



Thanks for the help sheetspread. I have decided against this option. I am actually trying to workaround due to lack of DAX skills. I have therefore gone for the simple option of exporting and re-importing the data. 

I am also working on the DAX solution, but it is hard work. I am attempting to apply a complicated calculation on different levels of granularity. I will probably end up back here once again asking for help once I have got the DAX as far as I can.

Thanks again for your support, its most appreciated.


----------



## adre (Oct 24, 2022)

There are two ways you can use a pivot table as a data source for Power Pivot.


Use the worksheet or Excel tab as the source for Power Query. PQ will read the entire sheet/tab including the pivot table. You can then proceed to transform the data anyway you like. Be aware that the size of the pivot table could change due to changes in the source of the pivot table.
Convert your pivot table to cube functions using OLAP. Then the pivot table can be treated as any other range / table and can be loaded to data model.
Hope this helps.


----------

