Forum Discussion

Merleau's avatar
Merleau
Helper II
6 years ago
Solved

Create a list field by merging linked fields

Hello, I have a table with multiple columns (more than 30) in no particular order. I need to merge 2 of these columns. For example, the original table is:   ID Type Programs Name ...
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    a shorter one which also uses the Type column, whose presence I had not yet noticed 😀

     

     

     

     

     

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

    group = Table.Group(Source,"ID", {"Type-Programs", each Text.Combine(List.Transform(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})[_],each "-"&_),"#(cr)")})
in
    group

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

there seems to be a lot of solutions to choose from.

Here is another one

 

 

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"}, {{"progrs", each List.Sort(List.Transform(_[Programs],each Text.End(_,2)&"-"&_))}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"progrs", each Text.Replace(Text.Replace(Text.Replace(Text.Combine(_,","),"AM,P","AM#(cr)P"),"AM-",""),"PM-","")})
in
    #"Extracted Values"

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    a shorter one which also uses the Type column, whose presence I had not yet noticed 😀

     

     

     

     

     

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

    group = Table.Group(Source,"ID", {"Type-Programs", each Text.Combine(List.Transform(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})[_],each "-"&_),"#(cr)")})
in
    group

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      and this the solution derived from imke solution to the other similar problem:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgekxgimxq0oNS8ZpsQIWYkxUCgAJO5YlJiUmRwAUWKMrMQQpiS4IDEPaFMADpvA4s75RamJeQFYbII72LMkMSczMQ+vg1E9hd3FUHNwORnT44aYSnQDckqLgSz3otTUbAQXoTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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(_,",")),
    #"Inserted Merged Column" = Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[PM], [AM], [#"AM-Plus"]}, "#(cr)"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"AM", "PM", "AM-Plus"})
in
    #"Removed Columns"