Forum Discussion

bryantw's avatar
bryantw
New Member
2 years ago
Solved

Help with setting up a Pivot Column

Please help! I need the values in "Attribute" to become new column headers, and the data in the Values column to become the values under the new columns. No data should be changed to averages/counts....
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi bryantw,

    another approach.

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJJLEkFUr6JRckZCoY6CkYGRsZKsTpw+YBSoERiMUiNY0FBTiqynGNufmleCZChYqBnaoAsg2KqEV5TgxPzUsozkzOwG2yiZ24KlgkuLU7E616YAlxGw+SJNdsUv9lumcW4zDU00jMAhkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]),
    Attributes = List.Buffer(List.Distinct(Source[Attribute])),
    StepBack = Source,
    PivotedColumn = Table.Pivot(StepBack, Attributes, "Attribute", "Value", each _),
    Ad_Table = Table.AddColumn(PivotedColumn, "t", each Table.FromColumns(
      List.Combine(List.Transform(Attributes, (x)=> {Record.Field(_, x)} )),
      Attributes), type table),
    #"Removed Other Columns" = Table.SelectColumns(Ad_Table,{"Name", "t"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "t", Attributes),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi bryantw,

another approach.

 

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJJLEkFUr6JRckZCoY6CkYGRsZKsTpw+YBSoERiMUiNY0FBTiqynGNufmleCZChYqBnaoAsg2KqEV5TgxPzUsozkzOwG2yiZ24KlgkuLU7E616YAlxGw+SJNdsUv9lumcW4zDU00jMAhkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]),
    Attributes = List.Buffer(List.Distinct(Source[Attribute])),
    StepBack = Source,
    PivotedColumn = Table.Pivot(StepBack, Attributes, "Attribute", "Value", each _),
    Ad_Table = Table.AddColumn(PivotedColumn, "t", each Table.FromColumns(
      List.Combine(List.Transform(Attributes, (x)=> {Record.Field(_, x)} )),
      Attributes), type table),
    #"Removed Other Columns" = Table.SelectColumns(Ad_Table,{"Name", "t"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "t", Attributes),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"