Forum Discussion
Load one query into multiple pivot tables
So a couple things. You'd be loading the data table from Power Query, the table should automatically load as a proper Excel table, so that your Pivot Table source can just be Table_Name (if that were the table name that you loaded to the worksheet). When you refresh, the pivot table source is still the Table_Name, and so the range will automatically update.
As for the calculated fields, you most certainly can add calculated fields to the data model, and they will show up in your pivot table. You just need to create the calculated fields in the data model. Click on "Manage Data Model", and then add your calculated coulmn in the "Add Column" space. Or add a measure in the space below the table. Either way, it will load to your pivot table.
--Nate
Since you have to load the raw data to a worksheet, why not create your two pivot tables from the worksheet data table, as opposed to the query?
--Nate
Hello Nate,
The main reason I wasn't convinced about that was that I would need to adjust the range of the table manually if the size of the table changes after a refresh. Otherwise the pivot table wouldn't use the entire data.
I also thought about reading from the file itself, but that would add the extra step of saving the file before refreshing the pivot tables, which I was trying to avoid.
If you think of any other solutions, please let me know. If I could find I way to get calculated fields on a pivot table that's based on the data model, then that would be good enough.
Thanks!
Ignacio