Forum Discussion
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,
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 AdvancedAnother 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
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-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?
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?
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
Hi DennisSchlein ,
Maybe you should refer to if it is help you
Working with Web Services in Power Query
Connect to data with API token
connect to a web api from power BI
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DennisSchlein , Are you trying Oauth, using the access key?
if yes, then refer to this
https://docs.microsoft.com/en-us/power-query/handlingauthentication
Hi amitchandak ,
I have not tried Oauth.
As I read the documentation for exchangeratesapi.io, i get (a static? API access key)
So I would think I just need to create the text string matching the url, addding dates from ym date table:So I think I just need to create the syntax for it to be like this:
S Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io/v1/"& Date &"?access_key=b5b562191a8c1fea37bcef334834395d"&"?base="&C&"")),
Or, do I completely misunderstand?
DennisSchlein , If I remember correctly, it might not like this in power query.
Try like what is shownn in this blog
If I do it on postman:
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 AdvancedAnother 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.
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 getLooks like it has switched back to anonymous auth and it needs to be Web API.
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!
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
ExpandedAllRatesToday 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
SourceQuick tip: use the Query option in Web.Contents instead of string concatenation — avoids the ? / & issues and works better with Power BI's privacy settings.
