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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors