ABC of Power BI: E is for exchange rate
Have you ever needed to bring exchange rates into Power BI? Hugh Johnson describes a simple way to add historical exchange rates into your model and maintain these automatically into the future.
Let’s say that you have three companies, each operating in different a different country and with different base currencies: GBP, EUR and USD.
You would like to create a report that will display the performance of each company in GBP to make it easier to compare them. This leads to the following requirements:
- Convert the historical transactions, say from the beginning of 2019, based on the exchange rates on the date of the transaction
- Convert outstanding balances based on the current exchange rates
- Maintain this automatically into the future
Online data sources
There are many websites that provide exchange rates online, via an API (Application Program Interface). The thing is, that despite claiming “free” in the headlines, many of these services are in fact not free for my purposes. In the end, I settled on a service from exchangerate.host that is free, and provides a range of APIs, depending on your particular needs.
The documentation on the website was clear, with some sample calls that I could just cut and paste into Power BI to get started. The endpoint that I used was for “Time-Series data”. This returns a dataset of daily exchange rates between two dates for a basket of currencies against a defined base currency.
Perfect! Well almost. It was not in the documentation, but after a little experimentation I realised that the maximum date range I could specify was one year. Never mind, it simply means that for the full date range from the start of 2019 and into the future, I would need to create a separate query for each year.
The end result – exchange rates as a date attribute
The end result that I wanted was two additional columns in my date table that contain the exchange rate for that date for USD and EUR. Something like this:
For my purposes, two decimal places was fine. The exchangerate.host API returns the rates to six decimal places by default, but keeping this down to two will reduce the footprint of these two extra columns in the data model. The memory that a query column will take up in your dataset correlates closely with the number of unique values that it has. You can see in the example that reducing the decimal places to two, gives many dates with the same exchange rate.
The date of this article is 22nd October. Tomorrow, when I refresh my dataset, I want the model to include the rates for 23rd October.
Why add exchange rates to your date table?
My design choice was to add the exchange rates into the date table, rather than use a separate currency table. The latter would be perfectly valid and, perhaps, a more natural way to think about it.
The reason I added them to the date table is that the exchange rates are, effectively, attributes of that date (given that I am not going for daily, not hourly or monthly rates) with a one-to-one relationship between a date and an exchange rate.
Using additional columns in the Dates table rather than a separate ExchangeRates table could make the model slightly more efficient with one less table relationship.
I have created a short YouTube video that explains what I did, step by step.
The main points, however, are as follows:
In Query Editor, I created two separate queries; one for 2019 and one for 2020. For each, I specified the full calendar year. I could repeat this for as many years back and forward that I want. The idea of creating queries for future dates is that the model will automatically add in these future exchange rates as time goes by and you refresh the model.
The function that I used for these queries was the Power Query M function “Web.Contents”, that is the same function that Power BI uses if from Power BI Desktop you choose Get Data > Web. Creating these queries directly in Power Query just made it a little more interactive and easier for me to test different parameters with the API.
The full code that I used to create the 2019 exchange rates query was as follows:
Note in the first line, that starts “Source = “, I have specified parameters in the URL for the start-date, end_date, base and symbols. This is from the API documentation and is how I specify the date range, my base currency, and the other currencies that I want exchange rates for.
I append the 2019 query to the 2020 so that I have one query spanning both years
I set the date column in the exchange rates query to be a primary key (by removing duplicates) and then merge the exchange rates into the Dates table.
I disable load for the exchange rate queries, so that they don’t create unnecessary tables and data in my dataset.
What I would do differently next time
A subtlety that I didn’t spot when I did the original work and video is that the API has an optional parameter “places” to specify to how many decimal points I wanted my exchange rates returned. It would be better to specify this in the API call. Doing this would remove the need to include the Number.Round function in my queries, making them slightly more efficient.
Option: Use a relative date range for a dynamic exchange rates table
Generally when I create a Dates table, I create a dynamic table that is relative to today. You could do the same for your exchange rates query. You could change the start_date and end_date parameters in the API query to be dynamic. For example, if you wanted to retrieve the exchange rates for today and the previous 13 weeks (91 days), you could:
1. Set a DateToday value in your model with a new blank query “DateToday” = Date.From(DateTimeZone.UtcNow()) – note that I would normally have this query in my model anyway in order to give me a “Last Refresh Date”.
2. Create a new blank query called “End”:
This takes the DateToday query, converts the result into text using Text.From (that would for 22nd October 2020 return “22/10/2020”) then the Text.Range function reorders this to “2020-10-22”. For safety, the optional culture “en-GB” will force the date into a known format to start with.
Create a new blank query called “Start”:
This is identical to the “End” query, except that in place of DateToday we have Date.AddDays(DateToday,-92) to give an offset of 92 days.
4. Substitute the StartDate and EndDate queries into the URL start_date and end_date parameters in the API call that now becomes:
Applying these steps I can now create a dynamic query that returns a table of exchange rates for a rolling range of dates relative to today:
The full M-code for this query is as follows:
I have covered in this article two ways to pull historical exchange rates into your Power BI model from a public API. The example API is from exchangerate.host. The first method was for a static range of dates and the second for a dynamic, rolling number of dates that are relative to today.
If you round your exchange rates to the minimum number of decimal points, then you will save space in your model.
Since a set of daily exchange rates have a one-to-one mapping to your Dates table, if the date range of your exchange rates is substantially the same as the historical date range of your Dates table, you may want to consider just adding the exchange rates as date attributes in your Dates table, so simplifying your model and probably helping with performance too.
You can apply the general techniques laid out here to retrieving data from other public APIs. In the past, when working with accounting data, I have also used public APIs to retrieve public holidays, and countries / regional data.
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...