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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Using Power Query to pull exchanges rates of different dates

Hi everyone,

 

I have been looking for the solution in previous threads, but could not find one. I will try to explain the issues I am having and the solution I seek.

 

We have a dataset that contains multiple currencies (~25), usually with each line having different Date. Example below:

Annotation.png

 

My goal: using web sources command in power query to pull the exchange rate of specific currency for specific date. 

 

As for now, I am trying to use this website, but it get stuck in pulling out only the needed column - 

"www.xe.com/currencytables/?from=USD&date="&[Date]

 

Do you have any suggestions how to get the needed information, or is there a better way than the one I am trying to use?

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can add a custom column using following formula and expand it to meet your requirement:

 

 

let
    url="https://www.xe.com/currencytables/?from="&[Currency]&"&date="&Date.ToText([Date],"YYYY-MM-DD"),
    CurrencyTable = Web.Page(Web.Contents(url)){0}[Data],
    SelectTable = Table.SelectColumns(CurrencyTable,{"Currency code ▲▼","Units per "&[Currency]}),
    Ta = Table.RenameColumns(SelectTable,{{"Currency code ▲▼","To Currency Code"},{"Units per "&[Currency],"Currency Number"}})
in  
    Ta

 

1.PNG2.PNG3.PNG

 

All the Query is here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xCsAgDAXQu2QWYjQWHdtaXEvFSbz/NRrTFgrhD//xSe9AYICslWw1SzpLCYmQAgzTwWk1eV9fjpjQWVUvDauW7fzGjP5RliaqHu36tuTklMP8vKjXkv/OMMYN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Counter = _t, Amount = _t, Currency = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Counter", Int64.Type}, {"Amount", Int64.Type}, {"Currency", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CurrencyHistory", each let  
                                                url="https://www.xe.com/currencytables/?from=" & [Currency]&"&date="&Date.ToText([Date],"YYYY-MM-DD"),
                                                CurrencyTable = Web.Page(Web.Contents(url)){0}[Data],
                                                SelectTable = Table.SelectColumns(CurrencyTable,{"Currency code ▲▼","Units per "&[Currency]}),
                                                Ta = Table.RenameColumns(SelectTable,{{"Currency code ▲▼","To Currency Code"},{"Units per "&[Currency],"Currency Number"}})
                                             in  
                                                Ta),
    #"Expanded CurrencyHistory" = Table.ExpandTableColumn(#"Added Custom", "CurrencyHistory", {"To Currency Code", "Currency Number"}, {"CurrencyHistory.To Currency Code", "CurrencyHistory.Currency Number"})
in
    #"Expanded CurrencyHistory"


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
NoorAlfar
Frequent Visitor

@v-lid-msft Hi,

this is not going to work when publishing the report on the service cause it is a dynamic source, is there a solution for this?

v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can add a custom column using following formula and expand it to meet your requirement:

 

 

let
    url="https://www.xe.com/currencytables/?from="&[Currency]&"&date="&Date.ToText([Date],"YYYY-MM-DD"),
    CurrencyTable = Web.Page(Web.Contents(url)){0}[Data],
    SelectTable = Table.SelectColumns(CurrencyTable,{"Currency code ▲▼","Units per "&[Currency]}),
    Ta = Table.RenameColumns(SelectTable,{{"Currency code ▲▼","To Currency Code"},{"Units per "&[Currency],"Currency Number"}})
in  
    Ta

 

1.PNG2.PNG3.PNG

 

All the Query is here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xCsAgDAXQu2QWYjQWHdtaXEvFSbz/NRrTFgrhD//xSe9AYICslWw1SzpLCYmQAgzTwWk1eV9fjpjQWVUvDauW7fzGjP5RliaqHu36tuTklMP8vKjXkv/OMMYN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Counter = _t, Amount = _t, Currency = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Counter", Int64.Type}, {"Amount", Int64.Type}, {"Currency", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CurrencyHistory", each let  
                                                url="https://www.xe.com/currencytables/?from=" & [Currency]&"&date="&Date.ToText([Date],"YYYY-MM-DD"),
                                                CurrencyTable = Web.Page(Web.Contents(url)){0}[Data],
                                                SelectTable = Table.SelectColumns(CurrencyTable,{"Currency code ▲▼","Units per "&[Currency]}),
                                                Ta = Table.RenameColumns(SelectTable,{{"Currency code ▲▼","To Currency Code"},{"Units per "&[Currency],"Currency Number"}})
                                             in  
                                                Ta),
    #"Expanded CurrencyHistory" = Table.ExpandTableColumn(#"Added Custom", "CurrencyHistory", {"To Currency Code", "Currency Number"}, {"CurrencyHistory.To Currency Code", "CurrencyHistory.Currency Number"})
in
    #"Expanded CurrencyHistory"


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lid-msft,

 

Thanks for the advice. I did some changes, as it is needed to have currency rates from other currency to USD. Therefore, I eliminated the [Currency] part.

 

This works well, except the fact that it pulls all the data as of that date. Do you have any ideas how it would pull only the needed currency? For example, not the whole list, but only USD/EUR pair? For now, I am able to filter out, but if the logic would include only the needed currency, it would reduce the wait time when applying query changes.

Hi @Anonymous ,

 

Sorry for that, based on my test, the web you want to connect does not support the "to" parameter, so we can only use the "from" parameter to get the whole website and then filter the content you want. We suggest you to use some RESTful API to get currency rate if you want to get the required history currency rate.


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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