How to only do one "data load" of appending source files together

Power Query
1

Hi all,

I have a set of queries, and one of them is a “source data” query which most of the other queries reference.

This source data query looks at a folder and appends all the files within it.

My issue is… whenever I make changes to the queries that reference the source data, it becomes unbearably slow. And I can see in the bottom right that it’s appending all the files again.

Just wondering what solutions are available? I can’t imagine I’m the only person to experience this issue.

Ideally I’d like to have one source query, and for it to “load” the data for the other queries to use. And if no files or added or changed within the folder, it shouldn’t need to append all the files again.

2

Hi @Callum173,

Welcome to the Forum.

What you see in the Power Query Editor is a preview of the data, no data is actually brought in untill you select “Close & Apply”. I can imagine that some transformation you are doing further downstream requires ‘more data’ for the preview and that is triggering the “source data” query…

You could limit the data you are bringing into the “source data” query during the design phase to improve the ‘user experience’ BUT don’t forget to remove that once you are all done…

I hope this is helpful

3

Hi Melissa,

Thank you.

I don’t think limiting the data brought into the source data is applicable to my use case. As I need all of the data in there, as there’s various columns with certain fields (that vary across all rows within all of the files) that all need to be captured within the design of the queries.

What I’m trying to move away from is the repeated “data load” of each query. As when you reference a query, I believe you’re referencing all the applied steps within that query - including the initial data load.

4

Hi @Callum173,

If filtering isn’t an option you could temporarily limit data using: Table.AlternateRows
Just a thought

5

Thank you, but I still don’t think this is quite what I’m looking for.

I’m looking to find a solution that appends the entire dataset once. Rather than multiple times when the source query is referenced.

6

Hi @Callum173,

I guess you have one option left. Add a buffer step by pressing the fx in front of the fomula bar, that returns the previous step name, enter the function like so: Table.Buffer( YourPrevStepNameHere)

You might want to read this.

https://social.technet.microsoft.com/Forums/en-US/34e454b5-3a18-4eef-b920-40703c93f390/tablebuffer-for-cashing-intermediate-query-results-or-how-workaround-unnecessary-queries-issue?forum=powerquery

I hope this is helpful

2 Likes
7

Hello @Callum173, just following up if the response from @Melissa help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

8

Hi @Callum173, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi Melissa,

Thanks for your help, apologies for the late reply.

Could you please clarify where I should add this Table.Buffer and how I’d use it in future queries?

For example:
Q1 = Source data that appends lots of files together
Q2 = I want to reference the output data from Q1, without appending the files again

Would I add the table.buffer on the very last step of Q1? Then would I add a Table.Buffer at the very first step of Q2 / as the source?

I also wonder whether using a Data Flow may be an alternative solution to my problem. What do you think?

10

Hi @Callum173,

Dataflows are great to offload some of the work especially if you need those tables more than once and/or in multiple models.

As for Buffering, you would buffer the result of Q1. Add a manual step to that Query by pressing the fx in front of the fomula bar, that returns the previous step name and wrap: Table.Buffer around it.

All the best.

1 Like
11

Hello @Callum173, just following up if the inquiry was solved?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

12

Hi Melissa,

Thank you.

So to confirm, I would add Table.Buffer as the last step in Q1?

How would I call it in Q2? As the first step?

13

Correct and you’d call the output of Q1 as needed.

14

Hi @Callum173 did the response above help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

15

Hi @Callum173, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.