cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GaRaGeS
New Member

How to Pivot/Unpivot columns in Power BI data table?

I have a dataset as below

 

NameIDScience_MarksMaths_MarksScience_GradeMaths_Grade
ABC1232334CB
DEF4564433AB

 

I need to convert it to the below format (more charts-friendly)

 

NameIDSubjectMarksGrade
ABC123Science23C
ABC123Maths34B
DEF456Science44A
DEF456Maths33B

 

Any recommendations on how i can do this in Power BI?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@GaRaGeS 

 

This works with your sample data

Please see the attached file for steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyBpJgwtgESIDEnJRidaKVXFzdgGwTUzMQCZIyBilyhMjHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Science_Marks = _t, Maths_Marks = _t, Science_Grade = _t, Maths_Grade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}, {"Science_Marks", Int64.Type}, {"Maths_Marks", Int64.Type}, {"Science_Grade", type text}, {"Maths_Grade", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Subject", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Subject", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Name", "ID", "Subject", "Grade", "Marks"})
in
    #"Reordered Columns"

Regards
Zubair

Please try my custom visuals

View solution in original post

1 REPLY 1
Zubair_Muhammad
Community Champion
Community Champion

@GaRaGeS 

 

This works with your sample data

Please see the attached file for steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyBpJgwtgESIDEnJRidaKVXFzdgGwTUzMQCZIyBilyhMjHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Science_Marks = _t, Maths_Marks = _t, Science_Grade = _t, Maths_Grade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}, {"Science_Marks", Int64.Type}, {"Maths_Marks", Int64.Type}, {"Science_Grade", type text}, {"Maths_Grade", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Subject", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Subject", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Name", "ID", "Subject", "Grade", "Marks"})
in
    #"Reordered Columns"

Regards
Zubair

Please try my custom visuals

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.