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

Managing Director, APAC
Building Solutions with InterWorks at Corinium’s Data Architecture Conference in Melbourne InterWorks was a proud sponsor of the Data Architecture Conference hosted by Corinium in Melbourne on 21 and 22 June 2023. Hundreds of ...
Virtual Event Recap: Deep Dive of Tableau Parameters For our June Lunch and Learn, we did a deep dive on parameters for our friends across Australia and Singapore. Tableau is the premier ...

See more from this author →

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

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!