Extract text with certain condition

[WLou]

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

[ImkeF · Accepted solution]

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]

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 · Accepted solution]

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.

[ImkeF · Accepted solution]

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]

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]

Thanks very much ! @ImkeF what a magic!