Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
ACE Emeritus
ACE Emeritus

The question has been asked numerous times on the forum: how to shift all rows to columns and all columns to rows:

While the Cross Tab and Transpose tools can be combined in straight-forward fashion to solve these, it seems like something that takes literally just one letter in R...


outputData = t(inputData)


... should be just as simple here. Thus the “Totally Transpose” macro:




This will shift rows to columns and vice versa, and will also allow the user to specify if any given input column should transition to the output column names.  Perhaps some examples will explain this better than mere words:


Sample Usage


Sample data used for testing (very similar to the first question in the above links) is as follows:


Test Input Data:



Without specifying a column for output column names

When no column name is chosen, all data (including the input column names) appears as row data in the ouput.


Totally Transpose setting: not using any input field for the output column names:



Output Data – these are the results:



With specifying a column for output column names

When a column name is chosen, all data in that field is used as the column headers and the data is pivoted accordingly.


Selecting that the input field “Specifier” be used for the output column names:




Output Data:



Behind the Scenes



It’s a very straight-forward macro: applying a RecordID to anchor the Transpose and Cross Tab; and if desired, moving a specific column into the output column headers.


I am attaching a workflow with sample data and the macro at the bottom of this post. You can also download the Totally Transpose Macro from the Gallery here.



In testing the macro, I generated 100,000 rows of dummy data similar to the above. On an aging laptop (dual core i5, 8GB RAM), this ran in 10.3 seconds, including writing the output to a csv. One caution, however, is that if you wish to browse the output in Alteryx (e.g. using a Browse tool), the displaying (rendering) of 100,000 columns may be prohibitively slow. I force-closed Alteryx rather than wait for the display to finish.


Download the macro and let me know what you think.  Happy blending!

6 - Meteoroid

Just one word : Awesome !

It's so useful ! Alteryx should manage it natively 🙂

Thanks a lot for your work !

12 - Quasar
12 - Quasar

Excellent work John! 

8 - Asteroid
8 - Asteroid



Thanks so much for this! It was 6 nights ago, on 8/24/16, that I began studying for a top coder challenge. I thought about how a complete matrix transpose could be done in Alteryx, so I started writing one!  My proof is in graphic. I called my version matrix_transpose.  


Aces are aces for a reason!






Is it possible to modify the macro to include transposing the field headers as well? I have tables with financial data and it has dates in the fields and account codes in the records. I need to transpose the tables to do calculations on the account codes and then transpose back to report with the dates as field headers.
5 - Atom

Genius! Bravo!