Restricted by data structure

[Jossy]

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

[LarsSchreiber]

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
Lars

[Jossy]

Great 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

[LarsSchreiber · Accepted solution]

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