Forum Discussion
Table.SelectRows wildcards
Hello
Is it possible to use wildcards in Table.SelectRows?
I am looking for a single digit wildcard or at least a single character.
Thanks!
Hi Anonymous ,
To my knowledge, wildcards to directly filter rows is not available in Power Query.
But an idea similar with what you expect has been submitted in the following link, please vote it up and you can add comments in it: Search or Filter with a Wildcard
As a workaround, you could use Text.Length() ,Text.StartsWith() , Text.EndsWith(), Text.Range() to get the expected:
1.Add a custom column
2.Then select rows when Custom=1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvMSY03MjAy1DUAIb2SihKlWB10cSMc4sY4xE0wxfUNjPSR1CcmJSOUm8KFU1LTwMIwlbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([Column1])=19 and Text.StartsWith([Column1],"file_") and Text.EndsWith([Column1],".txt") and Text.Range([Column1],9,1)="-" and Text.Range([Column1],12,1)="-" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous can you please elaborate this with an example dataset and desired end result. I am having little trouble visualizing what you need.
Not as such. But you can often use the Text.Contains method to accomplish the same thing or, if the pattern is really complex, you could use Regular Expressions in Power Query
You've been asked before for "an example dataset and desired end result." Is there some reason you are not supplying this? It would make helping you much simpler.
Hi Anonymous ,
To my knowledge, wildcards to directly filter rows is not available in Power Query.
But an idea similar with what you expect has been submitted in the following link, please vote it up and you can add comments in it: Search or Filter with a Wildcard
As a workaround, you could use Text.Length() ,Text.StartsWith() , Text.EndsWith(), Text.Range() to get the expected:
1.Add a custom column
2.Then select rows when Custom=1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvMSY03MjAy1DUAIb2SihKlWB10cSMc4sY4xE0wxfUNjPSR1CcmJSOUm8KFU1LTwMIwlbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([Column1])=19 and Text.StartsWith([Column1],"file_") and Text.EndsWith([Column1],".txt") and Text.Range([Column1],9,1)="-" and Text.Range([Column1],12,1)="-" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
