Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SteveDesmedt
Frequent Visitor

Different Columns on Row level as columns

Hi all, 

 

I have following problem in transforming my data.

 

Table 1

Entry No.Cost CodeCost Amount% CostCost Payed
1A001002121
1B0020000
1C001000660
2A0050021105
2B007502.9622,2
3B0030000
4C00500945

 

What i would like to achieve is the following : 

 

Entry No.Cost Code A00% Cost A00Cost Payed A00Cost Code B00% Cost B00Cost Payed B00Cost Code C00% Cost C00Cost Payed C00
11002121200001000660
2500211057502.9622,2000
300030000000
4000000500945

 

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

 

Kind regards

 

steve

1 ACCEPTED SOLUTION
spinfuzer
Solution Sage
Solution Sage

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"

 

 

 

 

View solution in original post

1 REPLY 1
spinfuzer
Solution Sage
Solution Sage

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"

 

 

 

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.