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

Question

Hiya all&nbsp;I have a table which has one column with a lot of text in it.&nbsp; I wish to search that text and identify whether the word "ALERT or RESPONSE" was used and whicg region was contained in the text.&nbsp; For example, see below, how do I check if NORTH EAST and ALERT were used to return a value of Yes.&nbsp; 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&nbsp;Best regards&nbsp;Grizzly

edhans · Answer

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 QuicklyHow to provide sample data in the Power BI Forum

grizzlydad · Answer

MessageRegionResponseAlertTHE NORTH WEST REGION HAS ESCALATED TO REGIONAL ALERT DUE TO&nbsp; PROTESTS TOMORROW MORNING WHICH WILL IMPACT ON THE NORTH WEST NETWORK.&nbsp;North WestNoYes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

grizzlydad · Answer

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

mahoneypat · Answer

Please try out the M code below for one way to accomplish this.&nbsp; 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.&nbsp;letSource = 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)inCustom1&nbsp;If this works for you, please mark it as solution.&nbsp; Kudos are appreciated too.&nbsp; Please let me know if not.Regards,Pat

v-juanli-msft · Answer

Hi&nbsp;Grizzlydad&nbsp;
Is this problem sloved?&nbsp;
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.
&nbsp;
Best Regards
Maggie

Forum Discussion

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

5 Replies

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Date difference with multiple criteria

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