Forum Discussion
Helper IIIHow to determine updated records on refresh
Hi Nickodemus,
How big are the CSV files? Could you keep all of them and import them each time? This way you could inject a Date column via Power Query to treat these as separete tables in the data model. Definitely ugly but would at least give you the ability to compare datasets.
Agree with Phil.
If your files are too large, you can use an R-script to create a transaction-table, by comparing the current version with the previous version and writing the differences into a separate csv-file (in append-mode).
That way you would only need to import 3 files with each update:
- the current one
- the previous one
- the transactions
How the script should look like: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
How to apply it directly without visual: http://zomalex.co.uk/using-r-in-power-bi-part-2-execute-a-r-step-in-a-query/
Memorable MemberPBI can store historical data. It must be set up in a 2 step process.
Your import/refresh does indeed over-write - make that your staging/import table.
Then using Query Editor set up an Append to your Master Table.
Although each record does not have a transaction date - as Phil suggests - you want to add a unique Import Date field using the current date to your import table also using Query Editor - and be sure that it is appended to the Master. Each record of the group will have this same date - each group imported will be a different date and in this way you have the historical groups of records.