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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

6 thoughts on “Currency Exchange Rate Converter Power BI Function – Part 1

  • So far so good.

    But is there a way to refresh this in the Power BI Service without using a gateway (to skip the test connection)?

    In my experience – without a gateway – web credentials won’t be accepted because there is no way to provide the API key in the test connection.

    • Hi Frank
      This method doesn’t need gateway. You can just refresh it without it.
      It is using the Web.Contents method which doesn’t need gateway at all. Usually methods that uses Web.Page needs gateway.
      Cheers
      Reza

      • Reza,

        I’m not able to invoke your function (as provided) in a DATAFLOW. All I get is 400 error, even though I will get the exchange rate using the DataSourcePath (below) in the browser.

        DataSource.Error: Web.Contents failed to get contents from ‘https://free.currconv.com/api/v7/convert?q=NZD_USD&compact=ultra&apiKey=XXXXXXXXXXXXXXXXXX’ (400): Bad Request
        Details
        DataSourceKind = Web
        DataSourcePath = https://free.currconv.com/api/v7/convert
        Url = https://free.currconv.com/api/v7/convert?q=NZD_USD&compact=ultra&apiKey=XXXXXXXXXXXXXXXX

        Rewriting Web.Contents with [RelativePath = …, Query=[…]] my anonymous credentials will only be accepted, if I use a gateway.

        To repeat myself I’m talking about refreshing a dataflow, not a dataset or PBI Desktop.

        Is that really different for you?

        • Hi Frank,
          Yes, this is the scheduled refresh of the Dataflow I am talking about.
          The GUI for the scheduled refresh is very misleading in this case. when you go to The schedule refresh window, you have at least three sections: Gateway connection, Data source credentials, and scheduled refresh. In normal scenarios, you cannot get into the scheduled refresh without setting up either of the first two steps. In this case, however, (as I said the GUI is misleading), you just need to skip the first two, go to the scheduled refresh and set it up as you want.
          here is an image of my scheduled refresh result successfully:
          scheduled refresh working successfuly without a gateway

  • Reza,
    I’m sorry to bother you again.

    You write:
    “… and then call the function above with the code below;
    Table.AddColumn(Source, “Rate”, each GetExchangeRate([From],[To]))
    That’s it. You got the rates!”

    Well, I won’t get the rates after invoking the function (via Table.AddColumn) but a message box “Please specify how to connect.” If I click the button “Configure connection” and then choose to connect anonymously, I won’t get the rates but errors. BTW, you got this error in your video as well because you did not enter your Api key, but I did enter a WORKING Api key.

    If I just “Save and Close” and click “Refresh Now” and then reopen the refreshed dataflow I won’t get the rates but errors as well, although these are replaced by an automatic code change (“Replace errors” step after “Transform Column Types” step). BTW, this code change can be seen in your video as well when you show the stored – working – example (i.e. it will refresh, whether there are errors or not).

    After several hours I’m still wondering how you got this going. It really drives me nuts.
    I only can get refreshing rates using a gateway.
    Could you export and publish the json? Maybe importing it would give me some clues.

    • Hi Frank,
      when you create the dataflow and copy the script, there will be a message right at that time saying that configure credential. Not in the Scheduled refresh window, in the same place that you are editing Power Query scripts for dataflow. In there, you can set the connection to anonymous. and then the rest should be working. Have you seen such a thing?
      Cheers
      Reza

Leave a Reply