Given a web service that returns "current" values such as this:
... on 1/1/2023
| ProductID | Price |
| 1 | 5.00 |
| 2 | 17.00 |
... and later on 1/5/2023
| ProductID | Price |
| 1 | 6.50 |
| 2 | 17.00 |
Is it possible to append this data to a table keeping track of historical values, like this? :
| Date | ProductID | Price |
| 1/1/2023 | 1 | 5.00 |
| 1/1/2023 | 2 | 17.00 |
| 1/5/2023 | 1 | 6.50 |
The web service only ever returns current values, and I'm looking to merge the latest data in the data model on each refresh.
I wonder if a separate process is needed to query the web service and dump the response to a file (~1 per refresh/day), which Power Query would then read. This would allow rebuilding the historical table should the Power BI data source ever get deleted, though it isn't a strict requirement and I would rather have a simple solution contained within Power Query.
