Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Transform dimensions of table.


Hi, I was wondering if Alteryx had to the capability to transform the dimensions of the data. Here's what my data looks like:


Grade AbbrevABCD
 GradePre-K1st Grade2nd Grade3rd Grade


here is how I need it to be:


Grade AbrevGradeDate# of Students
B1st Grade7/20/201729
B1st Grade8/20/201728
B1st Grade9/20/201724
B1st Grade10/20/201725
B1st Grade11/20/201723
C2nd Grade7/20/201731
C2nd Grade8/20/201730
C2nd Grade9/20/201736
C2nd Grade10/20/201735
C2nd Grade11/20/201714
D3rd Grade7/20/201716
D3rd Grade8/20/201715
D3rd Grade9/20/201717
D3rd Grade10/20/201719
D3rd Grade11/20/201720


Being new to Alteryx, I tried using the append tool but i'm not quite getting the results I intended.  Any help is appreciated.

Alteryx Certified Partner

Hey @jcyc


  1. First transpose your data grouping on Grade Abbrev
  2. Filter on the rows the have Grade in the Grade Abbrev column
  3. Use a join tool connecting the true to left and false to right..using name as your join criteria

This will give you the right result

Alteryx Partner

I think the tool you're looking for is the Transpose tool, but your input table isn't a great format for working with in Alteryx because you have two field indexes (Grade Abbrev and Grade). I've provided a sample workflow where I made the second row there the field names, and created the abbreviations afterwards.




Essentially, you want to Transpose, using the date field (The called "Grade" for me) as the Key, and the others are the data. This will get the correct dimensions you're looking for. I then use a Switch expression to create the Grade Abbrev based on Grade, Select Tool to rename/reorder things, and Sort tool to sort by the Grade Abbrev to get exactly what you have below.


Let me know if this helps,




That worked!


Question for you: is there a way to automate what the Switch Formula is trying to do? I ask because what if the data set has grades going up to for example Grad school. There'd be a lot of manual typing of how to assign what grade to which grade abbrev.

Alteryx Partner

Given that the Abbreviations need to be ordered the same way as the classes, I'd recommend either creating a lookup table in excel. You could do this by copying your original top 2 rows (the abbreviation, and the grade name), and then using Paste Special (Transpose). If you need help with that let me know.


Then you could read this in, and instead of using the Formula tool you could just Join on Grade.


Another option would be to add a Summarize Tool, and Group By Grade. You'll now have a list of unique grades. You can then create a formula something like:




From here you could join back in. The problem with this approach is that it is difficult to sort the Names in the order you'd like.


Let me know if either of these solutions works for you,