cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ringocheng618
Frequent Visitor

Pivot/Unpivot Messy Data

Hello I have given this messy data as attached: 

 

Screen Shot 2021-03-01 at 7.01.17 PM.png

 

What if I want to convert this into like the following, what should I do?

 

Screen Shot 2021-03-01 at 7.20.46 PM.png

 

 

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

@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"

Screenshot 2021-03-02 091942.png

View solution in original post

2 REPLIES 2
CNENFRNL
Super User III
Super User III

@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"

Screenshot 2021-03-02 091942.png

View solution in original post

Vera_33
Solution Sage
Solution Sage

Hi @ringocheng618 

 

I believe there are other ways to do it. Paste the code in Advanced Editor, see the output

 

Vera_33_0-1614667339590.png

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8kvMTVXSQaFidaKVAlJLUouAAr6JRZVAKqQoMQ8s7pJaUALkI1MgYaB8Zl5mXjpQzDkYSHgEgYUDDYFsGAESMAIyzYHYFCIN4sIIkIAJkGmCkDYGyUAJkABIpxnYBLA0SCWMAAkYo+o2BclACWRpoFWxAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Field 1" = _t, #"Field 2" = _t, #"Field 3" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Field 1", type text}, {"Field 2", type text}, {"Field 3", type text}}
  ),
  #"Transposed Table" = Table.Transpose(#"Changed Type"),
  #"Grouped Rows" = Table.Group(
    #"Transposed Table",
    {"Column1", "Column2", "Column3", "Column4"},
    {{"allrows", each Table.Skip(Table.Transpose(_), 4), type table}}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows",
    "Custom",
    each [
      a = Table.AddIndexColumn([allrows], "Index", 0, 1, Int64.Type),
      b = Table.AddColumn(a, "Custom", each Number.RoundDown([Index] / 2)),
      c = Table.Group(b, {"Custom"}, {{"Q", each Table.Transpose(Table.FromList(_[Column1]))}})
    ][c]
  ),
  #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Q"}, {"Q"}),
  #"Expanded Q" = Table.ExpandTableColumn(
    #"Expanded Custom",
    "Q",
    {"Column1", "Column2"},
    {"Column1.1", "Column2.1"}
  ),
  #"Added Custom1" = Table.AddColumn(#"Expanded Q", "Custom", each Text.At([Column1.1], 1)),
  #"Renamed Columns" = Table.RenameColumns(
    #"Added Custom1",
    {{"Column2", "Name"}, {"Column4", "Dept"}, {"Custom", "Q#"}, {"Column2.1", "Score"}}
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Renamed Columns",
    {"Name", "Dept", "Q#", "Score"}
  )
in
  #"Removed Other Columns"

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors