Forum Discussion
Data modeling and table lengths in Excel
There is Text.PositionOf function in Power Query which returns the position of the sub-string in a Text Column and if the sub-string is not found, it returns -1.
The syntax would be like this...
=Text.PositionOf(<Text Column>, "<sub-string being searched>")
You may replace the -1 with 0 in the added column if the sub-string was not found like this...
=Replacer.ReplaceValue(Text.PositionOf(<Text Column>, "<sub-string being searched>"),-1,0)
Hi,
Adding formulas to Power Query would work. I have many formulas to add, for instance FIND. I'd like to add a new column that returns position of a phrase in a string. I used Add Column/Custom Column but it seems like it doesn't like FIND as one of functions. Am I on a wrong path here?
There is Text.PositionOf function in Power Query which returns the position of the sub-string in a Text Column and if the sub-string is not found, it returns -1.
The syntax would be like this...
=Text.PositionOf(<Text Column>, "<sub-string being searched>")
You may replace the -1 with 0 in the added column if the sub-string was not found like this...
=Replacer.ReplaceValue(Text.PositionOf(<Text Column>, "<sub-string being searched>"),-1,0)
You're welcome! Glad it worked as desired.