Historize data from dataflow

[FabiNeed]

Hi all,

I have a dataflow which gets data via an API on a daily basis. Naturally, the dataflow always contains the most up to date information. However, for my use case, I would like to show the development of some values over time. Therefore, in addition to the most recent data from the last update, I also need the data from the previous updates. Hence, I need to store the data coming from the dataflow somewhere on a daily basis. How can I achieve this? 

I have tried it with incremental refresh as well as datamarts, but had no luck so far.

Every idea is highly appreciated. 🙂

[ppm1]

Incremental refresh is the way to do that. What problem did you run into?

Pat

[FabiNeed]

Thanks for your reply, @ppm1 . Then I probably have a misunderstanding in how incremental refresh works.

I get the following data via the API in a dataflow:

"DateTime" column refers to the date and time of the last refresh of the data in the dataflow.

Besides "DateTime", the only column whose values change over time is "RiskScore". What I want to achieve is the following:

If for "Company" A, the RiskScore is "40" on 15.06.2023 and changes to "50" on 16.06.2023, I want to add a new row with this new information. However, also when the RiskScore remains "40" on the 16.06.2023, I would like to add a new row with this information.

What would be the correct setting in incremental refresh? I tried it with the following, which did not result in the desired behavior:

Best,

Fabi

[ppm1]

Please see these two examples of potential approaches to do this. Hopefully, your API support date filtering/folding and it will be easy. Otherwise, you'll have to use the RangeStart and RangeEnd parameters to create a table of dates and then add the API call on each role, concatenating the date formatted as text into the URL filter.

Power BI incremental data refresh with JSON REST API - BecTec Solutions

Power BI Incremental Refresh with Web API Data Source | Data and Analytics with Dustin Ryan (sqldust...

Pat

[FabiNeed]

Thanks for the suggestions @ppm1 !

Unfortunately, the API does not allow to get data from the past (e.g. to call data from last month). It only delivers the current real-time data from the system. Is incremental refresh also in this case the way to go to build a database with historic data (data from yester, day before yester, etc.)?

[ppm1 · Accepted solution]

That is odd that you can't get past data. In that case, I would use Power Automate to call the API every day and write the data to a csv (with the date in the filename). You can then use incremental refresh to combine those csv files (not required, but it will speed it up significantly once you get a lot of them).

Pat

[FabiNeed]

Thanks @ppm1, very good idea I haven't though of. I am going to try this.

Best,

Fabi