Reply
Member
Posts: 61
Registered: ‎11-21-2017
Accepted Solution

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

Accepted Solutions
Highlighted
AlB Super Contributor
Super Contributor
Posts: 1,185
Registered: ‎11-12-2018

Re: Merging queries based on a specific column

[ Edited ]

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

View solution in original post

Super User
Posts: 10,526
Registered: ‎07-11-2015

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!


View solution in original post


All Replies
Highlighted
AlB Super Contributor
Super Contributor
Posts: 1,185
Registered: ‎11-12-2018

Re: Merging queries based on a specific column

[ Edited ]

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
Posts: 10,526
Registered: ‎07-11-2015

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!


Member
Posts: 61
Registered: ‎11-21-2017

Re: Merging queries based on a specific column

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