• Amit Sarda

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.


Problem Statement


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.

Input data organized by level-1 header consisting of Provider, level-2 header consisting of Factor

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.


Challenges


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.


Data Transformation


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.

Level-1 and Level-2 column headers are combined into on single-level header

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.

Pivoted Columns as Field and Field Value

Step 5: Create Calculated Fields for Provider and Factor in Tableau.

Calculated Field for Provider

Calculated Field for Factor

Results


The resultant flat file is now usable for subsequent analyses using Tableau.

Flat File in Tableau

A few sample visualizations from this flat file are shown below.

Providers’ Average Ratings

Respondents’ Average Ratings

You can find more such samples of data visualization on my Tableau Public portfolio. For more such case studies, check out the blog archives.


#tricks #ETL #datatransformation #Tableau #tips #datavisualization #analytics #Excel

4 views

Recent Posts

See All
 
  • Twitter
  • Instagram

©2020 by Amit Sarda