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

Agroup and concatenate column with a common value

Hi!

 

Sorry, this has probably been asked before but I have not been able to find it.

 

I have to columns, column 1 have repeated values and column 2 unique values. I would like to agrupate the repated valums from column 1 and concatenate the values in column 2.

 

 

I have something like:

 

aperro
agato
apájaro
benero
bfebrero
c1
c2
c3
c4

 

And I would like to get:

 

aperro,gato,pájaro
benero,febrero
c1,2,3,4

 

It is possible?

 

Thank you so much!

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

In the Query Editor, you can use the Group By option.

First choose some aggregation for column 2 to create base code, e.g. Sum:

 

Group By 4.png

 

(ignore any errors) and adjust the generated code to:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Column2 Values", each Text.Combine([Column2],","), type text}})
in
    #"Grouped Rows"

Notice that Column2 is typed as text.

 

Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
ChemEnger
Advocate III
Advocate III

@MarcelBeug,

 

I know this is an old thread but it helped me out no end, thanks!  For others that stumble across this reply, there is an Idea but with hardly any votes: Microsoft Idea  · Incorporate the Concatenate operator to the Group By function (powerbi.com)

Anonymous
Not applicable

Is there a way to achieve this, not taking blanks into account? 

RavitPBI
Frequent Visitor

Hi, seems like this solution doesn't work for DirectQuery mode. Is there a way we can implement this grouping when using DirectQuery mode?

Never mind, got it!

MarcelBeug
Community Champion
Community Champion

In the Query Editor, you can use the Group By option.

First choose some aggregation for column 2 to create base code, e.g. Sum:

 

Group By 4.png

 

(ignore any errors) and adjust the generated code to:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Column2 Values", each Text.Combine([Column2],","), type text}})
in
    #"Grouped Rows"

Notice that Column2 is typed as text.

 

Specializing in Power Query Formula Language (M)

@MarcelBeug hi Marcel.

 

If I have this Table

IDCityCountry
1TokyoJapan
1KyotoJapan
2New YorkUSA
3BeijingChina
3JakartaIndonesia
4Kuala LumpurMalaysia

 

can I summarise to this

IDCityCountry
1Tokyo, KyotoJapan
2New YorkUSA
3Beijing, JakartaChina, Indonesia
4Kuala LumpurMalaysia

 

The idea is to remove repetition, instead of Japan, Japan. It will become Japan.

Hi @ngadiez 

 

This woerked for me, the clave was List.Distict:

Table.Group(#"Columnas quitadas", {"City"}, {{"Country", each Text.Combine(List.Distinct([Country), ","), type text}})

Is there a way to accomplish this in DAX? trying to do this, but in DAX because its from a calculated table.

 

Thanks!

This is not possible in DAX, as far as I've tried. Neither SUMMARIZE or GROUP_BY or SUMMARIZECOLUMNS makes this available. You can count the rows, count the unique values, even, but you cannot extract the text from the columns and treat them like rows.

FrankAT
Community Champion
Community Champion

Hi @Cramos 

this is not correct. I have the following table:

03-05-_2021_17-55-20.png

 

With DAX I can create a calculated table like this:

 

03-05-_2021_17-56-52.png

 

In a matrix visual I can use the following measure:

 

03-05-_2021_17-58-02.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

It works perfectly! Thank you so much!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors