Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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)
Is there a way to achieve this, not taking blanks into account?
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!
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.
@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.
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:
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)
It works perfectly! Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
86 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |