Concatenate Columns If Same ID

Anonymous

Not applicable

6 years ago

To better understand what I’m doing, below are two tables.

Using this formula, I was able to concatenate the different paths the residents took:

Continuum = IF ( NOT ( ISBLANK ( SELECTEDVALUE ( Episode[ResidentSys] ) ) ), CONCATENATEX( RELATEDTABLE( 'Unit Stay Table' ), 'Unit Stay Table'[Unit], ", " ) )

The table output that I got looks similar to this (I am using the matrix visualization):

Resident Number	Paths
1	L
2	L, L, A, A
3	R, L, A
4	A, I
5	A, I
6	I
7	I
8	I
9	A
10	A
11	L, R
12	L, R

I want to be able to do a “count” of the different unique paths to get an output like this:

Path	Number of Residents
L	1
L, L, A, A	1
R, L, A	1
A, I	2
I	3
A	2
L, R	2

Thank you so much for all your help!! I really appreciate it 😊

Forum Discussion

Related content

Concatenate 3 columns

create custom column concatenated

Power query - concatenate text columns

Concatenate lists ;

Concatenate multiple columns to a single column