Transforming an excel sheet before appending by Alteryx

Question

Hey, I am a new user of Alteryx (transferring from PQ :) ) and I am having a following issue:

My excel file has multiple sheets, every one of them shows consistent data (with the same headers) in the first 5 columns, however from 6th column, they start to show monthly data and headers present Month-YY.
The problem is that sometimes 6th column shows January, however it can also show March if the specific sheet shows only data from March to December.

Is there any way to Input all sheets at once, however for example transpose (unpivot) all columns after 5th before Alteryx appends the sheets one under one?

Hope I explained it clearly 🙂

Thank you!

Klaudia

LukeM · Accepted Answer

Hi @K_Kumorek

This is a great example for where a Batch Macro is really useful. Your Batch Macro will take the list of sheets as the control parameter, and then read them in and transpose them and output one by one.

Similar to this:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

But including the transpose and any renaming before the Macro output stage.

Be sure to set the settings of the macro to allow it to finish processing before outputting records.

Let me know how you get on!

Luke

MichalM · Accepted Answer

@K_Kumorek

You could build such functionality using a batch macro.

1. Connect to the workbook using the Input Data tool and get the list of sheet names just like you would for the Dynamic Input tool

2. Build a batch macro replicating what the Dynamic Input does - replacing the reference to the sheet name in the workbook path but also add the transpose step to make sure that the columns which differ across the sheets are flipped vertically

3. Add a row ID to and a File reference if you need to flip the data back

Example attached

fmvizcaino · Accepted Answer

Hi @K_Kumorek ,

Attached is an example showing how to get that done.

I'm using a batch macro that does exactly what you are looking for, the idea is to open files one by one, transpose and them concatenate all of it.

Let me know if that works for you.

Best,

Fernando Vizcaino