Power BI: Exchange Rates from APIs & Data Transformation w/ Power Query

In my previous Power BI Tips and Tricks - Vol.2 guide I showed how to import live exchange rates from the https://exchangerate.host/ website's APIs into a Power BI dataset for free.

However, the website has recently changed, and now requires a free registration to obtain an API key. Additionally, it is no longer possible to obtain a different base currency than USD using the free API key.

In this quick guide, I will show you how to continue using this website for free (as I do for my datasets), including a small Power Query hack that allows you to obtain the desired currency base (in this case EUR).

Step 1: Get an API key

Go to https://exchangerate.host/ and click on Get Free API Key.

Step 2: Register for a free account

Fill out the required fields to complete the registration process.

Step 3: Get your API key

Once you are registered, you will be redirected to the Dashboard page. Here, you can see your API key and monitor how many calls you have made in the current month.

From here you can also click on "Documentation" to go to the next page, where you can get all the pieces of information you need for correctly importing the live exchange rate.

Pro Tip: If you are using the free subscription of the exchange rate API, you should be aware that it has a limit of 1000 calls per month. This might be enough for some scenarios, but not for others. For example, if you have multiple Power BI datasets and reports that require live or historical exchange rates, or both, and you need to refresh them frequently, you might run out of calls soon. In that case, I suggest you follow these steps: (a) create a Power BI dataset where you import the exchange rates you need from the API, (b) apply any transformations you need to the data in that dataset, (c) publish the dataset to Power BI service and set it to refresh automatically as often as you need, (d) use the Get Data from Power BI Dataset option to connect all your other Power BI datasets to this one.

Step 4: Build the Base URL / Endpoint

In the documentation part you can find several instructions on how to build the code you need to use, depending on your needs.

The base URL + Endpoint for the Live exchange rates is:

http://api.exchangerate.host/live

You need to add your API Key parameter to the base URL:

?access_key={YOUR_API_KEY}

Note that you must use HTTP and not HTTPS, otherwise you will receive an error when connecting to the APIs through Power Query (HTTPS is available only in the premium subscriptions).

Step 5: Import the data into Power BI

In Power BI, go to Get Data > From Web > Advanced.

In the URL parts section, enter the base URL + Endpoint in the first box and the API key parameter in the second box.

Note that in each box there must be no blank spaces between characters, as it would make the connection not working.

Click OK to import the data.

The image below is what you should see if every previous step was successful:

Power Query worked in the background and already applied some steps, auto-generating several lines of M-code.

However, we need to perform additional transformations to (a) make the data easier to use, transforming the table from horizontal to vertical (b) include the EURUSD exchange rate.

Step 6 - Part (a): Make the data easier to use

Here I share the M-code I used so it will be easier for everyone to potentially just copy it in their own Advanced Editor.

I also added comments so every step's logic is explained.

// Beginning of Power Query AUTO-GENERATED code after initial import 
let
  Source = Json.Document(Web.Contents("http://api.exchangerate.host/live" & "?    access_key={YOUR_API_KEY}")),
   #"Converted to Table" = Table.FromRecords({Source}),
   #"Expanded quotes" = Table.ExpandRecordColumn(#"Converted to Table", "quotes", {"USDAED", "USDAFN", ... "quotes.USDZMW", "quotes.USDZWL"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded quotes",{{"success", type logical}, {"terms", type text}, {"privacy", type text}, {"timestamp", Int64.Type}, {"source", type text}, {"quotes.USDAED", type number}, {"quotes.USDAFN", type number}, ... {"quotes.USDZMW", type number}, {"quotes.USDZWL", type number}}),
// End of Power Query AUTO-GENERATED code after initial import

// From here there are some steps I added to re-organize the imported table in a more friendly way:

// Added the value 1 for the imported base currency USD (we will need it later for converting the USD into EURUSD Value
#"Added USDUSD Value = 1" = Table.AddColumn(#"Changed Type", "quotes.USDUSD", each 1),

// Unpivoted all the columns with values and changed their data type into decimal
   #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added USDUSD Value = 1", {"success", "terms", "privacy", "timestamp", "source"}, "Attribute", "Value"),
   #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type number}}),

// Removed some initial unnecessary columns to avoid noise
   #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"success", "terms", "privacy", "source"}),

// Changing the unfrendly "quotes.USDCURRENCY", leaving only the converted currency code
   #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","quotes.USD","",Replacer.ReplaceText,{"Attribute"}),

// Converted the time stamp code into date, removed the old timestamp column and reorganised columns in a friendly order
   #"Added Date column" = Table.AddColumn(#"Replaced Value", "Date", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp])),
   #"Changed Type2" = Table.TransformColumnTypes(#"Added Date column",{{"Date", type date}}),
   #"Removed Column timestamp" = Table.RemoveColumns(#"Changed Type2",{"timestamp"}),
   #"Reordered Columns" = Table.ReorderColumns(#"Removed Column timestamp",{"Date", "Attribute", "Value"}),
in
    #"Reordered Columns"

The resulting table should look like this:

Step 6 - Part (b): include the EURUSD exchange rate

The first step is to create a duplication of the FX table above (Power Query editor left side > right click on the FX_Live query > "Duplicate"), and filter it by attribute, to have the only attribute visible EUR.

The result you should see is:

Then, adding t the following lines of code, below those I previously shared, should do the trick:

// Merged the Exchange rate FX_USDEUR, so that each value for each USDCURRENCY exchange rate has besides it the USDEUR Value, in the next step we will see why 
   #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Date"}, FX_USDEUR, {"Date"}, "FX_USDEUR", JoinKind.LeftOuter),
   #"Expanded FX_USDEUR" = Table.ExpandTableColumn(#"Merged Queries", "FX_USDEUR", {"Value"}, {"FX_USDEUR.Value"}),

/*Created the column Value base=EUR dividing each Value of the exchange rate USDCURRENCY by the USDEUR Value, removed the redundant column FX_USDEUR.Value and we have now a table with dates, currency and values in both USD and EUR*/ 
   #"Added Values base=EUR" = Table.AddColumn(#"Expanded FX_USDEUR", "Value base=EUR", each [Value]/[FX_USDEUR.Value]),
   #"Removed Redundant Columns" = Table.RemoveColumns(#"Added Values base=EUR",{"FX_USDEUR.Value"}),
   #"Renamed Columns" = Table.RenameColumns(#"Removed Redundant Columns",{{"Value", "Value base=USD"}})
in
    #"Renamed Columns"

Now what you should see is the FX_Live table with Dates, Currencies and the Values of all the currencies for both 1 USD and 1 EUR.

I hope this content was useful and for any question feel free either to leave a comment below or to write me on Linkdln.