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?
— David Pires (@davidmpires) May 9, 2016
Data-Agony Aunt replies: Don’t fret, David. This looks like a case for Tableau’s Data Interpreter!
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.
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.
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.
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!
— 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.
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.
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”.
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).
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
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) + "-" +
+ "-" + "01")
Volia! Date rage solved.
Data prep question?
Tweet it to our Data-Agony Aunt!