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

10 REPLIES 10
Burak317
Frequent Visitor

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)

View solution in original post

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

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors