Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
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.
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
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!
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?
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.