Complex header to long format data

[saveen_kumar22]

Hi

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?

[Qiu · Accepted solution]

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

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

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

[Qiu · Accepted solution]

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

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