Merging queries based on a specific column

[Anonymous]

Hello Everyone,

Can someone please help me with the below scenario. I have a table which contains two columns like below.

House        Name

1	ABC
1	DEF
1	GHI
2	JKL
2	MNO
3	PQR
4	STU
4	VWX

Now what I'm trying to achieve is:

House         Name

1	ABC, DEF, GHI
2	JKL, MNO
3	PQR
4	STU, VWX

[AlB · Accepted solution]

Hi @Anonymous

You can create a new calculated table as follows, where Table1 is the upper table you show:

NewTable =
ADDCOLUMNS (
    DISTINCT ( Table1[House] ),
    "Name", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Name] ), Table1[Name], ", " ) )
)

[Greg_Deckler · Accepted solution]

Here is a Power Query way to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0claK1YGwXVzd4Gx3D08w2wjI9vL2gbN9/fzBbGMgOyAwCMw2AbKDQ0Lh7LDwCKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [House = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"House", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"House"}, {{"Name", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Name],"Name"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name"})
in
    #"Removed Columns"

[Anonymous]

Thank you both @AlB @Greg_Deckler .. It worked quite well.

[AlB · Accepted solution]

Hi @Anonymous

You can create a new calculated table as follows, where Table1 is the upper table you show:

NewTable =
ADDCOLUMNS (
    DISTINCT ( Table1[House] ),
    "Name", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Name] ), Table1[Name], ", " ) )
)