Is there a way to copy the current table to a backup table before importing new data from the web

[Pandadev]

Hi I am trying to find a way to look for changes in a text file I import from the web on a weekly basis. [every monday] the data is http://av-info.faa.gov/data/AirOperators/tab/operceo.txt

I was hoping that if scheduled the the refresh every monday , it would copy the current data to the table called previous and import the current data to the table called current. 

I then need to find a way to compare each table to identify any changes.

Is this possible in PowerBI

Or would I be wasting my time , trying to get this to work.

Any help on this would be very much appreciated.

[V-lianl-msft]

Hi @Pandadev ,

You can use dataflow to save historical data:

https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/ 

https://docs.microsoft.com/en-us/power-bi/service-dataflows-create-use 

Note: Incremental refresh of entities in Power BI dataflow is allowed for Premium and pro users.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanimesa]

@Pandadev One approach could be to first create an empty table with exactly the same structure (but an additional Date_Imported column at the end. Call it the archived table. 

Then, import the web CSV and add a Power Query step to add a column with Imported date. 

Something like:

= Table.AddColumn(#"Changed Type", "Date_Imported", each DateTime.LocalNow())

Then you can do an append of this newly imported table to the archived table. Thus on each refresh, the archive table will contain all old rows and the newly imported rows. 

The comparision logic will be on the archived table - you compare the rows from previous date with those from the current date.

I realize this is hardly a solution, but just throwing some ideas if they are of help. Good luck! 

[Pandadev]

Hi thanks for your idea. I have not used a power query before , so am struggling to work out how to put this in place.