Keep history from "data value" after "refresh data" from the data via "get data from website?

%3CLINGO-SUB%20id%3D%22lingo-sub-212361%22%20slang%3D%22en-US%22%3EKeep%20history%20from%20%22data%20value%22%20after%20%22refresh%20data%22%20from%20the%20data%20via%20%22get%20data%20from%20website%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212361%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20excel%20file%2C%20I%20get%20my%20data%2C%20the%20current%20bitcoin%20price%2C%20using%20%22get%20data%20from%20website%22.%3C%2FP%3E%3CP%3EThat%20works%20fine%2C%20no%20problem.%3C%2FP%3E%3CP%3EWhen%20I%20%22refresh%22%20the%20data%2C%20I%20have%20the%20new%20value%20from%20the%20website.%3C%2FP%3E%3CP%3EMy%20question%20is%3A%20Is%20it%20possible%20for%20create%20a%20field%2C%20and%20keep%20the%20value%20what%20I%20loaded%3F%20And%20every%20time%20I%20do%20%22refresh%20data%22%26nbsp%3B%20a%20new%20field%20have%20the%20new%20value.%20So%20that%20I%20can%20see%20a%20history%20from%20all%20the%20%22loaded%22%20data%20values%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-212361%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212437%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20history%20from%20%22data%20value%22%20after%20%22refresh%20data%22%20from%20the%20data%20via%20%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212437%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Eddy%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsually%20such%20site%20provide%20the%20interface%20to%20history%20data%2C%20please%20check%20-%20that%20will%20be%20the%20easiest%20way.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20not%2C%20the%20workaround%20could%20be%20-%20create%20manually%20the%20table%2C%20let%20say%20%22Source%22%20with%20dates%20and%20prices%20(even%20one%20will%20be%20enough)%2C%20query%20it%20and%20load%20result%20to%20another%20table%20(%22History%22).%20Make%20another%20connection%20only%20query%20to%20History%20table.%20And%20third%20query%20(%22Price%22)%20which%20takes%20current%20date%20and%20price%20from%20your%20web%20site%2C%20also%20connection%20only%20-%20that%20will%20be%20the%20table%20with%20one%20row.%20If%20the%20date%20is%20not%20returned%20by%20that%20query%20you%20may%20add%20it%20by%3C%2FP%3E%0A%3CPRE%3EDateTime.LocalNow()%3C%2FPRE%3E%0A%3CP%3EAll%20three%20shall%20have%20the%20same%20structure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20go%20back%20to%20Source%20query%20and%20append%20to%20it%20both%20History%20and%20Price.%20Select%20columns%20and%20Remove%20duplicates.%3C%2FP%3E%0A%3CP%3EThat's%20all%2C%20with%20each%20refresh%20your%20History%20table%20in%20Excel%20sheet%20will%20be%20updated%20with%20current%20date%20and%20price.%3C%2FP%3E%0A%3CP%3EMockup%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2252122%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20history%20from%20%22data%20value%22%20after%20%22refresh%20data%22%20from%20the%20data%20via%20%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1015759%22%20target%3D%22_blank%22%3E%40Engo92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20slightly%20depends%20on%20your%20source%20data%2C%20but%20let%20assume%20it%20is%20as%20the%20table%20on%20the%20left%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20284px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269307iC5E72ABB48DFC61D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Enamed%20Source.%20On%20first%20step%20we%20query%20it%2C%20add%20custom%20column%20with%20current%20date%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20587px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269308iE60DE5EABB3F307F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eassign%20proper%20data%20types%2C%20rename%20query%20as%20History%20and%20load%20it%20back%20to%20Excel%20sheet%20(green%20table%20on%20the%20right).%3C%2FP%3E%0A%3CP%3EAs%20next%20step%20query%20the%20History%2C%20we%20will%20have%20it%20as%20History%20(2)%2C%20keep%20it%20as%20connection%20only.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20226px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269309iB6A3A6B1C34CC8F7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20edit%20History%20(2).%20Steps%20are%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269310i85A860E2B163B352%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20we%20assign%20proper%20types%3C%2FP%3E%0A%3CP%3E-%20append%20History%20query%3C%2FP%3E%0A%3CP%3E-%20add%20Index%20column%20to%20fix%20table%20in%20memory%2C%20otherwise%20for%20large%20table%20we%20may%20receive%20wrong%20result%3C%2FP%3E%0A%3CP%3E-%20sort%20table%20(by%20dates%20from%20oldest%20to%20newest)%3C%2FP%3E%0A%3CP%3E-%20select%20Date%20and%20Value%20columns%20and%20Remove%20duplicates%3C%2FP%3E%0A%3CP%3E-%20remove%20Index%20column%2C%20we%20don't%20need%20it%20any%20more%3C%2FP%3E%0A%3CP%3ENow%20open%20History%20(2)%20in%20advanced%20editor%20and%20copy%20script%20between%20%3CSTRONG%3Elet%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Ein%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269311i19CE7513950620EC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClose%20it%2C%20open%20in%20Advanced%20editor%20History%20query%20and%20paste%20code%20before%20in%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20775px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269313iEEF2FE4D07CF26A0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EManually%20correct%20the%20code%3A%3C%2FP%3E%0A%3CP%3E-%20add%20comma%20at%20the%20end%20of%20%23%22Reordered%20Columns%22%20step%3C%2FP%3E%0A%3CP%3E-%20Rename%20second%20Source%20as%20SourceHistory%3C%2FP%3E%0A%3CP%3E-%20since%20we%20have%20two%20steps%20%23%22Changed%20Type%22%20rename%20second%20one%20to%26nbsp%3B%23%22Changed%20Type%2001%22%3C%2FP%3E%0A%3CP%3E-%20Return%20result%20of%20%23%22Removed%20Columns%22%20step%20(after%20%3CSTRONG%3Ein%3C%2FSTRONG%3E)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20785px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269314i3E044F29337383FB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESave%20the%20query%20and%20we%20may%20delete%20History%20(2)%20query.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?

3 Replies

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

@Engo92 

It slightly depends on your source data, but let assume it is as the table on the left

image.png

named Source. On first step we query it, add custom column with current date as

image.png

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. 

image.png

Now edit History (2). Steps are:

image.png

- 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

image.png

Close it, open in Advanced editor History query and paste code before in

image.png

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)

image.png

Save the query and we may delete History (2) query.