Forum Discussion

LinLinB's avatar
LinLinB
Advocate I
3 years ago
Solved

Mobile Numbers = Not Starting with 0

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 🙂

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    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.

     

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