cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elinady Frequent Visitor
Frequent Visitor

Combine row values by another colum value in the Power Quesry Editor

Hi,

 

Is there a way to combine text values in multiple rows based on the value in another column in the Query editor? For example, I have the following table:

 

Column 1                  Column 2

03-02                         5.6.1.1.22

03-02                         5.7.1.1.11

03-02                         5.6.7.1.15

 

What I am trying to achieve is the following:

 

Column 1                 Column 2

03-02                       5.6.1.1.22; 5.7.1.11; 5.6.7.1.15

 

Using the group function doesn't work for this.

 

I can to this with a measure using the Concatenaex and Value functions however I need the table in the Query editor insteat.

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Combine row values by another colum value in the Power Quesry Editor

@elinady Please try this as a "New Table"

 

Test152Out = SUMMARIZE(Test152GroupConcat,Test152GroupConcat[Column1],"Result",CONCATENATEX(Test152GroupConcat,Test152GroupConcat[Column2],";"))

image.png

 

In Power Query, please try this..

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNTBS0lEy1TPTMwRCIyOlWB1kYXOwsKEhmrAZRMJUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Grouped", each Text.Combine([Column2],";"), type text}})
in
    #"Grouped Rows"

image.png



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

Proud to be a Datanaut !





4 REPLIES 4
Super User
Super User

Re: Combine row values by another colum value in the Power Quesry Editor

@elinady Please try this as a "New Table"

 

Test152Out = SUMMARIZE(Test152GroupConcat,Test152GroupConcat[Column1],"Result",CONCATENATEX(Test152GroupConcat,Test152GroupConcat[Column2],";"))

image.png

 

In Power Query, please try this..

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNTBS0lEy1TPTMwRCIyOlWB1kYXOwsKEhmrAZRMJUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Grouped", each Text.Combine([Column2],";"), type text}})
in
    #"Grouped Rows"

image.png



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

Proud to be a Datanaut !





elinady Frequent Visitor
Frequent Visitor

Re: Combine row values by another colum value in the Power Query Editor

Hi, thanks - i tried the Power Query Editor solution however the example I gave is just a small part of the table - the complete table is over 1000 rows with many different values.  Also, the values may change in the future. How would that work?

elinady Frequent Visitor
Frequent Visitor

Re: Combine row values by another colum value in the Power Quesry Editor

One more question - if I use the calculated table option:

 

"Test152Out = SUMMARIZE(Test152GroupConcat,Test152GroupConcat[Column1],"Result",CONCATENATEX(Test152GroupConcat,Test152GroupConcat[Column2],";"))"

 

How can I split the "Result" in separate columns by the ";" separator?

 

So to end up with something like this (Please note the numver of values may be different for each Column 1 value):

 

Column 1           Column 2           Column 3           Column 4

03-02                 5.6.1.1.22            5.7.1.1.11            5.6.7.1.15

03-01                 5.5.1.1.1              -                          -

03-03                 5.6.1.1.17            5.6.7.1.14            -

 

Thank you!

Community Support Team
Community Support Team

Re: Combine row values by another colum value in the Power Quesry Editor

HI @elinady,

 

>>How can I split the "Result" in separate columns by the ";" separator?

You can select 'custom' option to use custom character as split separator.

1.PNG

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,635 guests
Please welcome our newest community members: