Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
palunel
Helper I
Helper I

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

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)

View solution in original post

7 REPLIES 7
Phil_Seamark
Employee
Employee

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!

@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

 

 

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)

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

Yes, but only if you refresh the query.

Specializing in Power Query Formula Language (M)

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.

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.