Transforming Data - Filtering Rows to values that only start with a numerical digit

[RFJammer]

Hi, I'm currently at the stage of sorting out the data I want from our database in a dashboard and need to filter out certain rows in the model. 

Effectively, based on a field called 'Form or Set' I don't want anything that doesn't start with a numerical digit. I have gotten around this by doing the following:

= Table.SelectRows(#"Added Custom", each Text.StartsWith([Form or Set], "1") or Text.StartsWith([Form or Set], "2") or Text.StartsWith([Form or Set], "3") ..... Text.StartsWith([Form or Set], "9"))

but this seems inefficient. Is there a way of doing this which looks at the first digit of the string, and if it is not a numerical digit, it is rejected?

[Anonymous · Accepted solution]

Hi @RFJammer , hello sjoerdvn, Kedar_Pande and HotChilli, thank you for your prompt reply!

You could also add the filter row in power query advanced editor as shown below:

= Table.SelectRows(#"Changed Type", each Text.Start([Form or Set], 1) >= "0" and Text.Start([Form or Set], 1) <= "9")

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjoerdvn]

More generic, if first character is in a set:

Table.SelectRows(#"Added Custom", each (Text.PositionOfAny(Text.Start([Form or Set],1), {"0","1","2","3","4","5","6","7","8","9"})=0))

[Kedar_Pande]

@RFJammer 

= Table.SelectRows(#"Added Custom", each Value.Is(Number.FromText(Text.Start([Form or Set], 1)), type number))

This checks if the first character of the "Form or Set" column is a number, making the filtering much cleaner and easier to maintain.

Your Kudos/Likes are much appreciated!
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Regards,
Kedar Pande
www.linkedin.com/in/kedar-pande

[HotChilli]

You can use List.Contains({0..9}, xxxx)

or you can use Number.From(xxx) - that'll throw an error on text and you can remove the error lines by right-clicking the column header 'remove errors)