the substringshould come first, so the correct expression in Native Query would be:
charindex('.', [_].[text]) - 1 as [Custom]
As a result, the current implementation always returns -1 suggesting that the substring cannot be found. I've also found that switchingsubstring and text in the Power Query function returns the desired result, but in such a case the position of substring and text is not correct as per official documentation.