Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JJH_
Frequent Visitor

Create conditional column by delimiting at different points based on keywords

I am parsing through reported crime dataset where the [Attribute] column contains details on the type of crime and what type of data point it is.

 

Using Assault as an example, there is a datapoint for Assaults in 2020 (counts how many assaults in 2020) and Assault Rate in 2020 (counts how many assaults in 2020 for every 100K people).


I'd like to create a new conditional column that would parse the [Attribute] column to either state Assault or Assault_Rate. 

Thinking a formula such as this would work:

If [Attribute] contains "Rate" then extract all except for the last 4 characters ELSE delimit before "_"

 

AttributeTypeofCrime
Assault_2020Assault
Assault_Rate2020Assault_Rate

 

Sample Data

JJH__0-1624811026379.png

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

2 REPLIES 2
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1624850438656.png

 

Anonymous
Not applicable

I would type this in the formula bar:

= Table.AddColumn(PriorStepName, "TypeOfCrime", each if Text.Contains([Attribute], "Assault_Rate") then "Assault_Rate" else if Text.Contains([Attribute], "Assault") then "Assault" else [Attribute])

 

--Nate

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.