Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Table.SelectRows(#"Previous Step", each [Column Name] >= "AB7000" and [Column Name] <= "AB7999"),
Power Query does not natively support wildcards. There are some alternative routes you could take, though. For instance:
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.
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 🙂
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.
Best Regards,
Eyelyn Qin
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"
This wil also select eg AB71, but it was supposed to select any thing starting with AB and a 7'thousand number
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.
Like the example above, it could be:
AB12
AB234
AB27123
AB712345
AB7012
AB7790
Here only the last two (AB7012 and AB7790) should be selected
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.
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?
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.
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.
Table.SelectRows(#"Previous Step", each [Column Name] >= "AB7000" and [Column Name] <= "AB7999"),
Nice workround.
But is wildcards not possible? So it will be more difficoult to eg filter like XY??*9 ?
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.