Load one query into multiple pivot tables

[Anonymous]

Good morning,

I've got a very demanding query on Excel that I need to load as 2 different pivot tables as well as loading the raw table to a sheet on my worksheet.

I've tried loading the query as a table, and then inserting the pivot tables and connecting them to the same query, but this results in the connection being duplicated and taking almost 9 minutes to refresh.

I also tried loading the query to the data model and connect the pivot tables there instead, but I can't add calculated fields that way, which I need for my pivot tables.

I'm not sure if I'm missing some way to do this without tripling my refresh time or giving up on calculated fields.

Thanks for your help

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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