Forum Discussion

shep's avatar
shep
New Member
2 years ago

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?