incremental refresh in query editor

Copper Contributor

I am using query editor in excel to pull raw data from multiple files within a folder that contains day to day data in it. I am experiencing a lengthy waiting period when refreshing the query, and each day the waiting time increases as it searching through more and more files.

 

Is there a setting that can be applied, or an approach to tell the query editor to only look for new files, as the previously loaded files will not change as they will be historical data. This way the refresh times will be fast as it will only be looking for a days or 2 days worth of data, rather than 3 months worth, or by the end of year, 12 months worth..

5 Replies

@cchiappazzo could you solve this? I have the same problem. Regards

@hwoe1 

I guess incremental refresh works only in Power BI Premium, not in Excel.

@cchiappazzo 

I was just researching this question. As you apparently know, there is a dedicated feature in PowerBI for incremental refresh.

.

There is not a similar feature in Excel PowerQuery.  But I did find a couple of articles that suggest manual work arounds

Incremental data loads in Microsoft Power Query for Excel (DIY incremental) 2015 05 15
https://www.oraylis.de/blog/incremental-data-loads-in-microsoft-power-query-for-excel

How to create a Load History or Load Log in Power Query or Power BI      2016 11 19
https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or...

@ron S. 

IMHO, that's more about incremental data appending, not about incremental refresh

In the interest of keeping the solution with the question in case those websites are no longer accessible, the basic premise of incremental updates in the Excel for Windows version of Power Query is as follows:

 

  1. Create your initial PQ with the transforms that you want and output to an Excel Table. 
  2. Create a second PQ referencing the Excel Table you just created, and set it as “Connection Only”. This preserves the existing data.
  3. Return to edit the PQ you created in Step 1 by adding a step to append the output of the second PQ. This runs the first query to get more recent data, then appends the existing data that you preserved in Step 2 onto that result for the finished output.
  4. Add steps to the first query after that to de-dupe and sort the results, if necessary. This will obviously require some kind of unique identifier on each record (a unique date/time, an ‘id’ field, whatever provides some unique value). You can always remove that unique id column after de-duping as a last step if you don’t want to see it in the final output.

If you are using Excel for Mac, while it now supports refreshing PQs and creating PQs using VBA, you’ll find it’s a whole lot easier to build a PQ with the Windows version of Excel. Good reason for even the most die-hard Apple fan-boy to keep a Windows laptop or PC around, even if it’s a cheap $200  netbook or a small VM, as long as it can run Office.