Power Query Text Wildcard condition

[Taf]

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]

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]

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.

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