transposing

[Evandam]

hi,

I have a table that translates GL accounts to ratio's. The table looks like this:

Ratio code	GL accounts
1400	<4000> + <4010> + <4020> + <4030>
1520	(<1400> - <1410>) - <5000> +<6000>
1600	<4070> + <4035> - <4080>

the actual table is more than 30 rows in length and some ratio codes are built up of more than 8 GL accounts.

As you can see ratio code 1400 is the sum of GL accounts 4000, 4010, 4020 and 4030. 

I would like to use this information in PBI to generate visuals based in ratio code.

I think I need to do some transforming in order to use this data. I think that the following structure/layout is more usefull:

Ratio code	GL account
1400	4000
1400	4010
1400	4020
1400	4030
1520	1400
1520	1410

etc...

when i have this table i can calculate, for instance the reported amount via IF statements.

Any thoughts on this how to execute?

Or maybe other solutions that would be better in this case?

Thanks 

Erwin

[vanessafvg · Accepted solution]

you should be able to use the unpivot, however i am unsure what your source data looks like? is  it the top pic?