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]

is that what you want?

s666-PQ-exchangerates via DV.xlsx

Microsoft Excel Workbook

1drv.ms

there is data validation list for dates
after change refresh green table (right click and select Refresh)

 

[smitpau]

Ah yes that's what I'm after thanks.

It wouldn't necessarily need data validation though as the cell dates would auto-updated (so that 31/08/2019 would be 30/09/2019).

Not sure if that would cause a problem.

PS it's quite cool, haha.

 

[smitpau]

I don't get why changing that data validation cell changes the table, if you don't mind explaining probably quicker than me trying to back work it.

 

[sandy666]

you can remove DV and enter dates manually or somehow but the table should stay there (or moved to another place via drag&drop)

I don't get why changing that data validation cell changes the table, if you don't mind explaining probably quicker than me trying to back work it.

MAGIC !
open Power Query Editor then Advanced Editor and you'll see why and how it works

 

[smitpau]

I've tried amending the table into the format I had in mind.

FX Attempt.xlsm

1drv.ms

If I try the code below it comes up with this error might be because it's trying to return multiple dates.

Expression.Error: The column 'Date' of the table wasn't found.
Details:
Date

Attempted code (bold=Change):

let
Source = Excel.CurrentWorkbook(){[Name="[B]FX_List[/B]"]}[Content],
Date = Table.TransformColumnTypes(Source,{{"Date", type date}}){0}[Date],
Json = Json.Document(Web.Contents("https://api.exchangeratesapi.io/"&Date.ToText(Date,"yyyy-MM-dd")&"?base=GBP&symbols=EUR,SGD,USD,AUD,JPY,CHF,CAD,CNY,HKD,NZD,TRY,INR,BRL,ZAR,PLN")),
rates = Json[rates],
C2T = Record.ToTable(rates)
in
C2T

It

 

[sandy666]

Power Query works with columns and content

btw. why you enter this format date: 2016/08/31 ?

 

[smitpau]

Ah okay I see yours is the other way round, it's because that was the way the API used the date formatting but I see the code adjusts for that anyway.

I'll change to that format and see what happens.

 

[sandy666]

if you want use all dates at once you need to parameterise PQ so maybe see this:
working-with-parameters-and-functions-in-power-query-excel

 

[smitpau]

Okay thanks I'll take a look