Forum Discussion

Merleau's avatar
Merleau
Helper II
6 years ago
Solved

Pivoting a column and merging linked fields

Hello,

I have a table with multiple columns (more than 30) in no particular order. I need to create multiple columns by pivoting one of these columns. And populate them with associated information coming from another column.

 

The original table is:

 

 

ID

Type

Programs

Name

ID1

AM

EnglishAM

ID1-name

ID1

PM

SpanishPM

ID1-name

ID1

AM

ItalianAM

ID1-name

ID1

AM

FrenchAM

ID1-name

ID2

AM

FrenchAM

ID2-name

ID2

PM

CoreanPM

ID2-name

ID2

AM

EnglishAM

ID2-name

ID3

PM

ArabicPM

ID3-name

ID3

PM

ItalianPM

ID3-name

ID1

AM-Plus

GreekAM-Plus

ID1-name

 

After creating the columns, I would like to obtain the table below.

 

ID

AM

PM

AM-Plus

Name

ID1

EnglishAM, ItalianAM, FrenchAM

SpanishPM

GreekAM-Plus

ID1-name

ID2

FrenchAM, EnglishAM

CoreanPM

 

ID2-name

ID3

 

ArabicPM, ItalianPM

 

ID3-name

 

Can somebody pls help?

 Thank you

Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Merleau ,

    you can pivot on the Type-column and use a Text.Combine-function as the aggregation function on the "Programs"-column.
    Create a new query and paste this code into the advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgemJgAkHlyQmAdUE4BDDURvSWJOZmIeLnPA4m5FqXnJWK0ywqrECF0J2AXO+UWpiXkBOJRg8RSKGmOYMY5FiUmZyVBjjLEqgfoJmxqIn3QDckqLgSz3otTUbAQX4blYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs", each Text.Combine(_, ", "))
in
    #"Pivoted Column"

     

    Enclosing a file a well.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    I'm not sure I understand this request correctly.

    I guess this modification of this expression:

     

    Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs])

     

    to

     

    Table.AddColumn(#"Grouped Rows", "Custom", each List.Sort([Count][Programs]))

7 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Merleau ,

    you can pivot on the Type-column and use a Text.Combine-function as the aggregation function on the "Programs"-column.
    Create a new query and paste this code into the advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgemJgAkHlyQmAdUE4BDDURvSWJOZmIeLnPA4m5FqXnJWK0ywqrECF0J2AXO+UWpiXkBOJRg8RSKGmOYMY5FiUmZyVBjjLEqgfoJmxqIn3QDckqLgSz3otTUbAQX4blYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs", each Text.Combine(_, ", "))
in
    #"Pivoted Column"

     

    Enclosing a file a well.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      it looks very similar to a recently treated problem

       

       

      let
    Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),

    group = Table.Group(Source, "ID", {"Type-Programs", each Table.PromoteHeaders(Table.Transpose(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})))}),
    tc=Table.Combine(group[#"Type-Programs"])
in
    tc

       

       

       

      • Merleau's avatar
        Merleau
        Helper II
        6 years ago

        I solved it using the code below.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID", "Type"}, {{"Count", each _, type table [ID=text, Type=text, Programs=text, Name=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each [Count][Name]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Custom")
in
    #"Pivoted Column"

         

        But ImkeF's is so much cleaner.

        I will accept it as a solution.

         

        Thank you so much for all your contribution.