I’m going to show you in this tutorial how you can download exchange rates into your Power BI data model. This is perfect if you need to bring in the latest exchange rates for some evaluation work or for financial reports where you have to deal with sales from a variety of different regions around the world. You may watch the full video.
You want to grab the data from a source, in this case, a website and bring it into your model. From its rawest form, you’ll then want to perform some simple transformations and updates until it’s ready to be integrated into the data model. The downloaded exchange rates, in theory, should also automatically update as they’re updated on the website where they come from.
How To Do A Web Query
The first thing you need to do is query the web that has relatively wide exchange rate data. You go into the Query Editor whenever you need to review any data before bringing it into your model.
Then, you go to New Source and click on Web.
That will bring you to this pop-up box, where you place the web URL.
I download exchange rates from the website, XE. It’s one of the most well-known online resources for exchange or currency rates. Everything is going to be against the USD, as it should be, because that’s how currency rates are quoted in the market.
Once you click OK, it’s going to query the website and the most recent data (in this case exchange rates) in that website. Then, it’ll give you a few options when the Navigator box pops up. It’ll give you Live Currency Rates, but choose Table 0. You’ll then see the detailed preview, which shows you what you’re going to get.
As you can see, this is very useful wherever you may be in the world because it has every type of currency conversion.
Filtering The Currencies
Click on OK to import it into the Query Editor, and then you can change the table name. In this example, I named it Currency Rates.
Then, you’d probably want to filter it by the currencies that you’ll most likely use or that are most relevant to you.
What’s great about Power BI is that it will remember whatever filters you put in place.
To do this, click on the drop-down arrow of the Currency Code and click on your chosen currencies. Click OK and it will load up the filters.
This will give you a compact table, showing only the currencies you chose.
Making Changes and Updating The Currencies
Click on Advanced Editor and you’ll see the details. The Filtered Rows here will always be remembered by Power BI. These are the currencies or filters that you chose.
The Filtered Rows are going to hit the original source first, so if you want to modify it, this is where you can make the changes. It’s going to always give you the most recent data. If the rates will be updated on the website where you got them, they’re going to be updated in your model as well when you Refresh the query.
You can then use this table of currencies in your model. Remember to build your model correctly with the appropriate relationships. You can make this as a lookup table and connect it to your transaction table that might be in a variety of different currencies, for example. You can also integrate it into your calculations and get the home currency you might want to revalue.
Conclusion
This is a unique tutorial but one that’s well worth working through if this is the type of work that you do inside Power BI. I walked you through the process on how to download exchange rates from a well-known website. This gives you the ability to do that without any manual intervention.
Ref: https://blog.enterprisedna.co/how-to-download-latest-exchange-rates-into-your-power-bi-model/