Un-Pivot Data in Tableau

Say you have data with fields named: A, B, C, KEY, VALUE. And lets say the KEY field contains a discrete set of possible values like X, Y, and Z. How do you transform your data with Tableau so that your resulting data has fields: A, B, C, X, Y, Z?

Given an original record set of records that have A=a, B=b, C=c:

  • the value for X should be the VALUE from the original record containing A=a, B=b, C=c and KEY=X
  • the value for Y should be the VALUE from the original record containing A=a, B=b, C=c and KEY=Y
  • the value for Z should be the VALUE from the original record containing A=a, B=b, C=c and KEY=Z

If here were 10 records with value A=a, B=b, C=c and various (Key, Value), the resulting transformed data set should have one record having A=a, B=b, C=c. I don't want to have to know ahead of time what values are represented in the KEY field, and I want all unique combinations of values of A, B, C to be consolidated into single records in this way. I essentially want to unfold the data so that the values of the KEY field become columns, and the associated values of the VALUE field become the values under those KEY columns.

This feels like something Tableau should be able to do for me in some standard way, can someone point me in the right direction on how to do it?

Aside

You might ask why do you want to do that? and the reason is because I want to filter out all the rows from my dataset that have the same values of (A, B, C), if any row having that value (A, B, C) has KEY=k and VALUE=v. In order to do that, my idea was that I need all the rows with common values for (A, B, C) to be collapsed into a single row by un-pivoting the (KEY, VALUE) columns in the data set.

Topic transformation tableau

Category Data Science

1
answered at

We can do a manual unpivot.

I'm not exactly a tableau veteran, but I've faced similar issues and this is what I've been able to figure out.

First, we need some data to work with in Tableau. I whipped this SQL Server query up:

SELECT 'John' as [A], 'Paul' as [B], 'Jones' as [C], 'X' as [KEY], '12' as [VALUE]
UNION ALL
SELECT 'John' as [A], 'Paul' as [B], 'Jones' as [C], 'Y' as [KEY], '74' as [VALUE]
UNION ALL
SELECT 'John' as [A], 'Paul' as [B], 'Jones' as [C], 'Z' as [KEY], '25' as [VALUE]
UNION ALL
SELECT 'John' as [A], 'Paul' as [B], 'Smith' as [C], 'X' as [KEY], '99' as [VALUE]
UNION ALL
SELECT 'John' as [A], 'Paul' as [B], 'Smith' as [C], 'Y' as [KEY], '98' as [VALUE]
UNION ALL
SELECT 'John' as [A], 'Paul' as [B], 'Smith' as [C], 'Z' as [KEY], '97' as [VALUE]

This will give us the following dataset, which I believe is similar to what you've described.

A B C KEY VALUE
John Paul Jones X 12
John Paul Jones Y 74
John Paul Jones Z 25
John Paul Smith X 99
John Paul Smith Y 98
John Paul Smith Z 97

Because we're working with known, discrete values for "KEY", we can easily do this manually.

Start by dragging Dimensions A, B, and C to your Rows shelf.

Tableau Image Step 1

Create a calculated field named UNPIVOT_X, and type the formula below:

ATTR(IF [KEY] = "X" THEN [VALUE] ELSE NULL END)

Tableau Image Step 2

Do this three times, substituting "X" for the other values.

Drag these new measures to your rows shelf.

enter image description here

The ATTR() aggregate will only work if there is a one-to-one relationship between KEY and VALUE. Otherwise, choose your preferred aggregate -- MAX(), MIN(), etc.

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.