Different Columns on Row level as columns

[SteveDesmedt]

Hi all, 

I have following problem in transforming my data.

Table 1

Entry No.	Cost Code	Cost Amount	% Cost	Cost Payed
1	A00	100	21	21
1	B00	200	0	0
1	C00	1000	6	60
2	A00	500	21	105
2	B00	750	2.96	22,2
3	B00	300	0	0
4	C00	500	9	45

What i would like to achieve is the following : 

Entry No.	Cost Code A00	% Cost A00	Cost Payed A00	Cost Code B00	% Cost B00	Cost Payed B00	Cost Code C00	% Cost C00	Cost Payed C00
1	100	21	21	200	0	0	1000	6	60
2	500	21	105	750	2.96	22,2	0	0	0
3	0	0	0	300	0	0	0	0	0
4	0	0	0	0	0	0	500	9	45

Can someone help with this please ?  I do not achieve in transforming multiple columns

Kind regards

steve

[spinfuzer · Accepted solution]

1) Select Entry No. and Cost Code.  Right Click and Unpivot Other Columns

2) Select Attribute and then cost code.  Next Right Click and click merge.  Seperator is space in the example.

3) Select Column Name of Merged Column.  Transform Tab --> Pivot Column --> Values Column = Value.  Aggregate Sum.

If you want to replace to null values with 0 then edit your last step from List.Sum to each List.Sum(_) ?? 0.  Or just select all of them and do a replace value null with 0 with the GUI.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1BCsAwCAT/knMoamJLjm2fEfL/b8S4xEIPu4KDY++JU043kTV7C6NGBnuw9UY2effVGucKmIRRPyOTBoTyUodHW6ciWZyX4OX3ssZLaJulmnRM", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry No." = _t, #"Cost Code" = _t, #"Cost Amount" = _t, #"% Cost" = _t, #"Cost Payed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry No.", Int64.Type}, {"Cost Code", type text}, {"Cost Amount", Int64.Type}, {"% Cost", type number}, {"Cost Payed", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cost Code", "Entry No."}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Cost Code"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"