Cross Tab w/ Transpose?

Hi everyone - I need some assistance in determining the correct steps to apply both a cross tab and I think a transpose for a dataset I'm stuck on - I was hoping some of you had some suggestions.

See attached data model with a sample of the raw data and the desired results/output.

This has been a particularly frustrating exercise in trying to get the correct settings for transpose and cross tab, as Excel's Power Query functions very differently (and much more simply, efficiently and quickly) than the corresponding functions in Alteryx I've noticed, but hopefully someone here can prove me wrong.

In PowerQuery, I successfully accomplished this desired output by transposing the unique RecordIDs or values and then Unpivoted (ie., Cross Tab) the transposed columns into a single column with the corresponding data values, region designations, years and client names. I haven't found a workflow or series of steps that accomplishes the exact same task in Alteryx just yet.

Any ideas would be much appreciated and very helpful.

Thanks,

Cross Tab Transpose Workflow Sample.yxmd

Datasets

Transformation

Preparation

Accepted answers

BradWerner

Hi @ColliersOS,

I used the technique mentioned in @hhaigh1's blog post below and made some adjustments for your specific situation to get the following results. Please see my attached workflow for specifics.

Annotation 2020-07-30 094518.png

Here is the awesome blog post:
https://community.alteryx.com/t5/Alteryx-Designer/How-to-combine-multiple-header-rows-into-one/ta-p/33003

Cross Tab w Transpose.yxmd

All comments

BradWerner

Hi @ColliersOS

Would you be able to post your workflow with data in a text input tool instead of an Excel file stored locally on your computer?

Another option is to save and upload a packaged version of your workflow. You can package a workflow by going to Options > Export Workflow. That will bring the data along with the workflow.

Either way will allow us to see the current and desired data formats in order to answer your question.

Thanks!

Brad

ColliersOS

Hi Brad - Thanks, here's the exported workflow. Please let me know if you need anything else.

Cross Tab Transpose Workflow Sample.yxzp

BradWerner

Hi @ColliersOS,

I used the technique mentioned in @hhaigh1's blog post below and made some adjustments for your specific situation to get the following results. Please see my attached workflow for specifics.

Annotation 2020-07-30 094518.png

Here is the awesome blog post:
https://community.alteryx.com/t5/Alteryx-Designer/How-to-combine-multiple-header-rows-into-one/ta-p/33003

Cross Tab w Transpose.yxmd

ColliersOS

Wow, Brad - This is absolutely fantastic, I never would have figured this out in my hacking away at Alteryx. Thank you so much or keeping up in the thread and offering your knowledge and expertise, it's much appreciated.

Cheers,

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280