find the first position in a string from a column

[cristianj]

Hello , 

i need to find the position of the first number in a column 

Thank you i find this command but it gives me an error

SEARCH(\"[0-9]\", <string>)

thank you

[PhilipTreacy · Accepted solution]

Hi @cristianj 

Download example PBIX file

You need to use Power Query and the Text.PositionOfAny function. 

Text.PositionOfAny - PowerQuery M | Microsoft Learn

Here's the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxMSlaK1YlWSkwyhDAqKquMYIKGiUZJxgj5FKPUNGOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Pos of First Number", each Text.PositionOfAny([String], {"0".."9"}))
in
    #"Added Custom"

Regards

Phil

[Ahmedx · Accepted solution]

pls txry this

[PhilipTreacy · Accepted solution]

Hi @cristianj 

Download example PBIX file

You need to use Power Query and the Text.PositionOfAny function. 

Text.PositionOfAny - PowerQuery M | Microsoft Learn

Here's the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxMSlaK1YlWSkwyhDAqKquMYIKGiUZJxgj5FKPUNGOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Pos of First Number", each Text.PositionOfAny([String], {"0".."9"}))
in
    #"Added Custom"

Regards

Phil