Manipulating Data by Tranposing from Multiple Sheets of Data

kv-defy

I have some enquiry about an issue to convert the dataset while having to import multiple excel sheets.

Currently my dataset is 

	2021	2020	Variance	... (more columns)	Variance %
Revenue
IT Cost
... (more rows)
Other Costs

I have successfully import multiple excel sheets and the result is like this:

Initial Outcome:

	2021	2020	Variance	... (more columns)	Variance %	Country
Revenue						London
IT Cost						London
... (more rows)						London
Other Costs						London
Revenue						Sydney
IT Cost						Sydney
... (more rows)						Sydney
Other Costs						Sydney
...						(others Countries, Berlin, Shanghai and Jakarta)

based on the help from here: 

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

However, I would like to change the dataset into this format ->

Desired outcome:

Year	Revenue	IT Cost	... (more rows)	Other Costs	Country
2021					London
2020					London
Variance					London
... (more columns)					London
Variance %					London
2021					Sydney
2020					Sydney
Variance					Sydney
... (more columns)					Sydney
Variance %					Sydney
...					(others Countries, Berlin, Shanghai and Jakarta)

I understand that the idea of the workflow is to separate the year/cost table and the country, transpose year/cost table and use the Join feature (join to the right) to merge the country. How do I exactly do this because I can't simply join because the number of fields in the country column is different?

Thank you so much!

binu_acs

@kv-defy one way of doing this with the transpose tool and cross tab tool. In my input file i used only one country. If the solution is not working for you please provide a sample file 

DataTransform_06_07.yxmd