Agroup and concatenate column with a common value

[Cramos]

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:

a	perro
a	gato
a	pájaro
b	enero
b	febrero
c	1
c	2
c	3
c	4

And I would like to get:

a	perro,gato,pájaro
b	enero,febrero
c	1,2,3,4

It is possible?

Thank you so much!

[ChemEnger]

@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]

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

[RavitPBI]

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

[RavitPBI]

Never mind, got it!

[MarcelBeug · Accepted solution]

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:

(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.

[ngadiez]

@MarcelBeug hi Marcel.

If I have this Table

ID	City	Country
1	Tokyo	Japan
1	Kyoto	Japan
2	New York	USA
3	Beijing	China
3	Jakarta	Indonesia
4	Kuala Lumpur	Malaysia

can I summarise to this

ID	City	Country
1	Tokyo, Kyoto	Japan
2	New York	USA
3	Beijing, Jakarta	China, Indonesia
4	Kuala Lumpur	Malaysia

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

[jalozanocampo]

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}})

[mgirou]

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

Thanks!

[JoshEnglish]

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]

Hi @Cramos 

this is not correct. I have the following table:

With DAX I can create a calculated table like this:

In a matrix visual I can use the following measure:

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

[Cramos]

It works perfectly! Thank you so much!