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