Table.SelectRows wildcards

[Anonymous]

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!

[Anonymous · Accepted solution]

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.

[Anonymous]

@smpa01 @ronrsnfld @Anonymous thanks, RegEx would be great! How can I use them?

An example of what I want to catch is:

file_2021-01-01.txt
file_2021-01-02.txt
file_2021-01-03.txt
file_2021-01-04.txt

I am looking something like: file_????-??-??.txt or even better file_\d\d\d\d-\d\d-\d\d.txt

[ronrsnfld]

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.

[Anonymous]

Do you mean like:

= Table.SelectRows(TableName, each Text.Contains([TextColumn], "abc1"))

That's if you know which column you are searching. Otherwise, I'd use:

Table.FindText(TableName, "abc1")

This will return the entire row wherever "abc1" appears in any column.

--Nate

[ronrsnfld]

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

[smpa01]

@Anonymous  can you please elaborate this with an example dataset and desired end result. I am having little trouble visualizing what you need.