Exchange Rate Update daily and Incremental

[fabiopaturzo]

Hi 

I have an API that every date upload the exchange rate without the date, i added the Data in lthe last colomn

Every day this row will be update.

I want to save in antoher table the History

Thank you

[BA_Pete]

Hi @fabiopaturzo ,

Power Query is not a data warehouse and is not designed to retain data beyond its immediate use.

The PQ refresh process works like this:

Wipe all data > Import available data from source > Perform transformations > Push to Data Model.

As such, there's no function to create and retain history/archives of changing source data.

The closest native function (within the Power BI universe) would be Incremental Refresh, which comes with its own significant caveats:

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Beyond this, you will need to arrange some kind of data warehousing or storage capability at your end to keep the history that you can then query with PQ.

Pete

[fabiopaturzo]

Can you do one example?

[rubayatyasmin]

sure. I did try this one with a sample example. looks like it is rewriting the row as the append table is updating, not adding new rows. But then I thought that it is supposed to work like that. 

So, you need to use a DB actually to store data on a daily basis. use SSIS or may be can use Azure logic app to push the data. It is suggested that you use DB for long term data storage. 

[fabiopaturzo]

Where is the example?

[rubayatyasmin]

This is the sample data I created as per your question. 

the table that will update the date regularly.

Destination table. assumed there are some historical saved previously some other way.

then loaded it into the Power Query.  As two tables structure is same, we do append, so I tries to use Append functionality but noticed that the value is also changing in the destination table as the value changes in the other table. Then I thought PQ is not designed to store information, you need to use a DB for long term data saving. 

[rubayatyasmin · Accepted solution]

Hi, @fabiopaturzo 

use append table in power qury. 

see the doc Append queries - Power Query | Microsoft Learn