Forum Discussion
Historize data from dataflow
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
Incremental refresh is the way to do that. What problem did you run into?
Pat
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
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
Pat
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.)?
