Data preparation often uses a major part of the time it takes to execute an analytic project.
Preparation of data for analysis by Tableau consists in transferring data from a data source to a table in Excel and transforming it underways into a form that can be processed by Tableau.
Data in a source table may be in various forms. It is often possible to download the data directly into Excel but sometimes it must be downloaded in .csv or .tsv form for then to be transformed into .xlx or.xlxl form.
When the data is lodged in Excel it must be examined and transformed as necessary. This may be done manually or by using Tableau’s Excel Reshaper Plug-in or DataWrangler.
It may be possible to format the table manually if is not too large and complicated. This may be time consuming and error prone.
Manual preparation includes the following steps:
- Remove or exclude introductory and other unnecessary text
- Make sure each row contains only one piece of data
- Limit headers to a single row
- Fill blank cells
- Remove aggregated and descriptive data
- Delete blanks rows and duplicate headers
- If necessary transform a crosstable into a regular/column table by cutting and pasting
If the suggestions listed above have been followed and the data still are formatted in a way that doesn’t take full advantage of Tableau’s functionality, then the Tableau Add-In for Reshaping Data in Excel may be used to further format the data.
The Add-In is easy to download and install. Click here for instructions on how to download and install the tool and here to download it. The instructions on the Tableau page are comprehensive and there is no need for me to repeat them here.
Ken Black at 3danim8 has a very good post on the application of Reshaper. You can read it by clicking here.
If it not possible to reshape a datatable adequately manually or with the Reshaper then it may be possible to do so with DataWrangler.
This page on TableauPublic has a good example of the use of DataWrangler to reshape data into a form that Tableau can process.