Measure/ DAX to filter column by text

[RichOB]

Hi, in this example, I have the raw data in Table 1

I need to split up the Involvement column so I can add the Victim and Perpetrator as their own columns on the same row.

How can I achieve how table 2 looks, please?

Table1

Incident_Number	Individual ID	Invovlement	City	Name	Type
Inc1	ID1	Perpetrator	Liverpool	Dave.D	Assuault
Inc1	ID2	Victim	Liverpool	Mark.M	Assuault
Inc2	ID3	Perpetrator	Liverpool	Grant.G	Assuault
Inc2	ID4	Victim	Liverpool	Paul.P	Assuault
Inc3	ID5	Perpetrator	Liverpool	Aaron.A	Harassment
Inc3	ID6	Victim	Liverpool	Thomas.T	Harassment

Table2

Incident_Number	City	Perpetrator_Name	Victim_Name	Type
Inc1	Liverpool	Dave.D	Mark.M	Assault
Inc2	Liverpool	Grant.G	Paul.P	Assault
Inc3	Liverpool	Aaron.A	Thomas.T	Overdose

Thanks for your help

[rohit1991]

Hi @RichOB 

Power Query Solution (No DAX Needed)
1. Load your Table1 into Power BI
2. Go to Transform Data (Power Query)
3. Select the Name column
4. Click on Transform >> Pivot Column
5. For the Values Column, choose "Name"
6. For the Pivot Column, choose "Involvement"
7. In the Advanced Options >> Use "Don't Aggregate" (if prompted)
8. Done! You’ll now see Victim and Perpetrator names as separate columns for each Incident_Number

[RichOB]

Hi, thanks for your reply. I tried it but it's giving me the error:

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

How can I rectify the null entries please?

[RichOB]

Amazing, thanks for your help!

[rohit1991 · Accepted solution]

Hi @RichOB 

Before performing the Pivot Column step, just add one extra step to remove the nulls from the Involvement column:
Here's how:
  1. In Power Query, select the Involvement column
  2. Go to the top ribbon >> Filter >> Uncheck (null)
  3. Now try the Pivot Column step again (as before)
This will ensure Power Query only uses valid text entries ("Victim" / "Perpetrator") and avoids the type conversion error.