cancel
Showing results for 
Search instead for 
Did you mean: 
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

watkinnc
Super User
Super User

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors