Change Pivot Table data source from table on worksheet to data model

[TomBird]

I have an excel file where I started building out pivot tables but as I learned power query and the usefulness of measures I want to change some of my pivot tables that are pointing to ranges or tables in my spreadsheet to my data in the data model and I cannot figure out any way to do that. Is that possible so I don't have to recreate everyone of the pivot tables with a new connection to the data model or is that the only option?

Thanks

 

[bobsan42]

I think you are a bit confused here.
Maybe I am not all-knowing myself and someone else can explain it better, but here's my take:

PowerQuery and PowerPivot (Data Model) are two separate and different things

PowerQuery can create/define queries to other data sources. These definitions are are called connections. Resulting data can be shown in a table or just used as datasource for a Pivot table or for another query. (Select a pivot table, goto PivotTable Analyze > Change Data Source ... - here you can choose an existing connection to be used as data source)

PowerPivot helps you create links/relationships between various data sources (e.g. tables) - this allows you to include related data from different tables in a single PivotTable. But you cannot use the DataModel as datasource directly.

I hope this helps.

 

[TomBird]

When I originally setup my Pivot Table I based it off a table on a separate tab called Data. If you see the screen shot below, I don't have the option to select from the data model it is grayed out.

I have subsequently added that "Data" table to the Data model so I can use measures in Power Pivot. What I cannot figure out is how to switch pivot tables pointing to my "Data" table on the worksheet to the Data I have used in the Power Pivot data model where I have built my measures. I can just rebuild all my Pivot tables but that is a major pain given I have around 10 of them in this workbook.

Thanks

 

[bobsan42]

When I originally setup my Pivot Table I based it off a table on a separate tab called Data. If you see the screen shot below, I don't have the option to select from the data model it is grayed out.
View attachment 124287

I have subsequently added that "Data" table to the Data model so I can use measures in Power Pivot. What I cannot figure out is how to switch pivot tables pointing to my "Data" table on the worksheet to the Data I have used in the Power Pivot data model where I have built my measures. I can just rebuild all my Pivot tables but that is a major pain given I have around 10 of them in this workbook.

Thanks

Try to re-read my post and make sense of it! You cannot build a pivot table on the DataModel alone.
But you can build a pivot table on all tables/fields available in the DataModel taking into account their relationships:

 

[Alex Blakenburg]

Unfortunately I don't believe there is any way of changing the Data Source of the Standard Pivot from a Table or Range to the Data Model without rebuilding the table.
It might be possible to automate it using VBA but coding it would probably take longer than manually rebuilding your pivots.
Also the pivot field addresses in any GetPivotData formula you may have used are going to change.

 

[TomBird]

Unfortunately I don't believe there is any way of changing the Data Source of the Standard Pivot from a Table or Range to the Data Model without rebuilding the table.
It might be possible to automate it using VBA but coding it would probably take longer than manually rebuilding your pivots.
Also the pivot field addresses in any GetPivotData formula you may have used are going to change.

That is what I started doing.

 

[Alex Blakenburg]

Sorry we didn't have better news for you.