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.

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.