Convert exam table in PowerQuery

[gargamalebarbosa]

Hi,
How can I convert my table in PowerQuery like below image ?

 

[johnny51981]

This was the best that I am able to do, since I'm not sure how dynamic you are needing this to be. So with the way this currently is, your score columns will be populated as text, rather than number.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Lesson", type text}, {"Point", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Student", "Lesson"}, {{"Point", each Text.Combine([Point],"/"), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Point", Splitter.SplitTextByDelimiter("/", QuoteStyle.None))
in
    #"Split Column by Delimiter"

 

[gargamalebarbosa]

Perfect ! thank you so much..

 

[alansidman]

Here is another PQ solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Student"}, {{"Data", each _, type table [Student=text, Lesson=text, Point=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Lesson", "Point", "Index"}, {"Lesson", "Point", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Index", each "Point " & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Point")
in
    #"Pivoted Column"

 

[gargamalebarbosa]

Here is another PQ solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Student"}, {{"Data", each _, type table [Student=text, Lesson=text, Point=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Lesson", "Point", "Index"}, {"Lesson", "Point", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Index", each "Point " & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Point")
in
    #"Pivoted Column"

Thank you for your answer. But it did not work right for me. Maybe i did somethings wrong.

 

[alansidman]

But it did not work right for me. Maybe i did somethings wrong.

This statement does not help me to help you. Need for you to be mores specific for me to advise you