Forum Discussion
Optimize Append Query of Two Other Query Results in Excel Power Query
I have the queries working but noticed that some of the performance is slow and it looks like it's because PowerQuery is rebuilding all of the intermediate steps when I append them rather than using the results from the previous query. This ends up just about doubling the refresh time because it is reprocessing everything.
Scenario, refresh all:
1. read multiple files from folder > Combine Query_Temp_A1 > process data > Final_A
2. read multiple files from folder > Combine Query_Temp_B1 > process data > Final_B
3. append query (sources Final_A and FinalB) > read multiple files from folder > Combine Query_Temp_A1 > process data > Final_A > rows from Final_A added to new result > read multiple files from folder > Combine Query_Temp_B1 > process data > Final_B > rows from Final_B added to new result > new result query completed
I tried creating a reference of Final_A and Final_B and appending those instead but it didn't change anything. I also tried creating a connection only to Final_A and Final_B but then refresh all doesn't execute the refreshes in the necessary order so the append completes before Final_A and Final_B are done.
Do you have any suggestions on how to improve this performance while still ensuring that the data loads in proper sequence?
2 Replies
- spinfuzerSolution Sage
Does this link help?
https://learn.microsoft.com/en-us/power-query/multiple-queries
It talks about why it occurs at the top then talks about how to isolate the multiple queries in the second half.
- shepNew Member
Thanks. There's some good info in there and it gave me some ideas to try.