Successful access to pivot table's data from Power Query.

[M3dHutch]

I would like to access the data represented in a Excel Pivot Table from Power Query...  after trying several ways it seems there is no way to get past the error;  "A table cannot overlap a range that contains a PivotTable Report, query results, protected cells or another table."

I guess this is not possible to access this data directly from the Pivot Table... indeed, I suppose what I have IS in fact a table that is itself a Pivot Table Report. (it actually overlaps itself).

Quit obviously, I could instead import the data from the Table my Pivot Table references but would not be my first choice. Wondering if I need to re-create the Pivot Table and toggle on to "Add  this data to the Data Model"?   Should this allow me to access it's data?

[Anonymous]

Hi @M3dHutch ,

The error message you're seeing typically occurs when Power Query attempts to import data from a range that is already occupied by a Pivot Table or other special Excel features. Unfortunately, Power Query cannot directly access data from a Pivot Table because it is designed to work with raw data ranges or Excel tables.

Here's what you can do to work around this limitation:
1. Access the Source Data Directly;
2. Create a New Table from the Pivot Table. Select the entire Pivot Table, copy it, and paste it as values into a new worksheet. --> Convert this range of pasted values into an Excel Table (Insert > Table). --> Now, you can import this new table into Power Query.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group