Create new column returning a value based on multiple criteria in another column

[Grizzlydad]

Hiya all

I have a table which has one column with a lot of text in it.  I wish to search that text and identify whether the word "ALERT or RESPONSE" was used and whicg region was contained in the text.  For example, see below, how do I check if NORTH EAST and ALERT were used to return a value of Yes.  Please bear in mind it may be any location (there are several in the organisation) and I would like to search for the words ALERT or RESPONSE being used in that column too.

NORTH EAST HAVE DECLARED REGIONAL ALERT WITH IMMEDIATE EFFECT IN RELATION TO

Best regards

Grizzly

[v-juanli-msft]

Hi @Grizzlydad 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[edhans]

Can you provide a table of example data and a screenshot perhaps of the expected result, or sample columns with the expected result?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Grizzlydad]

Message	Region	Response	Alert
THE NORTH WEST REGION HAS ESCALATED TO REGIONAL ALERT DUE TO  PROTESTS TOMORROW MORNING WHICH WILL IMPACT ON THE NORTH WEST NETWORK.	North West	No	Yes

[Grizzlydad]

So I want to extract from the message column, which region and whether alert or response was used.  The message column often contains text written inconsitently

[mahoneypat]

Please try out the M code below for one way to accomplish this.  It used Text.Split and List.Intersect to break your message into a list and find all the regions and response types (Alert or Response) in the text.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3BCsMwDEN/ReQ89g8hNU1YahfHI4zSY2G3wdr/p97YaRcJPZC0LMEygUUto1MzKI1FGDk2UEuxRqMBJj8eK2IlNQx3+lDMKua15mESVelw48Ijei7JN0utKNMck8Fn/86YrIvergiXwK/38UTf9uMbXB7bHtb1BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Message = _t, Region = _t, Response = _t, Alert = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Message", type text}, {"Region", type text}, {"Response", type text}, {"Alert", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","NORTH WEST","NORTHWEST",Replacer.ReplaceText,{"Message"}),
RegionList = {"NORTH","SOUTH", "EAST", "WEST","NORTHEAST","NORTHWEST","SOUTHEAST","SOUTHWEST"},
#"Added Custom" = Table.AddColumn(#"Replaced Value", "FoundRegions", each Text.Combine(List.Intersect({Text.Split([Message]," "),RegionList}),"; "), type text),
Custom1 = Table.AddColumn(#"Added Custom", "ResponseType", each Text.Combine(List.Intersect({Text.Split([Message]," "),{"RESPONSE","ALERT"}}),"; "), type text)
in
Custom1

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat