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:
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!
@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