Merge data into one column from records with multiple entries.

[pezwi]

Hi,

I am trying to merge the data in one column in order to show all of our tagging and I'm wondering if it's possible because I can't seem to find a way or I'm searching for the wrong thing.

The current scenario shows the host name and each host could have multiple tags.  The results needed that I'm trying to figure out is it would show one host record and merge the Tag records into one column.

Current Scenario:			Results Needed:
Host Name	Tag			Host Name		New Column
Computer1	Prod			Computer1		Prod, Customer1
Computer1	Customer1			Computer2		UAT, Customer2, Application1
Computer2	UAT
Computer2	Customer2
Computer2	Application1

Any help would be greatly appreciated.

Thank you,

MIke

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLSgtSS0yVNJRCijKT1GK1UEVdC4tLsnPBbGRZYyAMqGOIRhiMNVGGDKOBQU5mcmJJZn5eZhGIUsmYjgBSTY0L18pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Host Name" = _t, Tag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Host Name", type text}, {"Tag", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Host Name"}, {{"New", each Text.Combine([Tag], ", #(lf)")}})
in
    #"Grouped Rows"

[mahoneypat]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to the Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLSgtSS0yVNJRCijKT1GK1UEVdC4tLsnPBbGRZYyAMqGOIRhiMNVGGDKOBQU5mcmJJZn5eUCjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Host Name" = _t, Tag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Host Name", type text}, {"Tag", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Host Name"}, {{"New", each Text.Combine([Tag], ", ")}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"New", type text}})
in
    #"Changed Type1"

Pat