Forum Discussion

Grizzlydad's avatar
Grizzlydad
Helper I
6 years ago

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

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...
Need Help
Grizzlydad's avatar
Grizzlydad
Helper I
6 years ago
MessageRegionResponseAlert
THE NORTH WEST REGION HAS ESCALATED TO REGIONAL ALERT DUE TO  PROTESTS TOMORROW MORNING WHICH WILL IMPACT ON THE NORTH WEST NETWORK. North WestNoYes
    
    
    
    
Grizzlydad's avatar
Grizzlydad
Helper I
6 years ago

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's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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