Recently I was leading some Power BI sandbox co-development with a client whose business is based on buying and selling commodities with US and Canadian customers. As we explored ways to solve business problems with Power BI, we encountered a situation where their Canadian customers’ financial transactions are in Canadian dollars (CAD), but the client’s reporting was all in US dollars. The client wanted a dynamic currency conversion calculator built into their reporting to show all financials in US dollars. There wasn’t a dynamic currency table in the client’s SQL server. This was a request that I had not fielded before, but I was confident that Power BI could handle it. Below I outline how we solved this particular challenge in a few simple steps noted below. Learn more about how we can partner with you to elevate your Business Analytics. Or head on over to our Power BI resources library for more information.
Step 1: Linking a live currency table to my Power BI model
I linked to the currency table found at xe.com. In the Query Editor -> New Source Option, I chose Web and entered the URL.
The Navigator appeared with the tables that could be loaded from the xe.com website. I chose Table 0 because it contained the complete currency information.
After clicking OK, the currency table is loaded into my Power BI model. I also renamed the table and filtered out the currencies I didn’t need. If I ever need these currencies in the future, I can adjust the filter.
Step 2: Load in a data table
I also loaded in a transaction table with dummy data to supply values that can be converted from CAD to USD.
Step 3. Currency Conversion with a Calculated Column
I’m not a fan of the calculated column, but it is needed for this exercise. I built a calculated column (Values Converted to USD) that uses the IF function to identify values with a CAD label and converts the initial CAD value to USD using the currency table.
If there is anything that I’ve learned with Power BI, it’s that there are multiple ways to do anything. This was an interesting challenge and the first time I used the RELATED function in Power BI. If there is a unique challenge you have encountered in Power BI or Azure, schedule a no-cost 90-min architecture session and our architects will work through it with you. Otherwise, check out these 10 Tips on how to better train your company on Power BI.
Jon is a Microsoft Data Analytics Consultant at Beyond Impact.
With experience in cloud analytics solutions, Power BI, and personalized analytics training,
he is a expert in tackling challenging business problems with data.