Combine rows and add as a new column

[Anonymous]

Hi,

I have a table like the following:

Index	ID	Category
1	ID_1	A
2	ID_2	B
3	ID_2	C
4	ID_3	A
5	ID_4	F
6	ID_5	E
7	ID_5	R
8	ID_5	T
9	ID_6
10	ID_7	Q
11	ID_8	S
12	ID_8
13	ID_9	Z

I want to add a new column and combine rows of the category with the same ID into one cell. In the new cell, the combined categories are separated by a break (new line). So, the result is this:

Index	ID	Category	Category_new
1	ID_1	A	A
2	ID_2	B	B C
3	ID_2	C	B C
4	ID_3	A	A
5	ID_4	F	F
6	ID_5	E	E R T
7	ID_5	R	E R T
8	ID_5	T	E R T
9	ID_6
10	ID_7	Q	Q
11	ID_8	S	S
12	ID_8
13	ID_9	Z	Z

Can someone help me do that?

Thank you very much.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure or calculated column as below to get it, please find the attachment for the details.

1. Create a calculated column

Category_New = 
VAR _newcategory =
    CONCATENATEX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Category],
        UNICHAR ( 10 ),
        'Table'[Category]
    )
RETURN
    IF ( 'Table'[Category] = BLANK (), BLANK (), _newcategory )

2. Create a measure

Measure =
VAR _curid =
    SELECTEDVALUE ( 'Table'[ID] )
VAR _curcategory =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _newcategory =
    CONCATENATEX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = _curid ),
        'Table'[Category],
        UNICHAR ( 10 ),
        'Table'[Category]
    )
RETURN
    IF ( _curcategory = BLANK (), BLANK (), _newcategory )

Best Regards

[Anonymous]

@v-yiruan-msft 

Thanks a lot. Works very well.

Just one thing:

In these equations, the order changes alphabetically.

For example, if it is going to combine three rows of R A C, the result after concatenation is A C R.

Is there any modification to these equations so that it keeps the original order when concatenating? 

Thanks.

[v-yiruan-msft]

Hi @Anonymous ,

You can make a little change on the calculated column or measure to remove the fifth arugment orderBy_expression ",'Table'[Category]" in the function CONCATENATEX. Please find the attachment for the details.

Best Regards

[amitchandak]

@Anonymous , try like

concatenatex(Filter(allselected(Table), Table[ID] = max(Table[ID])), Table[Category], unichar(10))

[Anonymous]

Thanks @amitchandak 

Are you sure it is working?

I get this result!