Static Data in Power Bi Desktop

[jimrward]

still trying to understand this beast, I have achieved what I want using PQ in Excel and decided to download PBI desktop and try and migrate my queries across
I have a csv data set with 120k plus rows of data which I read in, knock off a few columns add a few custom ones and name some columns
I then use this as a source for subsequent queries of this new data set
it appears that any child queries from the parent query, PBI reloads the csv every time all 120k+ rows
How do I make the initial load of the CSV static because for all intents and purposes it is once loaded
At the moment I am just choosing New Query and pointing source to the MasterData, do the query I wish
for each child query I create I get the wheels a turning saying loading csv for each one from child 1 to N

 

[Matt Allington]

You can try taking the last step in the main query, go into the M editor and wrap it in Table.Buffer(). This function should load the entire table/csv into memory. Sometimes it makes it worse, but it’s with a try

 

[jimrward]

@Matt Allington What is the lifetime for want of a better word of a buffered table, does it get released at query end or does it persist for entire session
currently my child queries are setup as
line 1 source=masterdata
line 2 mybuffer= table.buffer(source)
so with your suggestion it would imply once buffered in the main query it will be available to all sub queries
with my approach of buffering in each sub query is going to flood memory with multiple instances of the same buffer

 

[Matt Allington]

short answer, IDK. What I believe should happen is the table should be loaded into memory during refresh, and avail for reuse from memory. But there are many codependencies, such as memory available, caching to disk, etc. as I said, I have seen as many instances where it makes it worse as when it makes it better. Try it, and see what happens.

 

[jimrward]

I will test setting up the buffered data as last line in my initial query as see if I can reference it in a child query and report back
might be a couple of hours until I can do that

 

[jimrward]

from testing i can with my limited knowlege find that the scope of a Table.buffer is to the query it was declared in and cannot be seen in subsequent separate queries.
I can only make reference to the master query and buffer it in the initial 2 steps, with PBI in the background refreshing the master query in any subsequent follow on queries, not very optimal