How do you reference a Pivot Table in the Data Model in Excel 2013

Question

Hi!

I have a data model established with around 9 small tables.  I used the Data Model to create a Pivot table (ticking add to Data Model on creation) using the data model data sources (the pivot table combined details from a couple of data model tables together).

What I want to do now is use the pivot table as a table in the data model, and create relationships to and from it.  However, I can't seem to get the Pivot table into the data model.  

On creation I did click add to data model (in fact I have tried this a couple of times) and the pivot table doesn't show up in the Data Model - at least, I can't see it in either the diagram or table view.  I have also tried converting the pivot table to a table (using Ctrl T) but I get an error message that the range can't include a pivot table!.

Any help appreciated.

Thanks in advance

Mark

Answer 1

Instead of Ctrl T,

Select applicable portion of PT

Copy > Paste Special > Values

Convert it to a Table and edit it with Power Query

Add Table to Data Model.

If lost, upload file.

Answer 2

Thanks Herbert and Ashish, I will go with that for now!

Answer 3

Hi,

A Pivot Table does not appear in the Data Model.  Infact the Pivot Table is a result of the individual Tables appearing in the Data Model.  It looks like Herbert's is the only viable solution.

Answer 4

Thanks Herbert, I had considered that but was hoping for a more elegant solution that didn't involve a manual step.  

Is the fact that the pivot table doesn't show up in the data model a known issue?