Cross Tab w/ Transpose?

[ColliersOS]

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, 

[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. 

[BradWerner · Accepted solution]

Hi @ColliersOS,

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

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

[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, 

[BradWerner]

Thanks for the kind words @ColliersOS!

Happy to help! I learned something along the way as well.

Have a great day,

Brad