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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors