Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 con...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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,