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

try this

 

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(_[Programs],(x)=>Text.End(x,2))}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"progrs", each let rigth=Text.AfterDelimiter(Text.Combine(_, ","),"AM,",{0,RelativePosition.FromEnd}) in Text.BeforeDelimiter(Text.Combine(_, ","),rigth)&"#(cr)"& rigth})
in
    #"Extracted Values"