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

[GaRaGeS]

I have a dataset as below

Name	ID	Science_Marks	Maths_Marks	Science_Grade	Maths_Grade
ABC	123	23	34	C	B
DEF	456	44	33	A	B

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

Name	ID	Subject	Marks	Grade
ABC	123	Science	23	C
ABC	123	Maths	34	B
DEF	456	Science	44	A
DEF	456	Maths	33	B

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

[Zubair_Muhammad · Accepted solution]

@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"