Keeping some averages of certain columns while overriting the data file

[abehrmann]

I have a few reports built that all connect to one .xlsx file.  I recieved an updated copy of the report daily and overwrite the previos version.

There is a column in the table that goves the me duration of the request in that row and an associated status

Request #         Status             Days in Current Status

R1016	CLOSED_Cancelled	469.9541666
R1017	CLOSED_Cancelled	469.9375

If i created a new table in BI that averaged the days in current status for each status would i be able to keep the existing average and update it based on the new file data.

[edhans]

Yes. You just use the Get Data feature, and import the table from Excel. Next time you refresh it will load the new rows with the old ones. It should be a true Excel Table in Excel, not a range of cells.

[abehrmann]

im not sure i am following, I create a new table in excel and link it to the existing file, when the existing file is overwrited the table will update with existing and historic data?

[edhans]

No. Sorry. I thought the files were cumulative.

Then what you want is to NOT overwrite the files, but keep adding files. See this article. Now when you refresh, it will get ALL files in that folder. 

[abehrmann]

I have a gateway and scheduled refresh for that file so overwriting is a more efficient way to do this, if the report is sent to me multiple times a day but is cumulative it will get cumbersome very fast

Are there any alternitive solutions to this?

[edhans · Accepted solution]

The data has to be somewhere. Power BI refreshes by purging all data and getting it fresh when it imports. Even with incremental refreshes on large data sets, it ensures the data all exists and periodically does a full refresh.

So you need to figure out a way to keep the old data. Put it in SQL tables, do the file combine, etc. But whatever it is, if you want it in your report, it has to exist outside of Power BI to bring in.