Data Transformation using Excel and Tableau
Updated: Dec 21, 2019
Data transformation is fun! More so when you’re doing it using tools like Microsoft Excel and Tableau. While programming languages like Python and R facilitate this very easily, it’s not everybody’s cup of tea. Excel and Tableau are relatively easier to understand.
A client shared an Excel spreadsheet consisting of responses to a survey. The survey consisted of some 40+ Insurance providers, each evaluated on 5 factors: Responsiveness, Technology, Product, Partnership, In-Force Client Servicing. The data was organized as shown in the picture below, and for all practical purposes, is organized sub-optimally.
The client wanted to visualize/summarize the performance of each Provider based on all the responses captured. Secondly, he wanted to churn out as many charts as one possibly could using this data, which necessitated some data transformation.
As mentioned earlier, this layout of the data is sub-optimal. The Provider names are along the rows and suffixed with a number, one for each factor that the Provider is evaluated on. To be able to use this data to churn charts, it needs to be in a flat-file format, where there is just one level of columns. Essentially, this means bringing Provider names from rows to columns.
Step 1: In Excel, strip out the numbers at the end of the Provider name. A simple combination of LEFT and LEN functions in Excel does the trick for this step.
Step 2: Now, concatenate the texts for Providers and Factors into a single piece of text, separated by a hyphen (-). Ampersands (&) come in handy for quick tasks like this.
Step 3: Import this data in Tableau.
Step 4: In the Data Source tab in Tableau, select all the columns (except From) and Unpivot this data.
Step 5: Create Calculated Fields for Provider and Factor in Tableau.
The resultant flat file is now usable for subsequent analyses using Tableau.
A few sample visualizations from this flat file are shown below.