Sometimes You Have to Break the Rules. Expanding Beyond 16 Columns without Header Concatenation

By Kathleen Goepferd 02 Mayo, 2013

Header Concatenation: This was a new problem for me.

Heck, it was a new word for me up until recently.

Janet Shen, one of the incredible Viz Magicians from Tableau Professional Services sent me a great "How To" guide for overriding our default settings and I couldn't wait to share it. The useful content is ALL her. The silliness is all me. Hope you find this as helpful as I did!

Click inside to get the step-by-step tutorial.

Here at Tableau, we like to be helpful. As part of that, we have built our tools to steer you toward visual analytics best practices in subtle ways—and sometimes in not-so-subtle ways. Header concatenation is one of those less-subtle examples.

As you may already have learned, Tableau Desktop will concatenate row headers if there are more than six discrete (blue) fields on the Rows shelf.

We do this because we have found that using more than six separate fields can result in cluttered, hard-to-read visualizations.

However, being a group of rule-breakers and rebels ourselves,we do understand that sometimes you need to color outside the lines, man.

This six-field limit can be increased by adjusting the “Maximum levels of columns labels” setting under Analysis > Table Layout > Advanced.

Our knowledge base has a great article about doing exactly that. However, this setting can only be increased to a maximum of 16 column labels.

But what happens when your boss comes to you with a big ol’ Excel report and demands that you replicate that report in Tableau? You know it’s not going to be pretty. You also know that when your boss’s forehead vein is throbbing like that…it’s not the moment to argue Visualization Best Practices. You’ve got to make this work.

Ok, first of all, we need to say that we don’t recommend this. We really don’t recommend this. We have strong feelings about this, as a matter of fact.

But we trust you. We know that YOU know when breaking the rules is that right answer.

::deep breath::

Okay. Here we go:

To work around maximum columns, users can take advantage of these two facts:

  1. You can stack unlimited numbers of continuous fields (green) on the Columns shelf.
  2. With Multiple Mark Types enabled, a different label can be applied to each measure.

Here are the 10 steps to achieve a fully customizable text table that exceeds sixteen columns in size:

1. Create a set that combines of all the discrete fields currently on the Rows shelf. This set will define how many rows are created for the table’s contents.

2. Use Shift + Click to select all the other discrete fields on the Rows shelf. Drag them to the Level of Detail shelf.

3. Create a calculated field that is just the number 1.

4. Right-click-drag this field to the Columns Shelf and select the MIN() option.
On the Marks card, click Label > Show mark labels.
Notice how all bars (and subsequently, all labels) are aligned, compared to a field that contains varying data.

5. Verify that the mark type is set to “Bar,” not “Automatic.”
Open the Marks Card menu and select Multiple Mark Types.
Remove Measure Names from the Color shelf.

6. Double-click the axis for Min. 1 and set the range to Fixed: 1 to 3.
Clear the Title completely.
Go to the Tick Marks tab and select “None” for both major and minor tick marks.
Click OK.

7. Hold the Ctrl key and drag MIN(1) on the Columns shelf on top of SUM(Sales).
Double-click the axis of this new MIN(1) to change the axis range to a fixed 1-2.
Change Title to the first field name.
Go to the Tick Marks tab and select “None” for both major and minor tick marks.
Click OK. Right-click the second MIN(1) and select Dual Axis.

8. Click the leftmost MIN(1) on the Columns shelf or navigate to it using the arrows on the Marks card.
Drag the first discrete field from the Level of Detail shelf to the Label shelf.

9. Click the rightmost MIN(1) on the Columns shelf or navigate to it on the Marks card.
On the Marks card, open the Label menu and deselect “Show mark labels.”

10. Add more dual axis pairs of MIN(1) on the Columns shelf, progressively dragging more fields from the Level of Detail shelf to the Label shelf.
When done, navigate to All on the Marks card and remove “Multiple Fields” from the Level of Detail shelf and clear the Color shelf, which can now be used to conditionally format the cells in one column at a time.

And that’s it! You did it! That was really... kind of a hidden maze, eh? Sorry about that, but we don’t want people just stumbling onto this sort of advanced maverick stuff.

Basically, we hope that you’ll keep in mind when you find yourself having to dig three, four, five layers deep to try to override our defaults… to please just stop and think about if you want—if you really, really want—to be doing what you’re doing.


Submitted by JoeP (no verificado) on

Just give us a standard data grid view already.
Thanks for showing us this, though.

Submitted by Joe M. on

If you want a data table view in Tableau, you may want to consider transforming your data for Tableau to enable the view you want.

Instead of fighting with Tableau, using difficult to maintain workarounds that force Tableau to generate the view you want, I think it is better to work with VizQL.

If you can transform your data prior to Tableau, performing a un-pivot, then generating a data table view in Tableau is a joy instead of a pain.

Here is an example result:

If your argument is, "that multiplies my data source into something too large!" Then you can use a scaffold data source to paint your data onto marks with a data blend.

Think with VizQL, Tableau is an amazing tool.

Submitted by JoeP (no verificado) on

Thanks, Joe.

My argument isn't, "that multiplies my data source..." My argument is, everyone and their brother is familiar with viewing data in an Excel-type grid; every other data viz tool (Spotfire,etc) provides this view of the data; this is the way users naturally want to see the data - why not just provide it natively?

I think it's ridiculous to have to go through various complex gyrations just to recreate a simple, basic, standard view of data.
Besides, these workarounds usually make basic filtering and sorting impossible.

It's just silly.

Submitted by Joe M. on

> "why not just provide [a Excel-type grid] natively?"
Views in Tableau are built with VizQL, and VizQL does not approach data in the same way the Excel does.

I can understand that after working with Excel and other tools, that there is an expectation for all software to be able to make a data table in the way expected, but Tableau operates in a different context, a different perspective of data than Excel and other tools.

Tableau can create data tables as you want, with any sorting and filtering, it just requires awareness of how to work with VizQL instead of against it. If you are having any trouble getting a view that you want tin Tableau, you are welcome to contact me, and I would be glad to help.

I think it would be great if Tableau was not limited by the data source structure and contents, but if you can transform your data prior to Tableau you can get nearly any view you want.

Submitted by JoeP (no verificado) on

Thanks again, Joe. As always, you have great insight into all things Tableau.

But I think we are looking at this from different perspectives - the old is it a bug or a feature? - question.

I don't give two squats as to why Tableau has a key limitation. I just know that I am unable to provide my end users a view of the data that they are expecting and wanting to see. And it doesn't help that every other tool like Tableau offers this out of the box.

I know VizQL is a powerful platform. But if I have to acquire esoteric 'awareness' and jump through hoops just to display a standard grid of data then it is fundamentaly flawed in my (and every single customer I have ever worked with) opinion.

And to be clear, I only want to display data in the same flat table that already exists in the source data. But I want to be able to sort on each column without having to create the calculations required to work around all the goofiness introduced by the current nested data view Tableau provides.

I want a Show Me option called "Data Grid" that presents my data in the basic Excel column and rows format. I would use this view way more often than the current nested data view.

Submitted by Kevin Ruscoe (no verificado) on

As per the suggestion from @joe_mako, I have created an idea [1] for this requirement. I share what I believe to be the widely held view that this is a significant gap. I understand that the feature may not be easily implemented using VizQL but that is not a reason to ignore it. Here's hoping the idea gains some traction.


Submitted by Rishad K. on

Thanks for the post. I am trying to achieve the same. However, I can not figure out how you do STEP 1. can you please elaborate STEP 1 i.e.
1. Create a set that combines of all the discrete fields currently on the Rows shelf. This set will define how many rows are created for the table’s contents.

Submitted by Paul Mills (no verificado) on

Yes, I'd like to know this as well. I started thinking step 2 was a substep of step 1 but clearly it isn't. The other steps are explained in a lot of detail but not step 1.

non-humans click here