Forum Discussion
Keep history from "data value" after "refresh data" from the data via "get data from website?
Hi Eddy,
Usually such site provide the interface to history data, please check - that will be the easiest way.
If not, the workaround could be - create manually the table, let say "Source" with dates and prices (even one will be enough), query it and load result to another table ("History"). Make another connection only query to History table. And third query ("Price") which takes current date and price from your web site, also connection only - that will be the table with one row. If the date is not returned by that query you may add it by
DateTime.LocalNow()
All three shall have the same structure.
Now go back to Source query and append to it both History and Price. Select columns and Remove duplicates.
That's all, with each refresh your History table in Excel sheet will be updated with current date and price.
Mockup is attached.
- Please can you explain this more, it will be so much helpful if you do it by pictures
It slightly depends on your source data, but let assume it is as the table on the left
named Source. On first step we query it, add custom column with current date as
assign proper data types, rename query as History and load it back to Excel sheet (green table on the right).
As next step query the History, we will have it as History (2), keep it as connection only.
Now edit History (2). Steps are:
- we assign proper types
- append History query
- add Index column to fix table in memory, otherwise for large table we may receive wrong result
- sort table (by dates from oldest to newest)
- select Date and Value columns and Remove duplicates
- remove Index column, we don't need it any more
Now open History (2) in advanced editor and copy script between let and in
Close it, open in Advanced editor History query and paste code before in
Manually correct the code:
- add comma at the end of #"Reordered Columns" step
- Rename second Source as SourceHistory
- since we have two steps #"Changed Type" rename second one to #"Changed Type 01"
- Return result of #"Removed Columns" step (after in)
Save the query and we may delete History (2) query.
