Forum Discussion
Solved
Restricted by data structure
Hi,
I'm unable to build a chart I would like as my 'values' data is in 3 separate columns rather than 1 column that is categorised by a companion column. This I realise is not the greatest of explanations so here's a quick current and desired:
Current:
| Country | Cost 1 | Cost 2 |
| France | 5 | 10 |
| Germany | 8 | 8 |
| UK | 9 | 6 |
| France | 9 | 6 |
| Germany | 9 | 10 |
| UK | 10 | 8 |
Desired:
| Country | Cost Type | Cost |
| France | 1 | 5 |
| Germany | 1 | 7 |
| UK | 1 | 5 |
| France | 1 | 9 |
| Germany | 1 | 5 |
| UK | 1 | 5 |
| France | 2 | 6 |
| Germany | 2 | 6 |
| UK | 2 | 10 |
| France | 2 | 10 |
| Germany | 2 | 7 |
| UK | 2 | 8 |
With the latter I can create a wider range of charts, e.g. waterfalls, and also drill into the data easier.
Is there a way to sort the data differently on loading it or alternatively am I missing something when wanting to create charts? I've always had this problem with Pivot Tables too!
Jossy
- Hi ,
no, you will not have problems with refreshing, because all transformation steps will be repeated every time you refresh you data model. I am happy, when this is the solution you needed ;)
Regards,
Lars
3 Replies
- Hi ,
when you have your table in your import dialog mark cost1 and cost2 and click on transform --> unpivot. That should take you to the desired table structure ;)
Hope that helped,
Regards
LarsGreat stuff Lars! You have no idea how revolutionary that's going to be for me!
Will I hit problems refreshing data as the data structures are now different?
Phil
- Hi ,
no, you will not have problems with refreshing, because all transformation steps will be repeated every time you refresh you data model. I am happy, when this is the solution you needed ;)
Regards,
Lars
