Table transformation

[matal4]

Hi,

I have a table in PowerQuery that is built in a following way: 

Name	Primary Cost Center	Secondary Cost Center	Primary FTE	Secondary FTE
Employee 1	CC1	CC2	0.5	0.5
Employee 2	CC1		1

I would like to have a query showing data in layout like this:

CC1	1.5
CC2	0.5

Do you have any tips how to do it in a smart way? Do I need to transform the table?

[Anonymous · Accepted solution]

@matal4 try the following

1) Duplicate the tables and call it lets say table2

2) rename primary cost center & primary fte cols to cost center and fte  in orig table

3) delete secondary cost center & secondary fte cols from orig table

4) group by fte by cost center in orig table

5) rename secondary cost center & secondary  fte cols to cost center and fte  in table2

6) delete  primary cost center & primary fte cols from table2

7) group by fte by cost center in table2

😎 union these tables which have been grouped by cost center