Dealing with preformatted reporting files with data interpreter and pivoting columns to rows

Often, data in Microsoft Excel and text fields comes with a few rows that act as headers to describe the data in the file. Looking at the SIPRI-Milex-data-1949-2021.xlsx file we downloaded from the GitHub repository, we can see that the data table doesn’t start until row 6. The first four rows describe the dataset with a blank fifth row, as shown in Figure 7.22:

Figure 7.22 – Excel with header rows

Tableau is expecting the first row to be field names, one per column, and each of the other rows a record of data. Let’s open Tableau Desktop and connect to the file to see how we can bring it into a well-structured data model:

1. Open Tableau Desktop. From the Connect pane, select Microsoft Excel, locate SIPRI-Milex-data-1949-2021.xlsx, and click on Open.
2. Tableau will now switch the focus to the data source page. Double-click on the Share...