Power BI Tutorial: How to implement Currency Conversion Using Power Query and Power Pivot

Data for the tutorial can be obtained by downloading the following files:

    
Exchange Rate File

Working with Currency File

Step 1: Load Sales Data to Data Model

• Open workingwithcurrency.xlsx, highlight Sales Data table, pick PowerPivot from the ribbon and select Add to Data Model

Step 2: Using Power Query to transform Exchange Rate data

• Exchange rate file has unwanted rows on the top and bottom of the file and data needs to be un-pivoted
• Click on Power Query on the ribbon in workingwithcurrency.xlsx

• Click on “From File” and select “From Excel”
• Browse and select Exchange Rate file. This will load the file into the Navigator (right side panel)

• Right click on “EXCHANGE_RATE_REPORT” and select Edit to open Power Query editor

• Right click on the first cell and select “Filters -> Does not equal” to remove the first row
• Right click on the first cell and select “Filters -> Does not equal” to remove the next row
• Highlight the first row and select “Use First Row As Headers”. File should look as below

• Highlight Date column and change data type to Date
• Highlight Date column and click on Remove Errors. This will remove any rows that are not of data type Date
• Highlight Date column, click on Transform tab in the ribbon and select Unpivot Columns -> Unpivot Other Colums. This will unpivot all the currency columns
• Right click on Attribute column and rename it to Currency
• Right click on Value column and rename it to ExchangeRate
• Rename the query to Exchange Rate
• Close and Load to Data Model

Step 3: Using Power Query to create Date table

We will use the Sales Data to create Date table

• Click on the Sales Data sheet, highlight the data and from the ribbon select Power Query -> From Table. This opens Power Query editor
• In the editor rename the query to Date
• Right click on Revenue column and select Remove
• Highlight Date column and change data type to Date
• Highlight Date column and select Remove Duplicates
• From the ribbon select Add Column. A window will open, name column to Month and use formula = Date.Month([Date])
• From the ribbon select Add Column. A window will open, name column to Year and use formula = Date.Year([Date])
• Highlight Month and Year column and change data type to Whole Number
• Close and Load to Data Model

Step 4: Using Power Query to create Currency table

We will use Exchange Rate file to create Currency table.

• Select Exchange Rate file as the source for the Power Query
• Follow steps as before and until currency is unpivoted and attribute column is renamed to Currency
• Click on Currency column and select Remove duplicates
• Highlight Currency column, right click and select Remove Other Columns
• Rename query to Currency
• Close and Load to Data Model

Step 5: Create Measures using Power Pivot

• Open the Power Pivot model by clicking on PowerPivot -> Manage on the ribbon
• Create relationships as shown below
• Mark Date table as Date

  

• Open Sales table and create following measures

  Sales$:=SUM([Revenue])
  

  Sales$ EX:=IF(HASONEVALUE(Currency[Currency]), SUMX(ADDCOLUMNS(Sales, “EXRevenue”, [Revenue]*LOOKUPVALUE(‘Exchange Rate'[ExchangeRate], ‘Exchange Rate'[Date], Sales[Date], ‘Exchange Rate'[Currency], VALUES(Currency[Currency]))), [ExRevenue]), [Sales$])

• Format Sales$ as currency and Sales$ Ex as a decimal number

  Here is a pivot table report:

  

** Exchange rate file does not have data for weekends and holidays. Future post will explain how to handle this scenario