cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
palunel Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: How to programmatically set a dymanic URL

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"
Specializing in Power Query Formula Language (M)
7 REPLIES 7
Phil_Seamark Super Contributor
Super Contributor

Re: How to programmatically set a dymanic URL

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"

fx.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

palunel Regular Visitor
Regular Visitor

Re: How to programmatically set a dymanic URL

@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 :

 

 

Capture.JPG

 

 

MarcelBeug Super Contributor
Super Contributor

Re: How to programmatically set a dymanic URL

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"
Specializing in Power Query Formula Language (M)
Punitsetia Frequent Visitor
Frequent Visitor

Re: How to programmatically set a dymanic URL

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

Re: How to programmatically set a dymanic URL

Yes, but only if you refresh the query.

Specializing in Power Query Formula Language (M)
Punitsetia Frequent Visitor
Frequent Visitor

Re: How to programmatically set a dymanic URL

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

MarcelBeug Super Contributor
Super Contributor

Re: How to programmatically set a dymanic URL

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.

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 214 members 2,397 guests
Please welcome our newest community members: