Tableau Extended: Pivoting Fields on the Data Connection Page

Data

Tableau Extended: Pivoting Fields on the Data Connection Page

Tableau Extended takes topics from Tableau Desktop training courses and expands upon those lessons for advanced functionality and use cases.

In the Tableau Extended series, we are exploring all of the extra features that aren’t covered in the Tableau Desktop curriculum. Think of this series as a companion to your Tableau Desktop training experience. In this article, we’re going to continue to explore the Data Connection Page that we started in previous article. Using either the Data Preview or the Metadata Grid option, you can pivot your fields for a different format to the data from Excel, text, PDF and Google Sheets data sources.

Let’s examine this feature with a specific example.

Sample Data

There are a lot of data sources that would benefit from a pivot in Tableau. Here’s one such example that I’ve seen several times where dates are divided between rows and field headers:

Crosstab

In this format, our date fields are divided with years being column headers and individual months within the Month field. This is a useful view, but the structure of the data limits the other things that we might do with this data. Let’s import this into Tableau Desktop first and then make it more versatile with a pivot.

Here’s the Excel file for the above data source for your own practice.

Preparing the Table

After we’ve connected to the table, we can see that Tableau Desktop has made some incorrect assumptions about the data and the field headers. Have a look:

Data and field headers

Tableau Desktop algorithmically analyzes data when it is imported. When Tableau saw this data source, it saw a column of strings followed by six columns of numbers. It did not recognize Month or the Years as field headers. We can fix that.

I’m going to right click on the selected sheet for this data source. The default option selected is Generate field names automatically, but I want to switch it to Field names are in first row:

Generate field names automatically

This removes the F1, F2, etc. field headers and instead uses row 1 from the sheet as the field names.

Field names are first in row

We are now ready to pivot our fields.

Pivoting the Fields

Pivoting our fields means that we are taking all of these Year columns and combining them into a single column. An easy way to think of this is that rather than Years being in a crosstab across the table horizontally, we are combining them into one field that reads vertically. The numeric data contained in each Year field is then pivoted, i.e. rearranged, accordingly.

To pivot fields, select the field headers that you want to combine into a single column by holding down the CTRL button and then right-click.

Pivot fields

When Tableau converts these into pivoted fields, it generates generic names for the two new columns: Pivot Field Names and Pivot Field Values.

Generic names

Let’s rename each column according to Years and whatever our measure represents. How about Sales?

Rename fields

Let’s change the Sort fields drop-down to Z to A descending per table just to see the fields in a more logical order. Here’s what we get:

Sort fields

The final step for this particular example is to combine the Month and Year field into a date data type that we can use as a Date Part or Date Value, the full range of options, using the DATEPARSE function. Here’s the calculation you would use to do that:

DATEPARSE calculation

Learn more on the DATEPARSE function with my Deep Dive on Dates.

Final Result

Now that we’ve got our fields prepared and converted into a proper date data type, it opens the doors for a lot more options when creating your analysis. Here’s an example using a % of Total table calculation for quarters by year that would have been very difficult to achieve with the crosstab that we started with:

% of Total for quarters

What’s Next?

We are going to continue talking about the features on the Data Connection Page with our next article in the Tableau Extended series when I examine the Data Interpreter. Until then, leave any questions or thoughts in the comments section below.

And if you’re looking to apply a data pivot to other types of data sources (such as SQL for instance), consult this Tableau article for more assistance.

More About the Author

Robert Curtis

Analytics Consultant / ANZ Practice Lead
An Interactive Guide to Public Toilets in Australia Traveling requires planning. Where do you want to go? How will you get there? Where will you stay? What will you eat? These are the ...
Tableau Desktop Now Available in Dothraki With aims to broaden their global customer base, Tableau Software (NYSE: DATA) announced today a Dothraki language version. The ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK