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

     

     

     

     

ziying35's avatar
ziying35
Impactful Individual
6 years ago

Merleau 

let
    Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl)=>
       let grpinn = Table.Group(tbl, "Type", {"tt", each Text.Combine([Programs], ", ")})
       in  Text.Combine(Table.ToList(grpinn, each _{0}&"-"&_{1}), "#(lf)"),
    group = Table.Group(Source, {"ID", "Name"}, {"Type-Programs", fx })
in
    group