Create conditional column by delimiting at different points based on keywords

[JJH_]

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 "_"

Attribute	TypeofCrime
Assault_2020	Assault
Assault_Rate2020	Assault_Rate

Sample Data

[wdx223_Daniel · Accepted solution]

[Anonymous]

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