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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User
Super User

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
Super User
Super User

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors