Using Power Query to pull exchanges rates of different dates

[Anonymous]

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:

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?

[NoorAlfar]

@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 · Accepted solution]

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

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,

[Anonymous]

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.

[v-lid-msft]

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,