Merging multiple tabs and include a column which indicates the tab name

[kv-defy]

The data is a snippet of the dataset. I have currently created a workflow that specifically takes in a tab of the excel sheet and populate the desired data. Is there a way to do this for all the other tabs using macro?

I have initially tried to combine the data for all the sheet tabs (more than 50 tabs), the data gets very messy and very hard for me to read as there are thousands of rows and hundreds of columns. I also initially wanted to do the workflow for each tab manually by changing the tab name. However, it is too burdensome and tedious because I have 50 tabs. I would like to automate this which involves lesser human intervention. 

I would instead like to do the workflow 1 by 1 for each tab using macro and then merge them together with a new column that represents the tab name when the data for each tab are cleansed and modified. Is it possible for the workflow to populate each tab and then have another workflow to merge them together?

Alternatively, is there better ways of doing it? I am still new towards Alteryx and would need to seek advices from you. 

Attached is the test file.

[SPetrie · Accepted solution]

Are you doing the same cleansing and modifications in each tab, or are you doing different things for each one?

If you are doing the same modifications on each sheet, then this is a perfect use case for a batch macro.

You first bring in a list of sheet names for the excel sheet and the full path of the file name. Replace the <List of Sheet Names> with the actual sheet names and you have the setup you need to feed the macro.

For the batch macro portion, you need a file input and have a control parameter to accept the full path to the file/sheet and an action tool to update the file/sheet being opened.

For the sheet name, I choose to be fancy and lazy at the same time. We already have the sheet name thanks to the initial list of sheet names, so I feed that to a second control parameter. I changed the name of that parameter to something easy for me to identify and then I can reference it in a formula tool using a reference shortcut.

That sounds and looks a bit daunting, but it only takes a few moments to actually setup so its worth it. Alternatively you can have the file input add the full file name and parse the tab name from that or have a second action tool to update a formula field, but I find this method to look and feel cleaner.

After that initial setup, its really just adding any formulas and transformations you wish to happen on each sheet.

In the interface designer for the macro, check the radio button to auto configure by name.

Save the macro, add it to your workflow and point it to the columns that contain the sheet name and full file path.

I attached the sample workflow with macro for you to take a look at. Hopefully this helps with your task.

Let me know if this isnt quite what you are looking for or if you need additional clarification.

[kv-defy]

Thank @SPetrie for your detailed explanation. I am currently exploring the workflow and trying to cleanse the data with the original workflow. You mentioned "After that initial setup, its really just adding any formulas and transformations you wish to happen on each sheet." I am trying to include the original workflow that I have to cleanse the tab. But I am having trouble with it. Do I add the workflow in the BatchExample.yxmc that the Macro contains? If so, I tried it and when I tried to run the workflow with the sheetname, it does not work. It simply just merges all the data together.

[SPetrie]

Yes, you would add any modifications you want to the BatchExample.yxmc.

Interesting that it didnt work and just merged without creating the tab column.

If you have the workflow with example data like the sheet you shared previously, can you export the workflow and share that. It may be easier to figure out what is happening if we see how you have it setup.

[kv-defy]

@SPetrie 

I have tried to combine the data. However, when I am trying to include new functions inside the BatchExample, the workflow result that is produced by BatchExample varies from the actual example.yxmd output. For example, as I used the select tool in BatchExample and select those columns that I wanted, the workflow result of the example.yxmd did not output the exact same result that I wanted. Combining the data seems alright but the formulas does not seem to pick up well. Is there a reason why this is happening? To give a more concrete example, in the BatchExample workflow, I included a function Record ID and value of Revenue is number 1 for Record ID when I run the BatchExample workflow. However, when I run the example.yxmd workflow after saving the BatchExample macro workflow, revenue is now at number 10 for Record ID. 

With the same dataset given above, I also tried to select the specified range (e.g. A1 to D5), the example.yxmd workflow runs with the full specified range. 

Edit: I did some changes, but when running the example.yxmd workflow, the result shows the duplicated results of Amsterdam, not showing other values from other tabs. It means that I am having running data which Cost 1 showing all Amsterdam result after including my formulas and other workflow functions.

[SPetrie · Accepted solution]

If you changed the file input in the macro to have the range, then it probably broke the action tool. It was looking for Amsterdam to be replaced by each item but the addition of a range would have changed what it was replacing.

It may be easier to add the range to the end of the file name you are passing to the macro instead of adding the range to the macro itself. That way the macro can still be used for other workflows where you may want the full range or a different range.

You can add the range into the formula tool before the macro.

Replace([FileName],"<List of Sheet Names>","`"+[Sheet Names]+"$A1:D5`")

If you are still having issues, you would need to upload a copy of your workflow for me to look at. Its hard to guess what may be going wrong without seeing the changes you have made.