Forum Discussion

pezwi's avatar
pezwi
Frequent Visitor
5 years ago
Solved

Merge data into one column from records with multiple entries.

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 NameTag  Host Name New Column
Computer1Prod  Computer1 Prod, Customer1
Computer1Customer1  Computer2 UAT, Customer2, Application1
Computer2UAT     
Computer2Customer2     
Computer2Application1     

 

Any help would be greatly appreciated.

 

Thank you,

 

MIke

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

     

     

    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"

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

    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"