Combine Rows in 1 Column when keeping the other columns the same

[Dexter1088]

Hi There,

Can anyone help me with combining rows in a column? 
I want the data to be the same in all the other columns.

Example: (Combine Office Column)

Name	Office	Phone
Doug	OH	111
Matt	PA	222
Steve	IN	333
Doug	MO	111
Matt	OH	222
Steve	CA	333
Doug	IN	111
Matt	MI	222
Steve	MO	333

Formatted to look like this:

Name	Office	Phone
Doug	OH, MO, IN	111
Matt	PA, OH, MI	222
Steve	IN, CA, MO	333

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

=CONCATENATEX(VALUES(Data[Office]),Data[Office],",")

=min(Data[Phone])

Hope this helps.

[Nathaniel_C]

Hi @Dexter1088 ,

Like this? Learned this from Matt Allington's book.

[Combine Office] = IF(HASONEVALUE(CombineOffice[Office]),VALUES(CombineOffice[Office]), CONCATENATEX(VALUES(CombineOffice[Office]),CombineOffice[Office],", "))

If this solves your question, please mark it as the solution.

@Nathaniel_C 

[Dexter1088]

Maybe I'm doing it wrong, but when I do that I get this

Name	Office	Phone	Combined Office
Doug	OH, MO, IN	111	OH, PA, IN, MO, CA, MI
Matt	PA, OH, MI	222	OH, PA, IN, MO, CA, MI
Steve	IN, CA, MO	333	OH, PA, IN, MO, CA, MI

It just shows all the possibilites on every row. Thanks!

[Nathaniel_C]

Hi @Dexter1088 ,

Are you building this in a measure, and if so what are you using to display this? I used your original data to build my table in Power BI.  I then created the measure from above and added it to the matrix shown above turning off the subtotals to get that picture.  How are you trying to display this?

If you are trying to place this in the original data table, it won't work as it violates data rules having one piece of data in each cell. 

If this solves your question, please mark it as the solution.

@Nathaniel_C