Forum Discussion

bvbull200's avatar
bvbull200
Helper III
5 years ago
Solved

How to Pivot Column w/Different Number of Values

I don't know if that title accurately describes what I'm looking for, but here it goes.

 

I have a table with two columns.  The first column is a unique key - let's say a part #.  The second column is an attribute.  Some keys have 1 attribute, some keys have multiple attributes.  What I need to do is put each attribute in a column next to the key so that each key only takes up a single row.  For instance, I need this:

 

KeyAttribute
PartABCBlue
PartXYZ

Red

PartXYZRound
PartXYZSlim
Part123Black
Part123Square

 

To convert to:

 

KeyAttribute.1Attribute.2Attribute.3
PartABCBlue  
PartXYZRedRoundSlim
Part123BlackSquare 

 

Any help to this end is greatly appreciated!

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    bvbull200 , you can first group by key column and then extract attributes,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKnF0clbSUXLKKU1VitWBCEVERgGFglJT0EXyS/PQxYJzMnPhQoZGxmCzEpOz0cSCC0sTi4A2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Attribute = _t]),

    #"Grouped Rows" = Table.Group(
        Source,
        {"Key"},
        {{"Attr", each Table.Transpose(Table.SelectColumns(_,{"Attribute"}))}}
    ),
    #"Expanded Attr" = Table.ExpandTableColumn(#"Grouped Rows", "Attr", {"Column1", "Column2", "Column3"}, {"Attr.Column1", "Attr.Column2", "Attr.Column3"})
in
    #"Expanded Attr"

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    bvbull200 , you can first group by key column and then extract attributes,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKnF0clbSUXLKKU1VitWBCEVERgGFglJT0EXyS/PQxYJzMnPhQoZGxmCzEpOz0cSCC0sTi4A2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Attribute = _t]),

    #"Grouped Rows" = Table.Group(
        Source,
        {"Key"},
        {{"Attr", each Table.Transpose(Table.SelectColumns(_,{"Attribute"}))}}
    ),
    #"Expanded Attr" = Table.ExpandTableColumn(#"Grouped Rows", "Attr", {"Column1", "Column2", "Column3"}, {"Attr.Column1", "Attr.Column2", "Attr.Column3"})
in
    #"Expanded Attr"