Reading and compiling multiple sheets then tranpose

Hi guys!

Trying to read multiple sheets from a workbook but I'm getting stuck at the dynamic tool, for some reason it does not take all the data. I am attaching the file, as well as the desired outcome.

I used this config on the input tool

And this one for the dynamic tool

I get the warnings that they are not the same as the template, although it is the same, except for a couple of sheets, which are shorter in terms of the period analysed.

Therefore I just want to put each of them under this format, but I think I will manage to do it with transpose (?) so on and so forth for each country in the project.

Any suggestion would be much appreciated!! Thanks a lot in advance guys!

Project data.xlsx

Transformation

Preparation

Time Series

Accepted answers

FrederikE

Hey @PTM98,

You can use a batch macro instead.

Important:

- Exchange the sheet name with the inputs into the macro (You need to redo this after changing the path to yours) (Fig.1)

- Use the Property setting that they columns are stacked on top by position/name (Fig. 2)

New Workflow2.yxzp

All comments

Qiu

@PTM98
There is an existing macro we can bring the data with different schema.
https://community.alteryx.com/t5/Community-Gallery/Dynamic-Multi-File-Sheet-Names/ta-p/919218

Multi Sheet Import Example 2.yxzp

FrederikE

Hey @PTM98,

You can use a batch macro instead.

Important:

- Exchange the sheet name with the inputs into the macro (You need to redo this after changing the path to yours) (Fig.1)

- Use the Property setting that they columns are stacked on top by position/name (Fig. 2)

New Workflow2.yxzp

Qiu

@PTM98
We can do a Batch Macro as below, note that some data cleansing required/.

0510-PTM98.yxzp

PTM98

Thanks everyone for their time!! Used it, but did not have the time to post again, forgot about. Thank you again!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703