Make a part of power query (table) static

[Anonymous]

Hello,

I have a huge power query in excel which takes about 25 minutes to refresh. I add new data to the query every week and the previous data remains static. Is there a way to make the previous data static so that it refreshes just this week's data instead of refreshing the whole table?

Thank you. Any help would be appreciated.

[jbwtp]

Hi @Anonymous,

how do you add it (i.e. what is the source)? How do you know that the data is new (i.e. do you have any date filed in the output query to tell you which dates are already in the dataset)?

In a very general term the optimasation may sound like:

1. Get the existing table from your Excel file (I think, you may get away with just using Excel.CurrentWorkbook, i.e. Get Data From Range/Table, but if this does not work with further you may need to access it as if this would be an external file via Get Data From...->Excel File and so on).

2. Then it depends on many factors. Ideally, you would have only a weekly incremental data (e.g. scv file in a folder). In this case you can suck it in, proccess and combine with #1. Otherwise, it may be a tricky combinations of how to identify "new" data, so that "old" were not processed. E.g. using the latest date from #1 to filter source data at a very early stage before it goes into your cruching machine of heavy load steps.

If you can share more details, I amy be able to be more specific on suggestions.

[Anonymous]

So my source is a folder which has the excel files for weekly data for the past 12 months. It means that I would have one excel file for one week and this would be for every week in the past year. Every week I add a new excel file to the folder. 

Yes, I have dates on the excel files in the format of YYYY/MM/DD. 

Every week as I add the data, the data from the previous weeks remains static (that is, it does not change). But when I refresh the power query it refreshes every excel file in the past year. How do I make it refresh the data only for the most recent week (the lastest data) so that it does not take a long time to refresh?

Thank you once again for the all the help!

[jbwtp]

Hi @Anonymous,

Let's assume that your current query is called tMain and in general looks like the below:

let
    Source = Folder.Files(...),
    ...
    LastStep = ...
in 
    Last Step

The query returns data to Excel. You need to make it to load the data to a table:

In theory the data table would also be called tMain (same as the query). Now you can use it as a datasource. You will nee dot add a couple of lines to your query to modify it as the following (based on the code above, don't forget to add a comma to close the LastStep line):

let
    Source = Folder.Files(...),
    ...
    LastStep = ... ,
    ExistingData = Excel.CurrentWorkbook(){[Name="tMain"]}[Content],
    CombinedData = Table.Combine({ExistingData, LastStep})
in 
    CombinedData

Now you will need to only have the new file in the folder and move it out once it is exported to the table. When running the query will add it to the existing data store in the Excel table.

The downside of this approach may be a large size of the datatable and hence the Excel file. There are severa lstrategies to address this (if this is a problem). The simpliest are:

1. Store the data in a separate Ecxel file and have a simple query to extract the [already structured] data to the report/main file. In this case the intermediary file can even be set on shedule run in the Power BI Cloud. 

2. Store the data in the Power BI dataset and use "analyse data" functionality to get a connection from the PBI to Excel.

Cheers,

John