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]

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]

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]

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

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