Brian Julius Excel BI Power Query Challenge 11

gistfile1.txt

let
    Source = #"Marks Raw",
    Headers = List.Select( Table.ColumnNames(Source), each not Text.Contains( _, "Subject") ),
    
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, Headers, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"ID", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each ([Value] <> "") and ([Value.1] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByDelimiter("Subject", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.1.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.2", "Subject"}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Attribute.1", Splitter.SplitTextByDelimiter("Marks", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.1.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.1.2", "Marks"}}),
    Filter = Table.SelectRows( #"Renamed Columns1", each [Subject] = [Marks]),
    #"Removed Columns2" = Table.RemoveColumns(Filter,{"Subject", "Marks"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Value]), "Value", "Value.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Physics", Int64.Type}, {"Chemistry", Int64.Type}, {"Maths", Int64.Type}, {"Biology", Int64.Type}, {"Geography", Int64.Type}, {"English", Int64.Type}})
in
    #"Changed Type"