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

     

     

     

     

Merleau's avatar
Merleau
Helper II
6 years ago

Hi Fowmy 

Thanks so much for the prompt response. This is helping me move towards the final goal.

Now, I need to sort the custom column and divide it into the two type"AM" and "PM", possibly using line break.

Thsi is a strong requirement that must be fulfilled.

I will try to get it right using your solution.

Thanks.

 

Fowmy's avatar
Fowmy
Super User
6 years ago

Merleau 

Hope this is what you are expecting:

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", "Name", "Type"}, {{"Program", each Text.Combine([Programs],"#(lf)"), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"ID", Order.Ascending}, {"Type", Order.Ascending}})
in
    #"Sorted Rows"


________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply

YouTube, LinkedIn