Dear Data-Agony Aunt: Missing Headers, Case of Date (Format) Rage

During #SpringDataCleaning month our resident Data-Agony Aunt will be on-site to answer your data-prep questions.

Ms. Data-Agony Aunt

Want some advice on how to clean your data?
Tweet your question to #SpringDataCleaning

Dear Data-Agony Aunt: Where Have My Headers Gone?

#springdatacleaning this used to baffle me why the first NULL line and where are my headers? https://t.co/Y1uFeGE8fk pic.twitter.com/5iv8cZbLkK

— David Pires (@davidmpires) May 9, 2016

Data-Agony Aunt replies: Don’t fret, David. This looks like a case for Tableau’s Data Interpreter!

The Issue

You’ll notice that your data set has a “header line” in it containing (in you case) a second set of headers for all the rows: Years and Sectors of Economic Activity. Lots of data sets have header or footer rows that contain extra information about the data set such as a title or information about when the data set was generated.

David’s data with a header row

Tableau needs the first row or a data set to contain the column names and for the actual data to start from the second row downwards. Header rows mess up this structure. Thankfully, it’s an easy fix in Tableau’s Data Interpreter.

The Solution

When you bring your data, you’ll notice that the first row contains a lot of nulls. This is Tableau trying to see the non-existent data in these rows. To tell Tableau that your data set has header rows, and that it should look for data further down, click on the “Turn On” button in the Data Interpreter.

Data Interpreter in action

Hey presto! Tableau has tidied up your header rows and is reading your data from where it actually starts (the header rows haven’t disappeared, you can still see the information in them by clicking on the “Review results” button).

Dear Data-Agony Aunt: I’m Experiencing Date (Format) Rage!

Data-Agony Aunt: Why does Tableau recognise my date as a number? #springdatacleaning pic.twitter.com/IF92tD5GDY

— Rory (@Rory_Heath) May 13, 2016

Data-Agony Aunt replies: I feel your pain! Dates formats can be tricky. Follow these steps to turn date-format-rage into a blissful time.

The Issue

Currently Tableau doesn’t recognise dates formatted as

• Month_/Year: MMM-YY(eg Jan-16)
• FY_Year: YYYY (eg ‘2016)

If your date is formatted like that (as yours is), Tableau will either read it as a number (for example the number 2,016), or a text string (2016) depending what the field format/data type is in Excel.

Does this mean that you’ll have to give up on your analysis? Not at all! You can fix your data-formatting woes in the Data Interpreter.

The Solution

Once you have your data in Tableau, you need to decide if you want to keep your date as a number (numbers fields have “#” above them), or if you want to change it into a text string (text fields have “abc” above them). Why might you want change your date’s data type from a number to a text string? Well, Tableau lets you sum or divide numbers, but it doesn’t really make sense to sum or divide years; half of the year 2016 are the months January through June, not 1,008!

How to Change a Field’s Data Type

Simply click on the little arrow next to the data type symbol and select “String”.

Changing data type formats

When you go to your first sheet, drag your year field to dimensions if you want Tableau to treat it as a dimension (for more on the difference between dimensions and measures, see Ryan Sleeper’s post).

Drag and drop measure to dimension

What If I Want to Make It Look Like a Date Field?

If your data is formatted MMM-YY(i.e. Jan-16),then you might want to create a new field that is formatted like a date (i.e. 01-Jan-2016). Why might you want to do this? Tableau lets you easily group dates. Tou can only look at years, or at months, or at quarters. This is a pretty handy thing to be able to do.

In the Data Interpreter, create a calculated field

Create a calculate field

And then type in is formula to make every date formatted with "01" (eg first day of the month) in front of it

DATE(RIGHT([Month-Year], 2) + "-" +
LEFT([Month-Year], 3)
+ "-" + "01")

Create a your new date field

Volia! Date rage solved.