How to consolidate tables?

[imthinizam]

Hi, 

I have 4 data set in an excel which shows the performance of different segments. I need to consolidate all together and need to differentiate each data set with a name in an additional column. Appreciate if you could help me to achieve this ( I have attached a sample of my dataset and the desired outcome)

[BonusCup]

I'm sure there are other ways of doing this.  I used a Text input to mimic your first two tables, your last two tables would be the same as my second path.

I used a Record ID and then a Select to change the data type from int to str then used a formula to change the record ID values from 1 -> 4 to A -> D.  I then transposed them and then renamed and re-ordered the columns and then unioned them together.

 

[jdminton]

Here is a workflow that does what you require. If the inputs are coming in Excel, you can use filenames or worksheet names to categorize the totals. I assumed these are in separate tables as well. If the data is all in the same table, you would do this a little differently. If you want to adjust quickly for one table of data, you can filter the file with the different segments to feed into each stream in the workflow.

[imthinizam]

Can you please attach the workflow?

[jdminton]

I'm not sure who you were talking to, but mine is attached. You need to download.

[imthinizam]

Apologies, I have my data table in the same excel. For i.e I have the product name in Column A, from Colum B - D overall data, from F - I its Retail data and so on.

[imthinizam]

So could you please amend your workflow to capture the data from the same excel?

[jdminton]

This is the quick way to adjust it.

[jdminton]

I updated this to include another method that is more flexible for future data in the event you will be adding more months to the same file and shifting the totals column. Take a look at the container at the bottom.