Forum Discussion
Merge values from rows into one row
Hi jaryszek ,
Please try:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLcjJr8xNzSsp9k0sUIrViVYywy5sjF3Y0NrI2tjaxNoUu7QRUNjRyRnMNoGxYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter"), #"Grouped Rows" = Table.Group( #"Removed Duplicates", {"Column2"}, {{"Combined", each Text.Combine(List.Sort(List.Distinct([Column1])), ";")}}) in #"Grouped Rows"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can do that by creating a Group By step on the Home tab, and then modifying the code in the Formula Bar to match this example. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLcjJr8xNzSsp9k0sUIrViVYywy5sTLzqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Combined", each Text.Combine(List.Sort(List.Distinct([Column1])), ";")}})
in
#"Grouped Rows"
Pat
Thank you it is working!
One more thing.
Sometimes i will have already created strings as Value like "1;2;3;4;5":
1 DeploymentsMap 6 DeploymentsMap 3 DeploymentsMap 1;2;3;4;5 DeploymentsMap And as result i would like to get
1;2;3;4;5;6 DeploymentsMap
so check if in list value exists, if not append. So check if there is alreadylist, if yes to not have duplicates -->appendto get only uniqes.
what i get using provided formula:
Column2Combined
DeploymentsMap 1;1;2;3;4;5;3;6
Can you please advise also?Best,
Jacek
