Update Data from Web

[smitpau]

Hi,

I'd like to be able to update the data from the web based on a cell URL link.

The reason for this is if you look at the example below there is a specific date, this date will change over time (as only require a certain amount of history, say 1 year).

So ideally the web lookup would be dynamic but I can only seem to put a fixed URL into the from Web data source.

Hope that explains the issue clearly, any help appreciated, thanks for reading!


Example URL:
Original - https://api.exchangeratesapi.io/201...D,JPY,CHF,CAD,CNY,HKD,NZD,TRY,INR,BRL,ZAR,PLN
Updated (based on cell) - https://api.exchangeratesapi.io/201...D,JPY,CHF,CAD,CNY,HKD,NZD,TRY,INR,BRL,ZAR,PLN

 

[sandy666]

You are welcome & thanks for the feedback

 

[sandy666]

here is
exchange rates with parameter

for more

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[sandy666]

file from post#12 is updated with table from vertical to horizontal

Name	31/08/2016	30/09/2016	31/10/2016	30/11/2016	31/12/2016	31/01/2017	28/02/2017	31/03/2017	30/04/2017	31/05/2017	30/06/2017	31/07/2017	31/08/2017	30/09/2017	31/10/2017	30/11/2017	31/12/2017	31/01/2018	28/02/2018	31/03/2018	30/04/2018	31/05/2018	30/06/2018	31/07/2018	31/08/2018	30/09/2018	31/10/2018	30/11/2018	31/12/2018	31/01/2019	28/02/2019	31/03/2019	30/04/2019	31/05/2019	30/06/2019	31/07/2019	31/08/2019	30/09/2019	31/10/2019	30/11/2019	31/12/2019	31/01/2020	29/02/2020	31/03/2020	30/04/2020	31/05/2020
AUD	1.745867038	1.702263568	1.598778456	1.676363636	1.704781705	1.64891702	1.61854522	1.634308557	1.731795959	1.723115664	1.688899503	1.645381347	1.632653061	1.709610107	1.731301151	1.779735182	1.729652965	1.746900239	1.768591302	1.832895188	1.820486585	1.757983577	1.781727894	1.771351715	1.797303321	1.808632931	1.797171244	1.747541205	1.813242709	1.802621663	1.862643444	1.843294885	1.844796401	1.819309303	1.811834254	1.76538642	1.810633247	1.820645118	1.873497963	1.90413611	1.879995299	1.95948916	1.977964016	2.026894397	1.909901617	1.851633958
BRL	4.246869325	4.205428382	3.86851749	4.236715543	4.006750917	3.894663492	3.846198933	3.950767361	4.109952292	4.1761575	4.275982851	4.111384478	4.06749807	4.268071401	4.332122978	4.39484003	4.477869324	4.478216358	4.480574563	4.679163333	4.767166894	4.975593066	5.064725467	4.911230666	5.4146423	5.244562155	4.719431098	4.922418826	4.967972008	4.800406495	4.973262655	5.11068391	5.107364809	5.013022448	4.853159333	4.603647556	5.065864296	5.113070575	5.168054056	5.451334702	5.307592854	5.602257202	5.770614781	6.430400596	6.738967838	6.621747625
CAD	1.719571728	1.706096187	1.6285397	1.669560117	1.657128174	1.632425527	1.639294297	1.667387467	1.76553455	1.729296629	1.681393789	1.635540148	1.627651593	1.665608202	1.707852891	1.735182133	1.695050889	1.739278808	1.765311316	1.816779061	1.766939518	1.715100365	1.742791039	1.714637974	1.692890573	1.697734701	1.671598798	1.697130283	1.744491521	1.72520496	1.752431992	1.747640685	1.747866617	1.704193116	1.661145502	1.600253212	1.618506045	1.628713039	1.703528264	1.71475506	1.715796897	1.737689338	1.729707554	1.761786041	1.734882918	1.69611935
CHF	1.292007641	1.263138334	1.201554692	1.267214076	1.254292322	1.238952442	1.24822695	1.250219162	1.28218484	1.247181366	1.242991823	1.270297473	1.244495667	1.299303681	1.322891649	1.329658465	1.318936465	1.323057673	1.302946333	1.346325294	1.360618463	1.31455292	1.305682524	1.299260256	1.257075997	1.275329652	1.282616768	1.273184533	1.259767699	1.302724429	1.320556882	1.302691367	1.326059735	1.264361336	1.238636997	1.205046768	1.204549219	1.224639563	1.277907422	1.290466412	1.27574048	1.27044847	1.244095411	1.194115723	1.214889822	1.189947607
CNY	8.762469637	8.648130727	8.234980566	8.587096774	8.549837651	8.590674177	8.531739054	8.607763609	8.922022421	8.750529388	8.800450343	8.823082085	8.487164711	8.90630316	8.784788226	8.907995681	8.796366219	8.911386645	8.74116383	8.854497657	8.705547976	8.548243613	8.709440777	8.986550101	8.877200802	8.978023217	8.881775117	8.858063502	8.803617542	8.793304255	8.890196307	8.784457649	8.758579909	8.686705828	8.720651386	8.375953636	8.712858168	8.781908708	9.118456341	9.055089469	9.191937001	9.107692308	8.985758659	8.774973771	8.821701858	8.81982062
EUR	1.179161852	1.161399719	1.11049417	1.173020528	1.167978696	1.161372743	1.172264228	1.168866083	1.183810212	1.144623133	1.137229482	1.11831805	1.087275614	1.134069723	1.138265056	1.136557368	1.127103457	1.137527016	1.131029803	1.14298777	1.1368804	1.140510949	1.128604481	1.120824927	1.114330288	1.127014539	1.12520113	1.122737684	1.117905492	1.141839275	1.165025922	1.16509379	1.159447176	1.127484694	1.11538676	1.091429008	1.104179319	1.129012227	1.160995205	1.173364623	1.175364363	1.188001188	1.172126824	1.128120664	1.150681779	1.110025753
HKD	10.182888	10.05156615	9.42665186	9.676129032	9.548342638	9.690029615	9.642107731	9.710238098	10.06286032	10.00640989	10.12907555	10.24356967	10.06012634	10.45771054	10.33191809	10.51588339	10.5632136	11.08099192	10.8120794	11.05223454	10.77773988	10.47194343	10.32311946	10.32436673	10.19088478	10.20838499	9.99032327	9.975636592	10.0248175	10.29219667	10.44014679	10.27554468	10.20394676	9.860642892	9.911995985	9.526974668	9.556561586	9.638151581	10.14721419	10.08553828	10.28126469	10.1981586	10.02754498	9.582820979	9.700247397	9.584739366
INR	87.91382685	86.3680708	81.17046086	85.46510264	83.61968278	84.54851635	82.79585018	81.11521513	83.17628118	82.79516969	83.8644195	84.17669425	82.19749274	87.40161945	85.77510159	86.81877593	86.34232386	90.06938915	90.05598598	91.77734598	91.14199636	89.8745438	90.07730941	90.16420085	92.18241587	94.57455201	94.21365319	88.78778012	89.13038132	93.27228299	94.24069436	90.54992427	90.5081857	87.65178763	87.58462997	83.70878491	87.04963286	87.11627697	91.84923316	92.32912878	94.24894217	93.73982774	92.93207525	93.51951085	93.90806053	93.35594086
JPY	135.6154046	131.3426942	127.6735147	141.3255132	144.1285711	141.6177922	139.3001583	139.7379402	144.1407314	142.3911177	145.2810663	145.045851	142.226523	150.6271406	150.2509874	151.2530545	152.1702377	154.2486634	147.8482158	149.902846	150.2046385	145.2212591	145.6351222	146.6487335	143.8043236	147.8981179	144.1945248	144.8219338	140.6884062	142.5129599	147.3058776	144.9959222	144.8497356	136.7300689	136.7464168	132.1065671	129.4981505	132.7605478	140.1669511	141.3083016	143.3239304	142.975943	139.9050577	134.1335469	133.3294977	132.414972
NZD	1.810367191	1.784955228	1.700499722	1.749090909	1.770422108	1.708263167	1.724049001	1.789417087	1.881903093	1.807245464	1.768846736	1.752628047	1.801724419	1.854657624	1.935619728	1.968403705	1.899169325	1.911386645	1.912005881	1.954280489	1.949181446	1.902486314	1.946504148	1.929275947	1.956652552	1.97283895	1.949073397	1.858804509	1.906699608	1.896252484	1.943845751	1.922404754	1.948450979	1.931832275	1.891695946	1.842659594	1.931209628	1.961658745	2.015371577	2.005280141	1.957334274	2.02946243	2.063880912	2.077659827	2.03728209	1.982839002
PLN	5.133245289	5.016317666	4.805996669	5.217947214	5.151136443	5.021659602	5.058085693	4.9402125	4.998520237	4.774452012	4.805818066	4.752068888	4.629837017	4.8812629	4.830796899	4.768426436	4.707911139	4.721078376	4.725555618	4.812664305	4.804911323	4.910812044	4.935613114	4.793768213	4.781925563	4.820691987	4.882472742	4.816544663	4.808558684	4.879764324	5.019980195	5.010602354	4.970318152	4.830482676	4.739947577	4.683540159	4.837630431	4.943041333	4.943633683	5.067175125	5.00329102	5.10947431	5.070503429	5.133625893	5.216730913	4.939059586
SGD	1.790085607	1.769392472	1.693614659	1.780527859	1.779298746	1.765402706	1.738585077	1.746285928	1.806257621	1.776226178	1.786587516	1.780138671	1.749861372	1.818027172	1.80528838	1.816900608	1.806070579	1.852804004	1.827970367	1.846839639	1.820827649	1.785583942	1.794029682	1.790629904	1.779028304	1.785078328	1.766115693	1.749337585	1.742926453	1.765169335	1.792974894	1.772573692	1.769664224	1.733845963	1.717137918	1.665629809	1.690719373	1.700292414	1.761694124	1.762041654	1.776093089	1.792931393	1.795346657	1.763591034	1.763304758	1.744072463
TRY	3.883097894	3.899515696	3.771793448	4.266510264	4.329930622	4.718889728	4.500439599	4.546187743	4.587974856	4.54232244	4.564156801	4.62021919	4.464679852	4.764567126	5.027033795	5.300676252	5.124263156	5.315891252	5.253746536	5.597896903	5.558890405	6.002281022	6.02505502	6.4244564	8.511923334	7.849656261	7.004939633	6.596420712	6.773165797	6.815524447	7.069260791	7.39205406	7.758208886	7.3590926	7.323071775	6.724294118	7.112902335	6.942409086	7.397513148	7.415429745	7.856488011	7.854707455	8.011252418	8.129575939	8.742765088	8.44740698
USD	1.312642973	1.296238226	1.215546918	1.247507331	1.231166344	1.249056385	1.242248403	1.249634729	1.293904561	1.284381617	1.297806284	1.311451577	1.285703413	1.338882715	1.324712873	1.346706825	1.351735176	1.417017404	1.381439801	1.408275232	1.373237835	1.334283759	1.315727103	1.315400135	1.298306218	1.30463203	1.273502639	1.275317735	1.280001789	1.311744959	1.329993592	1.308982873	1.300667842	1.257258183	1.269310133	1.217052487	1.218572296	1.229381414	1.294974052	1.288589029	1.320404325	1.312978913	1.286643615	1.235968999	1.251481503	1.236124678
ZAR	19.07070254	18.02933696	16.48883953	17.46568915	16.88546801	16.77486789	16.16669597	16.64512057	17.15329158	16.86876896	16.96746386	17.19033773	16.8058017	18.08160766	18.70647559	18.32846508	16.68721752	16.81492435	16.2502969	16.71162419	17.06696226	16.76904653	18.11229615	17.22438915	19.13617116	18.53341598	18.89685281	17.5436745	18.40005366	17.40391423	18.5797169	18.94931842	18.57712643	18.47203274	17.98204227	17.31377493	18.58322752	18.69373285	19.63475091	18.91428571	18.54407616	19.59013959	20.0387974	22.12188216	22.61918187	21.56102922

 

[smitpau]

**** wow thanks you've gone above and beyond.

 

[sandy666]

You are welcome
hope it will work for you
have a nice day