Complex header to long format data

I am relatively new to Alteryx, moving from excel power query. I am trying to go from input tab to output tab per attached file. While this was straightforward with power query, looks very convoluted to do with Alteryx unless I am missing something. I have query saved in the excel file as well for the transformation. Is somebody able to please suggest a plan of action for Alteryx?

Sample.xlsx

Input

Parse

Accepted answers

Qiu

@saveen_kumar22
We can be sure Ateryx is capable of that. and repeatable. 😁

0703-saveen_kumar22.yxmd

Qiu

@saveen_kumar22
The Dynamic Multi File Sheet Names Macro is a good macro and I use if all the time.
The macro is best for the multiple files, multiple tabs with different schema.
but I think for your case, we can make it easier since you mentioned there are only multiple sheets.

We can use the Input tool to bring in the Sheet List first then use a macro with Dynamic Input tool to process.

Quick Sample as below.

0703-saveen_kumar22-r1.yxzp

All comments

Qiu

@saveen_kumar22
We can be sure Ateryx is capable of that. and repeatable. 😁

0703-saveen_kumar22.yxmd

saveen_kumar22

Thank you @Qiu , that is a very elegant solution: much better than the messy workflow I was trying to build, much appreciated! I have more than 100 tabs of data that I would need to feed to this workflow, which Google says is possible with a batch macro in Alteryx. That is something I cannot do on power query for sure. Are you able to please advise how I can possibly convert it to a batch macro to read all the tabs from one single file? Sample excel attached.

Thank you so much for your help!

Sample Multi Tab.xlsx

saveen_kumar22

@Qiu, based on your solution and another macro from the system I cobbled together a batch macro, which doesn't work as intended . Attaching my attempt for your kind advice. Thank you so much!

Sample Multi Sheet Import Example.yxmd

Sample Multi Tab.xlsx

SampleBatchMacro.yxmc

Dynamic Multi File Sheet Names.yxmc

Qiu

We can use the Input tool to bring in the Sheet List first then use a macro with Dynamic Input tool to process.

Quick Sample as below.

0703-saveen_kumar22-r1.yxzp

saveen_kumar22

Thank you so much @Qiu , this whole exercise helped me to better understand Alteryx in general and batch macros in particular. This will allow me to read the hundred odd sheets in the file in one go, something that I could not possibly do easily in Power Query. Glad we decided to get Alteryx in!

Qiu

@saveen_kumar22
I am so happy that I helped a bit.
An more over that you agree that alteryx is a really good platform for us to do our work much easier.

Lets enjoy the jouney.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703