PowerPivot - Import data and append new data on a daily basis

[bolwahnn]

Hey Guys (and Gals),

I currently have a daily report that imports data (in table form) from a file that is placed into network folder every morning (I did this using Powerquery). So, everyday I go into my report, hit refresh, and the table is automatically updated from the file sitting on the network with the new data.

What I want to do now is import the data into PowerPivot, but append the new data (instead of overwrite the old data) when I hit refresh so that I can start reporting Daily, Weekly and Monthly (instead of just daily). Can someone please help in walking me through how I can make this happen???

Thanks!

 

[Matt Allington]

You can read my articles here https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

and here https://exceleratorbi.com.au/understanding-power-query-combine/

 

[bolwahnn]

You can read my articles here https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

and here https://exceleratorbi.com.au/understanding-power-query-combine/

Thanks for this info Matt, however, I'm not seeing exactly what I need.....I currently use PowerQuery to pull in a CSV report from a folder on the network. This report on the network is updated daily with new stats from the previous day - When I refresh my report (on a daily basis) the old data is overwritten with the new data on the network report. I'm looking for the ability to have the new data append to the old data each day I refresh (not overwrite the old data).

Is this possible?

 

[bkjohn2016]

I currently use PowerQuery to pull in a CSV report from a folder on the network. This report on the network is updated daily with new stats from the previous day

Where is this CSV report coming from? It sounds like somebody put together a script to place that report in that folder on a daily basis. If your PQ model is updating with updated data my guess is that whoever wrote that script is not creating/generating new files with every iteration of that script but rather overwriting the previous days file.

 

[bolwahnn]

That is correct, the previous days file is overwritten every day with a new file.

Any idea on how to append these files into the data model?

 

[bkjohn2016]

Power Query doesn't store or overwrite data it just brings the data into your model. It's the source file that's being overwritten. Email a copy of that CSV file to your IT department and let them know that you would like to change the production parameters to include the production date; so, "report.csv" will be "report 5-12-2018.csv" and the next day will be "report 5-13-2018.csv" and so on. And then ask if they can run a historical report with the items prior to the parameter change. The reports will begin to accumulate in that folder and you will need to refer to Matt's post to create a query that will bring in everything in that folder. Also, that's the perfect opportunity to request any additional changes that you'd like to see, so if you wish that the report had additional fields that's the time to ask.