Forum Discussion

AilleryO's avatar
AilleryO
Memorable Member
3 years ago
Solved

Transpose and Group

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.

Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    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



4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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's avatar
      AlexisOlson
      Super User
      3 years ago

      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"