cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AS31 Member
Member

Merging queries based on a specific column

Hello Everyone,

 

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

 

 

House        Name

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

 

Now what I'm trying to achieve is:

 

House         Name

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

Accepted Solutions
Super User
Super User

Re: Merging queries based on a specific column

Hi @AS31

 

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], ", " ) )
)
Super User
Super User

Re: Merging queries based on a specific column

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


3 REPLIES 3
Super User
Super User

Re: Merging queries based on a specific column

Hi @AS31

 

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], ", " ) )
)
Super User
Super User

Re: Merging queries based on a specific column

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
AS31 Member
Member

Re: Merging queries based on a specific column

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