Currency Exchange Rate Converter Power BI Function – Part 1

If you ever need to convert amounts from one currency to another, you face the challenge that currency rates are not constant. They change every day. You either need to keep an up-to-date table of all new currency rates and use that in your Power BI solution, or use a function that gets the rate from one of the live services. This post is about a function that gives you the latest currency rate using Power BI and Power Query.

CurrencyConverterAPI.Com website

There are many websites and services for currency exchange rate tables. CurrencyConverterAPI.com is one of the websites that offers you a FREE currency converter API. Yes, absolutely free, and the good news is that it works perfectly fine. You need to get a free API Key first;

After entering your email address, the API key will be emailed to you. Then it works with a URL such as below to give you the Exchange rate:

https://free.currconv.com/api/v7/convert?apiKey=do-not-use-this-key&q=USD_PHP&compact=ultra

Power Query Function

So I ended up using this URL and building a Power Query function that does the conversion using the the API. The function mainly just expand to the result value.

let
    Source = (#"From Currency" as text,#"To Currency" as text) as number=>
let
    Source = Json.Document(Web.Contents("https://free.currconv.com/",[RelativePath="api/v7/convert?q="&#"From Currency"&"_"&#"To Currency"&"&compact=ultra&apiKey=paste-your-api-key-here"])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value]
in
    Value
in
    Source

Remember that you have to paste your APIKey instead of the paste-your-api-key-here in the script above.

Creating the Function

You can create the function above in Power Query in Power BI Desktop, and then use it in other tables, However, I strongly recommend to refrain from doing so. Why? This is a free API, and has the limitation of 100 calls. If you call this function in a table with 10,000 rows, it means it will be 10,000 API calls, which you need to get a paid version of the API for that.

The method that I suggest, and it works for billions of records, is to use dataflows to create an exchange table. Then use the exchange table in merge with other tables. Using that approach, you just have one API call per combination of rates from and rates to parameters. Here is how you can do it:

Create The Function in Dataflow

Go to an organizational workspace, and create a dataflow;

Add New Entities, under Define Entities, and then choose Blank Query

Copy and paste the script from above in there;

And remember to set the paste your API Key in the script. Rename the script to GetExchangeRate

Call The Function in the Dataflow

You also need to have a table of all the currencies that you want to change (FROM and TO), like below:

In this table, you can insert a step:

and then call the function above with the code below;

Table.AddColumn(Source, "Rate", each GetExchangeRate([From],[To]))

Merging in the Desktop

That’s it. You got the rates! Now you can save the Dataflow, and then REFRESH it, to load data into it. This dataflow now can be used in Power BI Desktop as a source;

In the next part of this article, I’ll explain how you can use the currency table to do the currency conversion and get the result such as below:

Considerations

• The method explained here is using a free API, if you use the method and appreciate the work done for it, you can buy a coffee for the creator of the API.
• Because we used Dataflow in this process to materialize the data and store it in intermediate storage, as long as you are not converting more than 100 different currencies in your dataset, you should be fine, because there will be an API call only once per currency.
• You can use the Function directly in the Power BI Desktop, but that causes a lot of API calls, and not only it is not supported, but also, it makes the refresh time very long (even if it is supported).
• To make sure the rates are LIVE, you need to schedule the Dataflow and of course the Power BI dataset, to be refreshed regularly.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.