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

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"