Forum Discussion

ringocheng618's avatar
ringocheng618
Frequent Visitor
5 years ago
Solved

Pivot/Unpivot Messy Data

Hello I have given this messy data as attached:      What if I want to convert this into like the following, what should I do?      
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    ringocheng618 , you might want to try,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSQaFidaKVAlJLUouAAr6JRZVAKqQoMQ8s7pJakFhUkpuaVwIUxcUBKQTqyMzLzEsHyjgHAwmPILBwoCGQDSNAAkZApjkQm0KkQVwYARIwATJNENLGIBkoARIA6TQDmwCWBqmEESABY1TdpiAZKIEsDbQqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Transformed Table" = Table.Combine(List.Transform(Table.ToColumns(Source), each Table.PromoteHeaders(Table.FromColumns(List.Split(_,2))))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transformed Table", {"Name", "Department"}, "Q#", "Score")
in
    #"Unpivoted Other Columns"

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

ringocheng618 , you might want to try,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSQaFidaKVAlJLUouAAr6JRZVAKqQoMQ8s7pJakFhUkpuaVwIUxcUBKQTqyMzLzEsHyjgHAwmPILBwoCGQDSNAAkZApjkQm0KkQVwYARIwATJNENLGIBkoARIA6TQDmwCWBqmEESABY1TdpiAZKIEsDbQqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Transformed Table" = Table.Combine(List.Transform(Table.ToColumns(Source), each Table.PromoteHeaders(Table.FromColumns(List.Split(_,2))))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transformed Table", {"Name", "Department"}, "Q#", "Score")
in
    #"Unpivoted Other Columns"