Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm using power query to download daily exchange rates from web source www.xe.com/currencytables. However, I don't know how to make the date dynamic so when I refresh the query the next day, the query will know to update the date accordingly.
For example, the URL web source is http://www.xe.com/currencytables/?from=USD&date=2017-03-07.
How do I change the following syntax to make the date not static at 2017-03-07 so that when I refresh it the next day the formula will change to 2017-03-08?
let
Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date=2017-03-07")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
in
#"Changed Type"
Thank you very much.
Caroline
Hi all,
You can do this by simply changing the link to http://www.xe.com/currencytables/?from=USD the website then defaults to the current date.
Its always teh simple answer!
Cheers,
Trys
Dear all,
May I know if I can substitute the Datetoday to change it to point to a cell value in excel to make the date variable instead of today date?
Thanks!
Best regards,
ESther
Dear all,
May I know if I can substitute the "Datetoday" to change it to point to a cell value in excel to make the date variable instead of today's date?
Thanks!
Best regards,
ESther
Hi Caroline,
Just in case you don't have the solution yet, here you go. I assigned the current date to DateToday variable and replaced the hard-coded date in your "Source" line, with the neccesary formating.
let
DateToday = DateTime.Date(DateTime.LocalNow()),
Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&"&Date.ToText(DateToday, "yyyy-MM-dd"))),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
in
#"Changed Type"
Best regards,
Ferdinand
Does it work Dynamically??????
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |