How to use wild cards like * and ? ind power query filter list

[ckurir]

How can I use a wild card in a filter for a list?

Eg, I have values like

AB12

AB234

AB7012

AB7790

And I want to filter all AB7???, that is all starting with AB and a 7000 thounds number

[Rickmaurinus]

Power Query does not natively support wildcards. There are some alternative routes you could take, though. For instance: 

• Text.StartsWith( "MyText", "My" ) resembles SQL's WHERE x = 'My%'
• Text.EndsWith( "MyText", "xt" ) resembles SQL's WHERE x = '%xt'

Once you get to more trickier ones like wildcard characters you will need to write custom functions. Wildcards are often represented by ? or _. 

I wrote an article on the different approaches that work for these type of wildcards and others. 

https://gorilla.bi/power-query/wildcards/

It can get a little complex. I hope for regex support or wildcards in the future, though. 

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

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

[Anonymous]

You could extract the Text.AfterDelimiter "AB7" and test its length (Text.Length) - if it's three then okay.

It works if there's no numbers involved too 🙂

[v-eqin-msft]

Hi @ckurir ,

Agree with @ronrsnfld , the wildcard filter is not directly available in power query. You could follow  @AnkitKukreja ‘s suggestion by using Text.StartsWith() to locate the string.

If you also want this in power bi, let the power bi community to know this things and give your votes at power bi site's feedback - Ideas

Here are similar ideas:

Search or Filter with a Wildcard

Auto Select filter values upon user entry with wildcard

Best Regards,
Eyelyn Qin

[AnkitKukreja]

You can try M code like Text.StartsWith.

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Column1], "AB7"))

Posting the full code I tried for your question.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNFKK1QExjIxNoCxzA7igubmlgVJsLAA=", 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}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Column1], "AB7"))
in
#"Filtered Rows"

[ckurir]

This wil also select eg AB71, but it was supposed to select any thing starting with AB and a 7'thousand number

[AnkitKukreja]

Hi @ckurir 

I could have think of a solution what was present in the question.
Is it possible for you to share some more datapoints and your expected answer? I might give it a shot.

But as @v-eqin-msft suggested the wildcard filter are not available directly in power query, maybe we can use it with dax if you can share your pbix or data in excel for me to try.

[ckurir]

Like the example above, it could be:

AB12

AB234

AB27123

AB712345

AB7012

AB7790

Here only the last two (AB7012 and AB7790) should be selected

[AnkitKukreja]

Hi @ckurir 

If you've few only few conditions like in this example then this might help.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNFKK1QExjIxNYCxzQyNjKBvENDGFcQzgqs3NLQ2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Test] = "AB712345" then "NA" else if Text.StartsWith([Test], "AB7") then [Test] else "NA")
in
#"Added Conditional Column"

Please mark it as a solution if it answers your question.

[ckurir]

Not really good. I have hunrdreds of lines. There could also be AB723 or similar which is not to be selected. Maybe something with lenght as a condition could be used?

[AnkitKukreja]

Hi! @ckurir 

That was my suggestion earlier. If you can provide the data and the expected result, then I might be able to help you.

[ckurir]

I cannot send you the real data due to GDPR. But the examples I gave is a good example. But I am not so keen any more to get the actual problem solved, I already accepted a solution. But I was just wondering if it is not possible to use wildcards as many other programming languages provides. But is seems not to be the case.

[ronrsnfld · Accepted solution]

Table.SelectRows(#"Previous Step", each [Column Name] >= "AB7000" and [Column Name] <= "AB7999"),

[ckurir]

Nice workround.

But is wildcards not possible? So it will be more difficoult to eg filter like XY??*9 ?

[Hisham326]

Try using TEXT.STARTSWITH() or TEXT.CONTAINS()

[ronrsnfld]

I do not believe wild cards are part of the M language specification.

You could implement Regular expressions (either in Javascript, Python or R), but that seems like more trouble than using straight text filters; and might be less efficient on large databases. 

However, I believe you can use wild cards in the Power BI filters, but you asked specifically about Power Query.