powerquerym

Power Query code to refer to another query (and how buffering works)


I am trying to reuse a power query to pull information from another power query. It seems like there are multiple way of doing this. I see two ways of doing this from this question. I also see it in my own spreadsheets. In my spread sheet I apparently have:

Source = Excel.CurrentWorkbook(){[Name="Project_List"]}[Content],

In other queries I have:

Source = Project_List,

Is there an advantage to one method or the other?

Ultimately what I am hoping to achieve is to query the data once across the network, dump it into a table A then in follow up queries keep referring to table A and not pull the data across the network for the subsequent queries.

I noticed that when I was building my most recent query using the Source = Project_List,, at each step of the follow up query it was loading actually reloaded all the files that Project_list query loads.

Is this just part of the building process? Or If I have one query A that loads across the network and 5 follow up queries that refer to query A will power query / excel be reading the across the network 1 time or five times?

What is the proper way in Power Query to refer to an existing query and reduce data pulls across the network?


Solution

  • Power Query will always stream data from source - see here for a fuller understanding.

    https://www.youtube.com/watch?v=AIvneMAE50o

    The only way around this is to use Table.Buffer or List.Buffer to temporarily cache the data.

    For this line, then presumably you have another query named Project_List which in turn gets your data from the workbook?

    Source = Project_List,