Beginner - exchange rate API

[DennisSchlein]

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...

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, 

[PaulusD]

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!

[v-stephen-msft]

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.

[NickA01 · Accepted solution]

@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]

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]

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

[NickA01]

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- 

[Shahebaz_Shaikh]

@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]

@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

[Shahebaz_Shaikh]

Hello @NickA01 sir I applied same Step and Still Base currency is EUR.

[DennisSchlein]

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?

[DennisSchlein]

If I do it on postman:

[amitchandak]

@DennisSchlein , Are you trying Oauth, using the access key?

if yes, then refer to this

https://docs.microsoft.com/en-us/power-query/handlingauthentication

[DennisSchlein]

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?

[amitchandak]

@DennisSchlein , If I remember correctly, it might not like this in power query. 

Try like what is shownn in this blog

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...