How to programmatically set a dymanic URL

[palunel]

Hi there

I want to read the daily exchange rates from www.xe.com but I need to cchange teh URL to include the correct date.  Below is my code but this does not work:

let
    xDate = Text.From(DateTime.Date(DateTime.LocalNow())),
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=ZAR&date=" & xDate )),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per ZAR", type number}, {"ZAR per Unit", type number}})
in
    #"Changed Type"

 I do not get an error when the code executes but I do not get a table return.  Setting the source as: 

Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=ZAR&date=" & "2017-04-13" )),

does work however.

Help would be greaatly appreciated!

Thanks

Paul

[Phil_Seamark]

Hi @palunel

This worked for me

let
    xDate = Text.From(DateTime.Date(DateTime.LocalNow())),
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=ZAR&date=" & xDate )),
    Data = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data,{{"Currency", type text}, {"Rate", type number}, {"", type text}})
in
    #"Changed Type"

[palunel]

@Phil_Seamark, so that's odd. I get the same result as you but it returns a completely wrong table.  That we.com query should return this :

[MarcelBeug · Accepted solution]

You can use Date.ToText to get the date in the required format.

Additionally I wouldn't rely on Source{0}, but address the required element.

let
    xDate = Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyy-MM-dd"),
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=ZAR&date=" & xDate )),
    Data0 = Table.SelectRows(Source, each [Id] = "historicalRateTbl"){0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per ZAR", type number}, {"ZAR per Unit", type number}})
in
    #"Changed Type"

[Punitsetia]

Does the data in the table changes automatically everyday or not by using it this query in Importing data.???

[MarcelBeug]

Yes, but only if you refresh the query.

[Punitsetia]

Is there any way in which I can get the Live without refreshing the queries??

[MarcelBeug]

That doesn't make sense as the Source of your data is different every day, so how would you expect that data from another source would suddenly appear without refresh?

Maybe you can schedule a refresh; I have no experience with scheduled refresh, but here is more information.