Input from multiple sheets, transform data and Union

Question

Pls refer to the snapshot below.

1. I have data input coming from multiple sheets of the same workbook.

2. Each input goes through a few steps of transformation.

3. Data from each set is then combined into 1 big data set using the "Union" tool.

Is there a way to do more efficiently? Probably using a macro...?

afv2688 · Accepted Answer

Hello @Utkarshaggarwal,

You just need to input a control parameter to the workflow. After connect it to the file input:

After this is connected select on the action tool to take the filename (like on the image).

Once you have saved it as a macro you just need to pass to the control parameter the filenames and it will perform the same task for every file and come as an output (remember to add the macro output in the end. This would be like on your previous workflow before the input on the union)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Utkarshaggarwal · Accepted Answer

@afv2688

Thanks for your help.

As my requirement was to collate data from multiple worksheets of a single workbook, I passed the Sheet names to the control parameter and then added just 1 more step.

In the action tool, I selected "Replace a specific String:" option and entered just the sheet's name (see the image).

Worked like a charm!!!