Forum Discussion
Transforming Data - Filtering Rows to values that only start with a numerical digit
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?
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.
4 Replies
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)
= 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-pandeMore 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))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.
