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
chtran
New Member

use power query to download daily exchange rates

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

5 REPLIES 5
trysodin
Frequent Visitor

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

Anonymous
Not applicable

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??????

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.