Repeatedly appending rows from source file to a table without losing old data

[katiejroberts]

Hi there,

Apologies if the title doesn't spell it out clearly. I'm having trouble with appending rows to the bottom of a table.  I have an Excel file that contains 2017 survey data (call it file A).  I've imported file A into my pbix (this is still linked as a data source) and want to start tacking on new survey data to this table from another Excel file that will be refreshed weekly - file B.  File B will only contain the previous 7 days worth of data each week, and when it's refreshed weekly, last week's data file is lost.  I want Power BI to take file B, tack it on to the bottom of to file A, and then next week look at the refreshed file B, and tack that on to last week's data + all other historical data... appending new data to the bottom of our table each week - but not forgetting about or losing the older weeks' data.  I'm using "append queries" right now but when I refresh the data source, it looks at the newly refreshed file B which does not contain any trace of last week's data, and so refreshing just results in my 2017 data + the current week's data. 

How do I configure my pbix so I can look to the same data source file each week and just append those rows to the bottom of a running data table, never losing last week's appended data?   

I hope this is clear - any guidance here is sincerely appreciated!  Thanks!

[katiejroberts · Accepted solution]

I ended up finding a sort of solution for this issue.

I've adjusted my process so it saves a new weekly data file (only containing the last 7 days of data) in the same folder every week, and have my pbix connected to that folder.  So when a new file has been added to the folder, that is reflected in my .pbix.  

It's not perfect - the weekly export is coming from the internet and I get a OLE DB or ODBC Error: [DataFormat.Error] External table is not in the expected format... error when I refresh sometimes.  I suspect maybe there's an invisible file maybe coming in from my download from the internet?  I'm not sure.  If I get that error and then move all my files to a folder of the same name and delete the original folder, then refresh, it works without issue.  So that's a separate little problem.  But for now, this is sort of taking care of my issue. 

Thanks everyone. 

[v-yulgu-msft]

Hi @katiejroberts,

Currently, the Power Query will override the old data and only show the new data after refreshing. Incremental data load is not yet supported now. But it would be released in the feature as mentioned here.

As a workaround, if possible, you could store historical data in your source system where your data comes from. Or find a way to maintain all historical files locally. Each time you refresh data will generate a new file. Then, load data in each period from those files into desktop.

Best regards,

Yuliana Gu

[drewlewis15]

I had this same request a few months back and found the following article... It basically allows you to create a history table and append new data without losing the rows previously appended. 

https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-...

[katiejroberts]

Hi!  Thanks for this!  It sounds like what I'm looking for but looks a little complex for my reach - I'm a new-ish Power BI user - but I will give it a shot and report back!  Thanks...