how to efficiently extract the first occurence of a any number in a column ?

[Anonymous]

Hello,

I wrote the code below in order to extract the first occurence of any number in a text, blank otherwise. It works fine but I find it a bit "tedious". Can you think of a shorter/more efficient way to do so please ?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewNDA0UorViVYCE0iCUBZYODgnv8Q/L9UKVRYsVVCUmZeMVRNYxgpNBsYqTk3Oz0uxggmBNZRklmSCGXmlOTlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IMEI = _t]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"IMEI", type text}}),
    IsIMEIContainsDigit = Table.AddColumn(#"Change Type", "IsIMEIContainsDigit", each if Text.PositionOfAny([IMEI], {"0".."9"}) = -1 then false else true),
    IsIMEINotText = Table.AddColumn(IsIMEIContainsDigit, "IsIMEINotText", each Value.Is(Value.FromText([IMEI]), type number)),
    positionOfFirstIMEIDigit = Table.AddColumn(IsIMEINotText, "positionOfFirstIMEIDigit", each Text.PositionOfAny(
                                                                                  [IMEI], 
                                                                                  {"0".."9"}
                                                                                  )),
    IMEIwithoutFirstTextCharacters = Table.AddColumn(positionOfFirstIMEIDigit, "IMEIwithoutFirstTextCharacters", each Text.Range(
                                                                          [IMEI], 
                                                                          Text.PositionOfAny(
                                                                                             [IMEI], 
                                                                                             {"0".."9"}
                                                                                            )
                                                                         )),
    IsIMEIwithoutFirstTextCharactersNotText = Table.AddColumn(IMEIwithoutFirstTextCharacters, "IsIMEIwithoutFirstTextCharactersNotText", each Value.Is(Value.FromText([IMEIwithoutFirstTextCharacters]), type number)),
    positionOfFirstIMEIwithoutFirstTextCharactersText = Table.AddColumn(IsIMEIwithoutFirstTextCharactersNotText, "positionOfFirstIMEIwithoutFirstTextCharactersText", each Text.PositionOfAny(
                                                                          Text.Range(
                                                                                     [IMEI], 
                                                                                     Text.PositionOfAny(
                                                                                                        [IMEI], 
                                                                                                        {"0".."9"}
                                                                                                        )
                                                                                    ), 
                                                                                    {Character.FromNumber(32)..Character.FromNumber(47), Character.FromNumber(58)..Character.FromNumber(255)}
                                                                         )),
    GOODIMEI = Table.AddColumn(positionOfFirstIMEIwithoutFirstTextCharactersText, "GOODIMEI", each if [IsIMEIContainsDigit] = false then 
                                                                                                        ""
                                                                                                    else
                                                                                                        if [IsIMEINotText] = true then 
                                                                                                            [IMEI] 
																	                                    else 
																	                                        if [IsIMEIwithoutFirstTextCharactersNotText] = true then 
																			                                    [IMEIwithoutFirstTextCharacters]
																			                                else
																				                                Text.Start([IMEIwithoutFirstTextCharacters], [positionOfFirstIMEIwithoutFirstTextCharactersText])),
    #"Removed Columns" = Table.RemoveColumns(GOODIMEI,{"IMEI", "IsIMEIContainsDigit", "IsIMEINotText", "positionOfFirstIMEIDigit", "IMEIwithoutFirstTextCharacters", "IsIMEIwithoutFirstTextCharactersNotText", "positionOfFirstIMEIwithoutFirstTextCharactersText"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"GOODIMEI", "IMEI"}})
in
    #"Renamed Columns"

[jgordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewNDA0UorViVYCE0iCUBZYODgnv8Q/L9UKVRYsVVCUmZeMVRNYxgpNBsYqTk3Oz0uxggmBNZRklmSCGXmlOTlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IMEI = _t]),
    n = {"0".."9"},
    GetFirstNumber = (x) => Text.Select(Splitter.SplitTextByCharacterTransition(n,(c) => not List.Contains(n,c))(x){0}, n),
    Result = Table.TransformColumns(Source, {"IMEI", each GetFirstNumber(_)})
in
    Result

[m_dekorte]

Hi @Anonymous 

You could give something like this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewNACzlGJ1opXABKq4GVgsOCe/xD8v1QpVyhwsV1CUmZeMKm6BkLBClbAsTk3Oz0uxQhU0AKsvySzJBDPySnNylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IMEI = _t]),
    firstIMEI = Table.AddColumn(Source, "First IMEI", each 
        [
            a= Splitter.SplitTextByCharacterTransition( each try Number.From(_) is text otherwise true, each true)([IMEI]), 
            b= try Text.Select( List.Select(a , each Text.Length(_) >1 ){0}, {"0".."9"}) otherwise ""
        ][b], type text
    )
in
    firstIMEI

With this result

I hope that works for you

[BA_Pete]

Hi @Anonymous ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewNACzlGJ1opXABKq4GVgsOCe/xD8v1QpVyhwsV1CUmZeMKm6BkLBClbAsTk3Oz0uxQhU0AKsvySzJBDPySnNylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IMEI = _t]),
    addFirstIMEI = Table.AddColumn(Source, "firstIMEI", each Text.Start(Text.Select([IMEI], {"0".."9"}), 15))
in
    addFirstIMEI

To get this output:

Also, I think the TAB key on your keyboard is stuck. You should get that looked at 😉

Pete

[Anonymous]

Hi,

The TABs are done on purpose so that the parameters on each function are clearly identified 🙂 Maybe it doesn't render the same with copy/paste from this site.

Regarding your solution, you are hardcoding the length to 15 characters, which I must not do because internally, IMEI can be a bit different from the official IMEI standard.

[BA_Pete]

No problem. Can you update your example data to be a reflective sample of the problem please?

Pete

[Anonymous]

Sure, I just did so

[BA_Pete · Accepted solution]

Cool. Give this a try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewNDA0UorViVYCE0iCUBZYODgnv8Q/L9UKVRYsVVCUmZeMVRNYxgpNBsYqTk3Oz0uxggmBNZRklmSCGXmlOTlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IMEI = _t]),
    duplicateIMEI = Table.DuplicateColumn(Source, "IMEI", "IMEI_Copy"),

    splitByCharTransition = Table.SplitColumn(duplicateIMEI, "IMEI_Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"IMEI_Copy_First", "IMEI.2"}),
    addFirstIMEI = Table.AddColumn(splitByCharTransition, "firstIMEI", each Text.Select([IMEI_Copy_First], {"0".."9"})),

    remOthCols = Table.SelectColumns(addFirstIMEI,{"IMEI", "firstIMEI"})
in
    remOthCols

For this output:

Pete

[Anonymous]

Brilliant !