Log In
Register
Home
February 19, 2021
Offline
Hello,
I have several Excel Power Queries in my excel sheet and several Pivot Tables on several Tabs which are made from the data that are the result of the several queries.
Now I want to automate everything that after the new variables are selected that everything will be refreshed when i activate the refresh All function.
The queries have a position, the previous has to be completed before the next query may start.
After all the queries are processed each pivot table has to be refreshed.
Is it possible to arrange this in power query by changing options or parameter.
If it is possible we want to make this happen without using a Macro or VBA-script.
If it isn't possible without using a Macro or VBA-script than i want to ask if someone can help/make me a script that refreshed the queries one after the other is completed and then refreshes the pivot tables.
In attachment a print screen of the power Queries in a sheet.
Thank You In Advance.
My Best Regards.
July 16, 2010
Offline
Hi Marc,
The queries should refresh in order by default. The PivotTables will only refresh correctly the first time if they are referencing the queries direct, as opposed to being built based on the tables that are the query outputs. i.e. If you have closed & loaded the queries to a table and then built the PivotTable from there, then you will have to Refresh All twice, once for the queries and once for the PivotTables. This is because the PivotTables refresh on the first Refresh All before the queries have had a chance to load the data to the tables being referenced by the PivotTables.
Hope that makes sense.
Mynda
February 19, 2021
Offline
Hello Mynda,
Is it possible to let refresh everything (queries and Pivot-tables which are built from a table) automated by pressing Data Refresh All.
Our goal is to refresh all queries and pivot tables with one click and if possible without using a macro.
Thanks in advance.
July 16, 2010
Offline
Hi Marc,
Yes, but as I explained above, if you load the data to a table and then build a PivotTable from it you must Refresh All twice, once for the query to load the data to the table and then again for the PivotTable to pick up the new data. This is because with the first Refresh All the PivotTable refreshes before the Query has time to load the data to the Table being referenced by the query.
Mynda
1 Guest(s)
