# Power Query Text Wildcard condition



## Taf (Jun 7, 2021)

How can I convert the below formula into a power Query Custom Column


```
=IF(ISNUMBER(SEARCH("?0*",[Term])),0,LEN(TRIM([Term]))-LEN(SUBSTITUTE([Term]," ",""))+1)
```


----------



## severynm (Jun 8, 2021)

Try this for the formula in your custom column:


```
if Text.Length(Text.BeforeDelimiter([Term],"0",0)) = 1 then 0 else Text.Length([Term])-Text.Length(Text.Replace([Term]," ",""))+1
```

PQ does not support use of wildcards so you kind of have to maneuver around that with the existing `Text.` functions: If the length of the text before the first instance of "0" is 1, then apply your logic.


----------



## Taf (Jun 8, 2021)

severynm said:


> Try this for the formula in your custom column:
> 
> 
> ```
> ...


This is perfect. Thank you. I am kind of new in the Pivot Table / Power Query world.


----------

