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 Let...
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.

     

     

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.