merge different rows in the same (according to a commun attribute)

[kadoc]

Hello,

I would like to merge some rows in one (considering the same attribute) separated with semicolons

from this:

A r

A t

A g

A j

B f

B t
B g

C u

C i

To this:

A r; t; g; j

B f; t; g

C u; i

Can you help me please ?

Paul

[Anonymous · Accepted solution]

@kadoc,

This code should give you the expected result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclQoUorVAdElUDodSmeBaSeFNChdAqUh8s4KpVA6Uyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column.1", "Column.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column.1"}, {{"AllData", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [AllData][Column.2]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Result", each [Column.1] & " " & [Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Result"})
in
    #"Removed Other Columns"