Using the Data Interpreter and pivot

In this recipe, we will clean up the following spreadsheet on Canada international student permits and ready it for Tableau:

Getting ready

To follow this recipe, download the file from the Citizenship and Immigration Canada website using the following URL:

http://www.cic.gc.ca/opendata-donneesouvertes/data/IRCC_IS_0004_E.xls

How to do it...

Here are the steps to clean up the file:

1. Connect to the Excel file in this recipe. Make sure you choose Excel from the To a File section:

   

2. Check the checkbox beside Use Data Interpreter. Note that when this checkbox is checked, the label changes to Cleaned with Data Interpreter:

   

3. Select all fields except for Destination.

4. While the fields are selected, right-click and choose Pivot:

   

5. Right-click the new fields to rename them:

   • Change Pivot Field Names to Period

   • Change Pivot Field Values to International Students

   

6. Click on Add underneath Filters:

   

7. In the Select a field: option, choose Period:

   

8. In the filter window for Period, under the...