Forum Discussion

DennisSchlein's avatar
DennisSchlein
Helper III
5 years ago
Solved

Beginner - exchange rate API

Hi 

I'm brand new to PBI, and i'm trying to find a solution to making a exchange rate table.

I found a model in here where the API endpoint has been updated.

Im trying to reach this endpoint :
http://api.exchangeratesapi.io/v1/2013-03-16?access_key=b5b562191a8c1fea37bcef334834395d&symbols=USD,AUD,CAD,PLN,MXN&format=1

And I have this code:

= (Date as text) => let
C=currency,
Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io/v1/"& Date &"?access_key=b5b562191a8c1fea37bcef334834395d"&"?base="&C&"")),
rates = Source[rates],
#"Converted to Table" = Record.ToTable(rates),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Currency"}, {"Value", "Rate"}})
in
#"Renamed Columns"

 

 

But I get this error message:



Thanks in advance, 

 

Need Help
  • NickA01's avatar
    NickA01
    5 years ago

    DennisSchlein 
    We are all always learning;  
    if you just open Advanced editor  then you can replace your code with mine - remember to add in the key. 
    Get Data --> From Web -- Click on Advanced

    Another option to get data for multiple dates would be to use the timeseries mentioned in the documentation but this looks like it is dependent on the subscription you have. 

     

     

    Regarding getting data for different dates, as mentioned by amitchandak  Chris Webbs cross join blog will help. 

15 Replies

  • NickA01's avatar
    NickA01
    Resolver III
    5 years ago

    Just had a look at their site and under FAQ, it states 

    If you have a key, you should try the Web API auth

    This worked fine in my test; 

     

    Here's the query 
    let
    Source = Json.Document(Web.Contents("http://api.exchangeratesapi.io/v1/" & "2021-07-19?" & "access_key=<ENTER KEY HERE>" & "&symbols=USD,AUD,CAD,PLN,MXN&format=1")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded rates" = Table.ExpandRecordColumn(#"Converted to Table", "rates", {"USD", "AUD", "CAD", "PLN", "MXN"}, {"rates.USD", "rates.AUD", "rates.CAD", "rates.PLN", "rates.MXN"})
    in
    #"Expanded rates"


    By the way-  Not a good idea to share the API key- 

    • DennisSchlein's avatar
      DennisSchlein
      Helper III
      5 years ago

      Hi NickA01 , 

      I'm sorry, I must be the worst BI worker in the world.

      I cant even get to add json as data source: when trying to link to a URL i get some chrome error 😕

       

      would you mind sharing your model?

    • Shahebaz_Shaikh's avatar
      Shahebaz_Shaikh
      New Member
      2 years ago

      NickA01 Sir, which is your base currency? I am successfully able to run your above code. But your base currency in EUR as i notice is that right?

      and i want base currency in USD. How can I do that. Can you please help me with that?

       

      • NickA01's avatar
        NickA01
        Resolver III
        2 years ago

        Shahebaz_Shaikh 

        if you check the documentation page on the exchangeratesapi website, they advise how to set different base currencies 
        EURO is the default. 

         

        this should do what you need




  • NickA01's avatar
    NickA01
    Resolver III
    5 years ago

    DennisSchlein 
    We are all always learning;  
    if you just open Advanced editor  then you can replace your code with mine - remember to add in the key. 
    Get Data --> From Web -- Click on Advanced

    Another option to get data for multiple dates would be to use the timeseries mentioned in the documentation but this looks like it is dependent on the subscription you have. 

     

     

    Regarding getting data for different dates, as mentioned by amitchandak  Chris Webbs cross join blog will help. 

    • DennisSchlein's avatar
      DennisSchlein
      Helper III
      5 years ago

      Hi NickA01 ,

      This is SO close to working :'( 

      When I do as you do, and do a single call towards : http://api.exchangeratesapi.io/v1/ - it works.

      I have made a date table with all dates from 01-01-2015 -til today.

      So when I copy the string from your setup:

      = (Date as text) => let
      C=currency,
      Source = Json.Document(Web.Contents("http://api.exchangeratesapi.io/v1/" & Date & "access_key=b5b562191a8c1fea37bcef334834395d" & "&symbols=EUR,PLN,SEK,NOR,EUR")),
      rates = Source[rates],
      #"Converted to Table" = Record.ToTable(rates),
      #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Currency"}, {"Value", "Rate"}})
      in
      #"Renamed Columns"

      But change the static date to my date coloum, I get 

       






      • NickA01's avatar
        NickA01
        Resolver III
        5 years ago

        Looks like it has switched back to anonymous auth and it needs to be Web API. 

  • PaulusD's avatar
    PaulusD
    Resolver II
    2 years ago

    Hi all,

     

    I have worked with the timeseries option from APIlayer for around a year/ year and a half now without the apikey.

    Seems that this somehow changed recently and the report stopped getting data.

     

    But i can't figure out how to get the data into Pbi anymore.

    i registered on the site, have the API key and get the data as follows:

    I have multiple tables for different years, and append all to one table for as sort of master list.

     

    can anyone see what's wrong with the data source?

    In PBi I get this error:

     

     

    Thanks!

  • ewan89's avatar
    ewan89
    New Member
    3 months ago

    Hey, I know this is an older thread but it still comes up in search so just wanted to share what worked for me.

    I've used both AllRatesToday (allratestoday.com) and Exchange Rate API (exchange-rateapi.com) — both have free tiers, 160+ currencies, and mid-market rates from Reuters. No credit card
    needed.

    Here's the Power Query I use:

    let
    Source = Json.Document(
    Web.Contents(
    "https://exchange-rateapi.com/api/v1/rates",
    [
    Headers = [Authorization = "Bearer YOUR_API_KEY"],
    Query = [source = "USD", target = "EUR,GBP,JPY"]
    ]
    )
    ),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(ToTable, "Column1", {"source", "target", "rate", "time"})
    in
    Expanded

    AllRatesToday also has a similar endpoint if you want to try both and see which fits your workflow better:

    let
    Source = Json.Document(
    Web.Contents(
    "https://api.allratestoday.com/v1/latest",
    [Query = [base = "USD", symbols = "EUR,GBP,JPY"]]
    )
    )
    in
    Source

    Quick tip: use the Query option in Web.Contents instead of string concatenation — avoids the ? / & issues and works better with Power BI's privacy settings.