Tableau pivot rows into fixed number of columns

Question

I am new to Tableau so maybe this is an easy question but I can't get it done yet. I have my data in the following format:

  EntityId  |  ActionId
------------|------------
     1      |     2
     1      |     6
     1      |     1
     1      |     7
     1      |     7
     2      |     1
     2      |     2
     2      |     3
     2      |     3

My desired table format for my visualizations looks like the following:

  EntityId  |  1stActId  |  2ndActId  |  3rdActId
-------------------------------------------------
     1      |     2      |     6      |     1
     1      |     6      |     1      |     7
     1      |     1      |     7      |     7
     2      |     1      |     2      |     3
     2      |     2      |     3      |     3

So I want to extract all Action Triples where every action is in one column. The next step would be to have the number of columns variable so that I can get Tuples, Triples, Quadruples and so on.

Is there a way to do this in Tableau directly or do I have to transform it before importing it in Tableau?

Thanks in advance!

Best regards,

Tim

Okechukwu Ossai Okechukwu Ossai · Accepted Answer · 2016-06-22T11:16:06

Interestingly Tableau works best with your current data format rather than your desired table format. There is a functionality called Pivot which transforms your desired table to your current table format but not vice versa. To achieve what you want, you will have to transform the data before importing it into Tableau. Otherwise, consider the format below, depending on your objective, it may give you opportunity to filter, group and drill down into your data. However, it will duplicate the EntityId, assuming this is not an issue for you.

EntityId    Value   ActionId
1             2       1st  
1             6       1st  
1             1       1st  
2             1       1st  
2             2       1st  
1             6       2nd  
1             1       2nd  
1             7       2nd  
2             2       2nd  
2             3       2nd  
1             1       3rd  
1             7       3rd  
1             7       3rd  
2             3       3rd  
2             3       3rd

Tableau pivot rows into fixed number of columns

1 Answers