Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.
SOLVED

Rotate data like Excel paste special > transpose function (turn rows into columns and vice versa)

How to do this? Want example below to have first column named Attribute, then the values in that column would be "BA-SL", "BA-SL-L", "BA-SL102S" for example.

4 REPLIES 4

Here is example image... wont' let me copy and paste for some reason.

This is "solution" I came up with but I have to manually adjust value count to match # of columns...

 

drop col: column2,column3,column4,column5 action: Drop

filter type: missing missing: column6 action: Delete

nest col: column6, column7, column8, column9, column10, column11, column12, column13, column14, column15, column16 into: array as: 'column1'

drop col: column7, column8, column9, column10, column11, column12, column13, column14, column15, column16, column6 action: Drop

derive type: single value: column1 as: 'column1_copy'

move col: column1_copy position: after after: column1

unnest col: column1_copy keys: '[0]' pluck: false markLineage: false

flatten col: column1_copy

window value: mod(rownumber() - 1, 11) group: order: $sourcerownumber

sort order: window

pivot col: {0} group: window value: any(column1_copy) limit: 50

 

Trifacta_Alumni
Alteryx Alumni (Retired)

Hi @Jacob Joseph? you've put together a good solution! When you're referring to adjusting the value count to match the number of columns, I assume you mean the window operation, specifically "mod(rownumber() -1, 11)", correct?

 

You can avoid modifying this step based on the number of columns in your dataset by utilizing the "unpivot" transformation instead of your existing steps 3-9. The "unpivot" transform converts existing columns to rows. Here's how this operation would work:

 

1. Choose the "Unpivot" icon from the toolbar.

 

2. In the panel on the right side of the screen, complete the transform by entering the names of the columns that contain your data values into the "Columns" field. I mocked up an example dataset based on your screenshot. As you can see in the image below, I've entered column7, column8, and column9 into this field, since those are the columns that contain the values associated with each label in column6. I excluded column6 from the unpivot so that I retain this label on each out my resulting records.

 

The output of this transform will be two new columns: one column named "key" that contains your original column names, and one column named "value" that contains your original column values. The benefit of the "unpivot" transform for your use case is that you can retain relationships between the existing records without needing to create a rownumber column. Notice as well that the values for column6 are duplicated for each of the new records that you have created.

 

3. Once you have added the unpivot step to your recipe, you can perform a pivot. Given the new data schema, you will enter "column6" in the "Column labels" field, and "key" in the "Row labels" field. This transform will look like the image below:

You can see that by performing the unpivot step first, we've retained the relationship between your original fields. Using the "key" column in the "Row labels" field essentially replaces the window operation from your original recipe.

 

4. There are two final clean-up steps to perform after the pivot. First, you can delete the "key" column.

 

Second, you can clean the column names using the "Rename columns" transformation and the "Find and replace" option. You will choose "All" in the "Columns" field for this transform, "any_value_" in the "Find" field, and "" in the "Replace with" field. Here's how this step looks:

 

That's it! Let me know if this helps.

THANK YOU!!!!