Separate names in columns based on Role in separate column.

[inglexjc]

Current table V_TRAILS_CASE_CATEGORY shows:

CAS_ID	D_STF_PERS_NME	ROLE
1234	One, Tech	Primary
1235	Two, Tech	Secondary
1236	Three, Tech	Primary
1237	Two, Tech	Secondary

I would like it to show as:

CAS_ID	Primary	Secondary
1234	One, Tech
1235		Two, Tech
1236	Three, Tech
1237		Two, Tech

[marcelsmaglhaes · Accepted solution]

Hey @inglexjc 

In Power Query you should select the ROLE column in your query, and then, in Transform menu, choose the Pivot Column buton and copy the configuration of the image bellow.

After you fill the option, click in OK. You will see the result you are looking for.

[ExcelMonke]

You could try the following, by creating a new table. Go to modeling, create table with the following DAX:

Union(
SELECTCOLUMNS(Filter('Table','Table'[D_STF_PERS_NME]="One, Tech"),"CAS_ID",'Table'[CAS_ID],"Primary","One, Tech","Secondary",""), 
//Looks for all D_STF_PERS_NME with "One, Tech" and creates a column named "Primary" and for each matching ID will return a value of "One, Tech", and blank in the "Secondary" column.

SELECTCOLUMNS(Filter('Table','Table'[D_STF_PERS_NME]="Three, Tech"),"CAS_ID",'Table'[CAS_ID],"Primary","Three, Tech","Secondary",""), //Looks for all D_STF_PERS_NME with "Three, Tech" and creates a column named "Primary" and for each matching ID will return a value of "Three, Tech", and blank in the "Secondary" column.

SELECTCOLUMNS(Filter('Table','Table'[D_STF_PERS_NME]="Two, Tech"),"CAS_ID",'Table'[CAS_ID],"Primary","","Secondary","Two, Tech")//Looks for all D_STF_PERS_NME with "Two, Tech" and creates a column named "Primary" and for each matching ID will return a value of Blank, and "Two, Tech" in the "Secondary" column.

)

Hope this helps!

[inglexjc]

I changed the names of the D_STF_PERS_NME to be One, Tech; Two, Tech; Three, Tech to protect PII.  what if there are REAL names that are different listed under D_STF_PERS_NME?

[ExcelMonke]

Hmm... you could try switching the filter clause to look at the role column and the selectcolumns clauses to return the D_STF_PERS_NME rather than a static value, although I have not tried this out myself yet.