Does excel remember existing data when importing the same data in multiple Power Queries?

Question

I can't find the exact answer to this and I want to be certain my assumption is correct since I am to rebuild an Excel doc someone else produced.

In short, if an excel document imports from the same file path in multiple queries, does excel remember any data it already got to save memory? My assumption is no and that for each query the data will be imported again.

In terms of code, I mean something like the following:

Query1
let 
source = Folder.Files("FilePath")
#..other steps

Query2
let
source = Folder.Files("FilePath")
#..other steps

The other steps aren't so relevant to my question and don't necessarily have to be the same steps. I just want to know, since the query sources are the same, does Excel remember anything to save memory? I assume not and that it's expected for the creator to reference already imported data. I cannot find exact confirmation though.

For some context, I believe this could be one of many reasons slowing the file down so much, especially as it's more than 2 queries in my case.

teylyn · Accepted Answer · 2021-10-13 19:49:48Z

1

If you access the data from its original source, then it will be accessed in each query. You can use a different approach if you want to minimise the duplication.

RawDataQuery
let 
   source = Folder.Files("FilePath")
in
   source

Query1
let
source = RawDataQuery
#..other steps

Query2
let
source = RawDataQuery
#..other steps

In the early releases of Power Query, this would still load the data several times, but the more recent versions should only load the data once.

answered Oct 13, 2021 at 19:49

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alexis Olson Over a year ago

In Power BI, you can choose whether to load queries or not in the Query Editor. In Excel, I thought it would load all three queries but you could delete the sheet with the RawDataQuery output and it would keep the query as Connection Only. Has this changed recently?

teylyn Over a year ago

@AlexisOlson This is not well documented. Following a request on UserVoice (which no longer exists, due to MSFT moving to a different platform, but here is the cached Google result for that page), Microsoft rolled out changes that improve performance by caching child nodes. ... more

teylyn Over a year ago

... See Ken Puls' blog when it was released in O365 insider builds. It is not quite clear whether this feature made it into the Office 2019 build, and it's hard to test, since the feature is not listed or identified anywhere.

Collectives™ on Stack Overflow

Does excel remember existing data when importing the same data in multiple Power Queries?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related