Forum Discussion

WLou's avatar
WLou
Helper I
6 years ago
Solved

Extract text with certain condition

Hi all, 

Not sure if this is even possible ?

 

One of the columns in my excel is text filed and I wish to extract number plate info from there 

The number plate is always 6 digits, mixed with Letters and Numbers and they are random

This 6 digist can appear at the front of the text, end of the text and middle of the text string, I think front and end is easy but middle is a trouble

 

Sample text attached

Rego 1PR5DA - 19/20
CS64KT - Bennett - T
MV Insurance 1KL8ZM - M'ton Po

 

Regards,

Wendy

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi WLou  

    please check if this works for you (paste code into the advanced editor and follow the steps)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYrBCoJAGIRfZdhzkWuu5rHsEiaISYfMw0+7G1Ss0K+v1gv0Yq1LzAwM803XicbcB8i6UfstlpD5Ko5Ev+hEcUqTsvXTzjhnxtG3NoDqjIPj6U3uZiDL4+ZSeVZdpyiKs3FwqIfwI9YWpBkyXicqhWayAVi/qWIW6Pt5sX7axx8AhDkEyz7BeZ2XmSK2WvT9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ListOfAllowedValues = {"0".."9"} & {"A".."Z"},
    Custom1 = Source,
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Text.Split([Column1], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> "")),
    TextToList = Table.AddColumn(#"Filtered Rows1", "ListOfCharacters", each Text.ToList([Custom])),
    #"Added Custom2" = Table.AddColumn(TextToList, "ContainedInListOfAllowedValues", each List.MatchesAll( [ListOfCharacters] , (x) => List.Contains(ListOfAllowedValues, x))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PlateNumber", each if [ContainedInListOfAllowedValues] and  List.Count([ListOfCharacters]) = 6 then [Custom] else null)
in
    #"Added Custom3"

     

     

  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi WLou  

    please check the following code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYrBCoJAGIRfZdhzkWuu5rHsEiaISYfMw0+7G1Ss0K+v1gv0Yq1LzAwM803XicbcB8i6UfstlpD5Ko5Ev+hEcUqTsvXTzjhnxtG3NoDqjIPj6U3uZiDL4+ZSeVZdpyiKs3FwqIfwI9YWpBkyXicqhWayAVi/qWIW6Pt5sX7axx8AhDkEyz7BeZ2XmSK2WvT9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ListOfAllowedValues = {"0".."9"} & {"A".."Z"},
    Custom1 = Source,
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Text.Split([Column1], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> "")),
    TextToList = Table.AddColumn(#"Filtered Rows1", "ListOfCharacters", each Text.ToList([Custom])),
    #"Added Custom2" = Table.AddColumn(TextToList, "ContainedInListOfAllowedValues", each List.MatchesAll( [ListOfCharacters] , (x) => List.Contains(ListOfAllowedValues, x))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PlateNumber", each if [ContainedInListOfAllowedValues] and  List.Count([ListOfCharacters]) = 6 then [Custom] else null),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom3",{{"Column1", Order.Descending}, {"PlateNumber", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates",null,"NA",Replacer.ReplaceValue,{"PlateNumber"})
in
    #"Replaced Value"

     

    (x) => is the start of  a function definition with one parameter "x". 
    Any occurance of "x" in the function code is the reference to the function parameter itself.

     

     

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi WLou  

    please check if this works for you (paste code into the advanced editor and follow the steps)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYrBCoJAGIRfZdhzkWuu5rHsEiaISYfMw0+7G1Ss0K+v1gv0Yq1LzAwM803XicbcB8i6UfstlpD5Ko5Ev+hEcUqTsvXTzjhnxtG3NoDqjIPj6U3uZiDL4+ZSeVZdpyiKs3FwqIfwI9YWpBkyXicqhWayAVi/qWIW6Pt5sX7axx8AhDkEyz7BeZ2XmSK2WvT9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ListOfAllowedValues = {"0".."9"} & {"A".."Z"},
    Custom1 = Source,
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Text.Split([Column1], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> "")),
    TextToList = Table.AddColumn(#"Filtered Rows1", "ListOfCharacters", each Text.ToList([Custom])),
    #"Added Custom2" = Table.AddColumn(TextToList, "ContainedInListOfAllowedValues", each List.MatchesAll( [ListOfCharacters] , (x) => List.Contains(ListOfAllowedValues, x))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PlateNumber", each if [ContainedInListOfAllowedValues] and  List.Count([ListOfCharacters]) = 6 then [Custom] else null)
in
    #"Added Custom3"

     

     

    • WLou's avatar
      WLou
      Helper I
      6 years ago

      Hi Imke ImkeF 

      Thanks for sharing this solution 

       

      However can you elaborate a bit more of the below Mcode? I believe it is trying to match to the list created earlier which contains Numbers of Capital letters only

      also how are those "(x)", "=>" and "x" used in this context?

      List.MatchesAll( [ListOfCharacters] , (x) => List.Contains(ListOfAllowedValues, x))

       

      Thanks in advance

      Wendy

       

  • WLou's avatar
    WLou
    Helper I
    6 years ago

    and no more question

    If the origianl record has cells that doesn't contain those capital letters or numbers with 6 digits

     

    Is there a way to mark it as "NA" or something?

     

    It seems the current one is filtering them out

     

    Thanks

    Wendy    

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hi WLou  

      please check the following code:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYrBCoJAGIRfZdhzkWuu5rHsEiaISYfMw0+7G1Ss0K+v1gv0Yq1LzAwM803XicbcB8i6UfstlpD5Ko5Ev+hEcUqTsvXTzjhnxtG3NoDqjIPj6U3uZiDL4+ZSeVZdpyiKs3FwqIfwI9YWpBkyXicqhWayAVi/qWIW6Pt5sX7axx8AhDkEyz7BeZ2XmSK2WvT9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ListOfAllowedValues = {"0".."9"} & {"A".."Z"},
    Custom1 = Source,
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Text.Split([Column1], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> "")),
    TextToList = Table.AddColumn(#"Filtered Rows1", "ListOfCharacters", each Text.ToList([Custom])),
    #"Added Custom2" = Table.AddColumn(TextToList, "ContainedInListOfAllowedValues", each List.MatchesAll( [ListOfCharacters] , (x) => List.Contains(ListOfAllowedValues, x))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PlateNumber", each if [ContainedInListOfAllowedValues] and  List.Count([ListOfCharacters]) = 6 then [Custom] else null),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom3",{{"Column1", Order.Descending}, {"PlateNumber", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates",null,"NA",Replacer.ReplaceValue,{"PlateNumber"})
in
    #"Replaced Value"

       

      (x) => is the start of  a function definition with one parameter "x". 
      Any occurance of "x" in the function code is the reference to the function parameter itself.