Forum Discussion
Post PartisanMeasure/ DAX to filter column by text
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
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.
4 Replies
rohit1991Super User
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
RichOBHi, 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?- rohit1991
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.