Reshape Excel Data Like Tableau Excel Add-in

Question

I use Hyperion SmartView for Excel which returns data in a crosstab format like FIGURE 1, including multiple rows of header fields that are required to get the right data out of Hyperion.  Currently I use the Tableau add-in for Excel to reshape the data, which takes the highlighted fields in FIGURE 1 and transforms them into one row per cell, and adds the header fields from each column as columns to the left of the measure cell value as in FIGURE 2.  Is this same transformation possible in Alteryx?

FIGURE 1

FIGURE 2

Hyperion_Example.xlsx

michael_treadwell · Accepted Answer

The transpose tool is what you are looking for but the headers information as separate rows in the columns throws a wrinkle into the solution.

I used transpose and then a summarize tool to capture all of your headers, comma delimited. Then used the Text to Columns tool to split them apart.

Note that in my Text to Columns tool the '# of columns' is hardcoded so if that is going to change between worksheets that may require extra development.

Either way, this idea should get you on the right track.

s_pichaipillai · Accepted Answer

@ShaneGraham

Here is the another solution for you . i have not tested the Result fully but if you find anything then let me know

Thanks

Saravanan