Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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"
@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 :
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"
Yes, but only if you refresh the query.
Is there any way in which I can get the Live without refreshing the queries??
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |