To acquire the necessary data for this project, we will be using two sources: the dollar buying exchange rate and the dollar selling exchange rate. These data can be found on the official website of the Central Bank of Costa Rica at the following URL: https://www.bccr.fi.cr/indicadores-economicos

To access the dataset for our exercise, we will navigate to the “Most consulted indicators / Exchange rates” section on the economic indicators page. This will lead us to the specific page containing the required data, which can be accessed at the following address: https://www.bccr.fi.cr/indicadores-economicos/tipos-de-cambio.

Once on the page, we will carefully select the options that correspond to our project’s needs. After opening the selected options, a new page will appear, and we will copy its URL to ensure accurate data retrieval. For your convenience, the addresses of the selected data sources are listed in the table below:

Continuing with the Data Transformation process, we will begin by accessing the “Power Query Editor” through the menu path “Home / Queries / Transform Data.” Upon opening the editor, the datasets previously extracted from the Central Bank of Costa Rica’s website will be displayed on the left side of the screen, each assigned a name by Power BI. We will proceed to rename them as “Buying Exchange Rate” and “Selling Exchange Rate” for clarity.

Next, we will initiate the data transformation for both queries by following a series of steps. The steps will list as they are executed as “Applied Steps” on the right side of the window. Let’s start with the data related to the buying exchange rate.

• Step 1: We will designate the first row as column headers. Since the first row contains information about the capture year, this step will allow us to name the columns accordingly, based on the corresponding years.
• Step 2: The currency exchange rate data is currently in text format after being extracted from the dataset. Our next task is to convert this data into numerical format.
• Step 3: To achieve the desired structure with two columns – the exchange rate date and the exchange rate value – we will pivot the data. By selecting the date column and choosing “Unpivot Other Columns” from the right-click menu, we will effectively reduce the columns representing years to just two: “Attribute” for the year and “Value” for the exchange rate.
• Step 4: We will merge the columns containing dates and years to create a single column with the complete date format “Day Month Year.”
• Step 5: As the original data comes from the Central Bank of Costa Rica, the month abbreviations are in Spanish. Our task here is to replace these Spanish abbreviations with their English counterparts. For instance, “Ene” will be replaced with “Jan.”
• Step 6: To maintain data consistency within Power BI, we will change the data type of the date column to “Date” format.
• Step 7: Since the decimal point was not included in the extracted data, we need to divide each data point by 100. This can be achieved using the “divide” arithmetic operation found under the menu “Transform / Number Column / Standard.”
• Step 8: We will rename the column headers to make them more descriptive. The date column will be named “Date,” while the exchange rate column will be titled “Currency-Buy” or “Currency-Sale,” depending on the context.
• Step 9: We will sort the table in ascending order (Date column), add an index column, it will be useful later with our DAX code. To add an index column can be done by selecting “Add Column / General / Index Column” from the menu.
• Step 10: Finally, to ensure a chronological order, we will sort the table in descending order, arranging the data from the most recent date to the least recent. This can be achieved by clicking on the “Date” column header and selecting “Sort Descending.”

By completing these steps, we have successfully transformed the data for the selling exchange rate. The same set of steps will be applied to the currency selling exchange rate data.