Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need Help on Data Transformation

Happy holidays everyone! Need help on transforming this data that comes from the client tool as follows :   Name 1.1 1.1 Score 1.2 1.2 Score 2.1 2.2 Score Name 1 Attendance 100% Qual...
  • bolfri's avatar
    3 years ago

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRciwpSc1LScxLTgVyDA0MdAwMVIGswNLEnMySSiDLwhQq5JiSkVqUClFoBlEXqwM1yQjdJFOYQQFF+SmlySWZZRDTYMLBPo5A0hxidmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"1.1" = _t, #"1.1 Score" = _t, #"1.2" = _t, #"1.2 Score" = _t, #"2.2" = _t, #"2.2 Score" = _t]),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Context", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Type", each if Text.Contains([Context],"Score") then "Score" else "Attribute"),
        #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Type]), "Type", "Value"),
        #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Pivoted Column", {{"Context", each Text.BeforeDelimiter(_, " "), type text}}),
        #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Name", "Context"}, {{"Attribute", each List.Max([Attribute]), type nullable text}, {"Score", each List.Max([Score]), type nullable number}})
    in
        #"Grouped Rows"

     

     

    Before:

     

    After: