Perhaps you’ve encountered this frustration when importing data into Tableau Desktop or Tableau Prep Builder. The dreaded renaming of all of your columns into F1, F2, F3 and so on.
Perhaps that’s because you are working with an Excel spreadsheet to which you have added headers (which I always promote when talking about good spreadsheet practices), like the example below.
Perhaps you’re working with a CSV, my favorite format in which to download data because it’s so beautifully simple and efficient.
The challenge is, the headers are not what they should be. And typically, data types in all columns is shifted into string, which may be inaccurate. Renaming the columns is an inefficient way to handle fixing this issue. While that’s not onerous with only a few columns, it’s not efficient and it’s not repeatable for other data.
Tableau Prep Builder has 2 easy approaches to promoting the correct column headings. One method involves the Data Interpreter feature, and another requires building out steps in a flow, which you can save and insert wherever you need them.
What is Tableau Prep Builder?
Tableau Prep Builder is a data blending and preparation tool, which allows you to quickly prepare your data for analysis in Tableau Desktop or another program. Tableau Prep Builder helps you easily connect to your data sources, and clean and blend them seamlessly together.
How to Promote Column Headers in Tableau Using Data Interpreter
When you’re working with an Excel file, Data Interpreter can detect things like titles, notes, footers, empty cells, and ignore them to just grab the fields and values in your data set. It can even detect separate tables and sub-tables so that you can work with a subset of your data independently of the other data.
Take the example here:
When we load this data into Tableau Prep Builder, we initially see all of the renamed columns:
If we tick Use Data Interpreter on the left, Tableau Prep Builder gives us several different views, which we can use by dragging those table interpretations onto the canvas.
But what happens when the data interpreter cannot find the headers, or if you are working with a CSV and that feature isn’t available? Then we need to move to Method #2.
How to Promote Column Headers in Tableau By Creating a Flow
You can promote the correct headings using Tableau Prep Builder, it just requires you to build a flow to do so.
To promote column headings when Data Interpreter fails, create a flow that you can then save and reuse elsewhere.
First, remove any extra rows at the top (and internally if multiple tables) of the data by filtering them out.
We will need a way to order the table (and in our instance of multiple tables in the same file, isolate just the first table. Adding a Clean step with a calculated field to create an index can give us this column. Use the ORDERBY and ROWNUMBER functions.
{ ORDERBY [F1]: ROW_NUMBER()}
This works best on a field that has unique values only. Since our data does not match that format, it’s best to add that index column in the source program of the file (e.g., Excel for .xlsx or .csv)Because we are working with 2 tables in this example, we can filter the records in the first table and then create another stream to filter the records in the second table.
Next, we will isolate just the header rows in each stream, by filtering out only the first row, and pivoting all columns but the row number indicator into rows. We then rename these columns to indicate the old and new headers.
We can then isolate the rest of the data from the headers, and also pivot all of the columns except for the row index number into rows.
To get the names into the correct columns, we can join in the Header workstream into the pivoted workstream, matching up the Old Headers into the Pivoted Names column, and remove any extra fields afterwards.
Now the correct column names are associated with their respective rows, and we will later pivot this back into columns. But first we need to assign data types, and create a way to automate this process.
We need something to indicate the type of data. In our example, we have a mix of string and numbers. Regular Expressions give us a great option for separating the numeric data from the string data, and could also be used to separate date data. For more on Regular Expressions, engage in our Tableau Prep Builder: Practitioner learning program.
To filter out the string fields in our data, all of which begin with a capital letter, the following formula in a filter works: REGEXP_MATCH([F],'[A-Z].*’)
And to filter out the nulls and numbers into a separate field, you can use the following formula in a filter: NOT REGEXP_MATCH([F],'[A-Z].*’) OR ISNULL([F])
You could also use a non-Regular Expression way to isolate the numbers and nulls, by filtering with FLOAT([F] )>0 OR ISNULL([F]), and IsNull(FLOAT([F])) AND Not ISNULL([F]) to find just the strings. The ISDATE function can find string fields that are actual dates.
Use clean steps to assign the correct data types into the separate flows.
Pivot each flow from rows back into columns, using the New Headers as the header field, and join the pieces back together.
You have just promoted your column headings into proper column headings using Tableau Prep Builder. You can reorder the fields manually, if desired.
PS – Highlight the steps of the flow you’ve just created, and save them as a flow to insert and reuse later!
Closing
Tableau Prep Builder helps you easily overcome the frustration of header formatting when you are trying to visualize. In many instances, a simple “Data Interpreter” approach will solve your woes. In others, you will build a simple workflow, which you can reuse when the issue rears its head again in another data source.
Tableau Prep Builder will ultimately save you time and energy when working with your data in Tableau Desktop or Tableau Online. Want more advanced techniques with Tableau Prep Builder? Take a look at the Tableau Prep Builder: Practitioner program on Data Coach.
