Forum Discussion

palunel's avatar
palunel
Helper I
9 years ago
Solved

How to programmatically set a dymanic URL

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

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"

7 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      palunel
      Helper I
      9 years ago

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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"