Mobile Numbers = Not Starting with 0

[LinLinB]

Hi - my data has no initial data validation so I have to do it all from scratch.

With my Mobile number as data type Text (like this 012345678, no gaps), I have these steps which work well:

1) Text.Select col which only selects the numbers (in case there is text added to the number)

2) Text.Length lets me know if there are 10 digits remaining (if it's less, it's invalid number)

This was usually enough for me to then Format the valid 10 digit numbers as xxxx xxx xxx (to be used for SMS messaging)

However, while all valid numbers should start with 0, some invalid numbers start with 1.

Any ideas how I can filter for 10 digit numbers starting with 1? 

The invalid number looks like this: 123456789

Power Query please

Thanks for your help 🙂

[LinLinB]

Thanks @ImkeF - I'm always looking to expand my PQ knowledge - that's another new one!

[ImkeF · Accepted solution]

Hi @LinLinB ,
you could use a filter that transforms to number and filters those > than 99999999

Table.SelectRows(#"Added Custom", each Number.From([Column1]) > 99999999)

 See also file attached.