Jul 05 2018
04:30 AM
- last edited on
Jul 12 2019
11:11 AM
by
TechCommunityAP
Jul 05 2018
04:30 AM
- last edited on
Jul 12 2019
11:11 AM
by
TechCommunityAP
In my excel file, I get my data, the current bitcoin price, using "get data from website".
That works fine, no problem.
When I "refresh" the data, I have the new value from the website.
My question is: Is it possible for create a field, and keep the value what I loaded? And every time I do "refresh data" a new field have the new value. So that I can see a history from all the "loaded" data values?
Jul 05 2018 06:55 AM
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.
Apr 02 2021 02:14 PM
Apr 03 2021 03:26 AM
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.