Please help with transforming a table

Hi arb1782 

Please create a blank query and copy below codes into its Advanced Editor to check the result. Note that I remain only a outcome type column in the result and use original data's answervalue for it. I think the result is close to what you need. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZLBbsIwDEB/xeoZCSgb0o4TaNpOY+KI0OQVs1hKYpSkhf79AhTItEnd2lNsJ322n7paZQtyXiy8zLNB9iTOxGOOgeIxExucaIsmyTR+kI4pWr8nV6EuKVsPVtk41l7LUIghD8tCHNtPeCvJBxZrkd2RkU+Go/EwH+XH14pQB1XH6PkaLelQotY1nC9P5PxP5GlCxiJwdaw+fg9gz0FBUARbNKzrfwz+kOA9bknJScoyhpDEoQa23TqkanyjoVEk549/+qnhdjUTs9OMtmjWNLThAjUERxgM2dBL5q1PozKZSaGjTbMw0GFHjimO4XvqTTpcLMMvtaGPvyHHrclW7MReV+2gXcl7UToXCT6+P1k9p3DJp10ttpLvu+pqH/quh4+do4ql9BfWosmv8El3I63svIeTVvg4W6+/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person ID", Int64.Type}, {"Form", type text}, {"Date", type text}, {"Controlname", type text}, {"Controllabel", type text}, {"answervalue", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith([Controlname], "outcome") then "outcome" else if Text.EndsWith([Controlname], "currentscore") then "currentscore" else if Text.EndsWith([Controlname], "previousscore") then "previousscore" else "type"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "answervalue"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Person ID", "Form", "Date"}, {{"Outcome type", each List.Max([type]), type nullable text}, {"Outcome description", each List.Max([outcome]), type nullable text}, {"Previous score", each List.Max([previousscore]), type nullable text}, {"Current score", each List.Max([currentscore]), type nullable text}})
in
    #"Grouped Rows"

Result:

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.