Transpose and Group

[AilleryO]

Hi,

I'm struggling with a transposition/unpivoting problem.

My table looks like that :

Family	Product
F1	P1
F1	P2
F2	P3
F4	P4
F4	P5
F1	P6
F2	P7

and my required outcome would be :

F1	F2	F4
P1	P3	P4
P2	P7	P5
P6	P3

I managed to get all product values with a concatenate, but cannot find a way to have them in columns.

Any guess ?

Thanks for your help.

[AilleryO]

Thanks a lot that is beautiful 🙂

[ImkeF · Accepted solution]

Hello @AilleryO ,
with a bit of tweaking the Group-code, you can achieve it like this:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WcjNU0lEKMFSK1YGxjSBsIxDbGMI2AbFNkNimSOrNkNSbK8XGAgA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Family = _t, Product = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Family", type text}, {"Product", type text}}
  ), 
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Family"}, {{"Product", each _[Product]}}), 
  Custom1 = Table.FromColumns(#"Grouped Rows"[Product], #"Grouped Rows"[Family])
in
  Custom1

[AlexisOlson]

@ImkeF, I love this. Simple and elegant.

@AilleryO Here's a method involving concatenating and transposing that may be more similar to your original idea:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjNU0lEKMFSK1YGxjSBsIxDbGMI2AbFNkNimSOrNkNSbK8XGAgA=",BinaryEncoding.Base64),Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table  [Family = _t, Product = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Family"}, {{"Count", each Text.Combine([Product], "|")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter("|")),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter")
in
    #"Transposed Table"

[AilleryO]

Hi @AlexisOlson ,

Thanks for providing another solution that works perfectly as well.