Hi,
I've a dataset that looks like this(demo only, I've more columns in it)
Title | Mr | Mrs |
Name | John | Alice |
Surname | Adams | Adams |
Address | 3z | 4z |
Phone | 123 | 456 |
What I want to do is to pivot the data in a way that my new headers are the values from first column and the rest follows the suit. Exactly how it looks below.
Title | Name | Surname | Address | Phone |
Mr | John | Adams | 3z | 123 |
Mrs | Alice | Adams | 4z | 456 |
I tried using Cross Tab to do this but it works partially as it only allows me to select one column for values hence I lose all the info in the third column.
Is there a way to do this, bearing in mind that the dataset is much larger and there are more columns.
Thanks
Solved! Go to Solution.
Hi Pav,
Try a Transpose first, Key Field your first column, all other fields as Data Fields. Then do the Cross Tab, again with your first column as the only Group Data field, and then "Name" as New Column header and "Value" as "Values.
Hope that helps!
Hi John,
Unfortunately this didn't work for me, it produces the exact same output as input i.e. the table looks exactly the same prior to using the transponse and cross tab tools.
PS, and a Dynamic Rename can shift the first row of data into the column headers if desired as well:
This question is actually fairly common so I went ahead and created a macro that "totally transposes" the data, all rows to columns and vice versa; with the option to do that shift into column headers. Hopefully you or anyone else might find it handy. (Attached).
EDIT: altered to use user-specified column name for the input column to transpose into column headers.
This is fantastic, thank you John!
No problem! PS, I also realized that, depending on the input column names, simply grabbing the first one (in the macro) isn't reliable, since they could get reoardered during Transpose/Cross-Tab.
I therefore tweaked the macro to allow the users to specify an input column, if desired, that will contain the output column names.
Rather than re-attach it here, I edited the original post above to include the corrected version of the macro.
Enjoy.
Hi Thank you for this solution. Do you think it would be possible to achieve solution like that? So I have data normalized and can easli create Pie Chart in Tableau.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
It pivots by Steps Values:
|
HI @dlesny,
For that I would first add a Record ID, then anchor on that to Cross Tab #Feature into the columns, then Transpose your "Steps" fields down to rows.
Does that work for you?
- John