Thank you! I have quite huge massive of combined data and don't want export it in a table, just leave as a query connection only. But going this way if I build the pivot based on this query, all the pivot tables become connected with each other. I mean if I change filter in one pivot it influence on another. If I try to build another pivot (without copying previous one) excel create the duplicate of the query :( In 2013 Excel ver. where PQ were just an add-in, were no such issue. Do anyone have a solution?
In Excel 2013, Power Query (formerly known as Power Query) was indeed an add-in, and its behavior might have been different compared to newer versions where it's integrated more tightly. However, you can still work around this issue in newer versions of Excel (such as Excel 2016 and later) by following these steps: 1. Create Separate Queries for Each Pivot Table: - Instead of using the same query for all pivot tables, create a separate query for each pivot table you want to build. - To do this, you can duplicate your original query and apply any additional transformations or filters specific to each pivot table. 2. Load Data to the Data Model: - After creating separate queries, load each query into the Excel Data Model. - Go to the "Data" tab, click on "Queries & Connections," and then select "Connections." - From there, you can load each query to the Data Model by checking the "Add this data to the Data Model" option. 3. Create Pivot Tables from Data Model: - Instead of directly creating pivot tables from individual queries, create pivot tables from the Data Model. - Go to the "Insert" tab, click on "PivotTable," choose "Use an external data source," and then select the Data Model. - You'll see all your queries listed in the Data Model. Choose the specific query you want to use for each pivot table. 4. Configure Pivot Tables Independently: - Since each pivot table is now based on a separate query loaded into the Data Model, they should be independent of each other. - You can configure filters, slicers, and other settings for each pivot table without affecting the others. By following these steps, you should be able to create multiple pivot tables based on different queries from the same data source without them being interconnected. Excel's Data Model allows for more flexibility and independence in managing pivot tables linked to external data.
Very nice. This man is Excel himself
As always, you’re an excellence teacher. Thanks.
This was new and so useful, You're a wonderful teacher. Thank you Sir..
Helping me at my new job, thanks!
Happy to help!
Clear and useful, I think I'm getting a better idea on how Power BI works with this.
Sooo useful thank you! 😊
Glad you found it useful. Power Query is a very powerful tool.
loving the beard!
Thank you!
I have quite huge massive of combined data and don't want export it in a table, just leave as a query connection only. But going this way if I build the pivot based on this query, all the pivot tables become connected with each other. I mean if I change filter in one pivot it influence on another.
If I try to build another pivot (without copying previous one) excel create the duplicate of the query :(
In 2013 Excel ver. where PQ were just an add-in, were no such issue.
Do anyone have a solution?
In Excel 2013, Power Query (formerly known as Power Query) was indeed an add-in, and its behavior might have been different compared to newer versions where it's integrated more tightly. However, you can still work around this issue in newer versions of Excel (such as Excel 2016 and later) by following these steps:
1. Create Separate Queries for Each Pivot Table:
- Instead of using the same query for all pivot tables, create a separate query for each pivot table you want to build.
- To do this, you can duplicate your original query and apply any additional transformations or filters specific to each pivot table.
2. Load Data to the Data Model:
- After creating separate queries, load each query into the Excel Data Model.
- Go to the "Data" tab, click on "Queries & Connections," and then select "Connections."
- From there, you can load each query to the Data Model by checking the "Add this data to the Data Model" option.
3. Create Pivot Tables from Data Model:
- Instead of directly creating pivot tables from individual queries, create pivot tables from the Data Model.
- Go to the "Insert" tab, click on "PivotTable," choose "Use an external data source," and then select the Data Model.
- You'll see all your queries listed in the Data Model. Choose the specific query you want to use for each pivot table.
4. Configure Pivot Tables Independently:
- Since each pivot table is now based on a separate query loaded into the Data Model, they should be independent of each other.
- You can configure filters, slicers, and other settings for each pivot table without affecting the others.
By following these steps, you should be able to create multiple pivot tables based on different queries from the same data source without them being interconnected. Excel's Data Model allows for more flexibility and independence in managing pivot tables linked to external data.
Is this dynamic?
Do you want to know will it update when data changes?
Thank you!