SOLVED

Refresh All Order

Occasional Contributor

I have several PowerQuery connections on workbook and some Pivot Tables based on results

 

When I do Data/Refresh All then 

 

  • 1st is refreshed pivot tables and
  • 2nd PowerQueries

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

6 Replies

@Henn Sarv 

 

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)

 

@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.

best response confirmed by Henn Sarv (Occasional Contributor)
Solution

@Henn Sarv 

I 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. 

Thank You - looks like there is the point

Background refreshing breaks dependency chain
One more tech thing I not jet find any solution

Function =CELL("filename";a1) must give me path to the folder I keep my things
Unfortunately this gives me something like https://d.docs.live.net/cd1fc5b8xx99xxx2/... becouse I have to store my file (and reference file with data) in Onedrive to be synced with all PS-s I use

The other function =INFO("DIRECTORY") works weird

When I start Excel with empty workbook (or some other workbook) and open my workbook then this function works fine and gives my reference to right folder (My Documents in Onedrive)

BUt when I open Excel with my Workbook (1st document) then this same function points to C:\WINDOWS\system32\ and my reference folder isn't correctly found

I have no idea how CORRECTLY point to Onedrive folder from PowerQuery (there is no such function to access Environment or ...) and I need to use calculation from Excel

This is actually case prevent me transfer some PowerQueries to PowerBI - thee is no such workaround



@Henn Sarv 

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