Jun 24 2021 12:58 AM
I have several PowerQuery connections on workbook and some Pivot Tables based on results
When I do Data/Refresh All then
so I need manually refresh things in right order or refresh all twice - a bit frustrating
Is there ANY way to assign refreshing order or is some hope that in future this ordering will be fixed
Jun 24 2021 04:34 AM - edited Jun 24 2021 04:36 AM
Query and process data with Excel and Power BI
Excel offers the option of automatically updating queries at specific time intervals or when the file is opened. This can be beneficial if the source file changes frequently and you want to make sure you always have the latest data.
On the other hand however, it can lead to disruptive delays.
So it always depends on the respective Use case depends on whether an automatic update makes sense.
If the list of queries is not visible, open it via Data - Queries and Connections.
Right click on a query and choose Properties In the Query Properties dialog you can,
for example, set the query to be updated every hour.
Or you can have it automatically update each time you open the file.
If you enable this setting, you also have the option to use the “Remove data from external data area before saving worksheet”.
This means that the table with the imported data does not match the workbook
is saved, but must always be reloaded.
This reduces the file size, especially with large tables.
Another benefit of this setting is that
Data security: In the case of sensitive source data within a company network, the file is
with the query unusable as long as the user is outside the network.
Manual updates are even faster with the key combination Ctrl + Alt + F5.
I'm not a specialist in external connections, as well as Power Queries and Power BI.
Can only handle languages well and have researched this for you on the Internet.
All information without guarantee :)).
Hope this information can help you. If this is not what you are looking for please ignore it.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Jun 24 2021 06:49 AM
@NikolinoDE - unfortunately no help
both options are well known and I know how to use them. My question is
"how to control the ORDER excel makes refresh (actually both during opening or during refresh all)
Actual behavior - all Pivot Tables refreshed first
All Queries refreshed after that
Logical order for refresh all might be dependency order like for formulas
PS! I'm 25+ Year Excel user (quite prof) but this particular option is hidden for me
PPS! and please don't offer create macros to refresh is predefined order - I know how to use macros but I hate macros.
Jun 24 2021 10:48 AM
SolutionI didn't touch this issue for a long while, perhaps something from here https://techcommunity.microsoft.com/t5/excel/pivot-table-will-not-update-properly/m-p/100885 could help.
Jun 24 2021 11:02 AM
Jun 24 2021 11:18 AM
Jun 24 2021 01:53 PM
Do we speak about OneDrive Personal? I have no experience with it, I only know that Power Query doesn't like it. Loading a File from OneDrive Personal into Power BI Desktop - Excelerator BI
Jun 24 2021 10:48 AM
SolutionI didn't touch this issue for a long while, perhaps something from here https://techcommunity.microsoft.com/t5/excel/pivot-table-will-not-update-properly/m-p/100885 could help.