MicrosoftHello SergeiBaklan
Appreciate your response but I also want to achieve case insensitivity like for example FOF, fOf, FOf, etc and any further permutations / combinations of all these words (FOF and others above) should be covered in the condition for which I think (not sure) regular expressions is the answer. Not sure about support for case insensitivity in power query - Haven't delved deeper in to that part.
Actually what I have covered above is just an example and my actual function in excel with regular expressions span two columns in excel:
For the first column the pattern is: (?i)^((?!(equity|hybrid|Solution Oriented|FOF|elss)).)*$ and for 2nd column the pattern is: (?i)^((?=.*direct)|(?=.*growth)|(?=.*gold)|(?=.*silver))(^((?!(regular|idcw|dividend|hybrid|balanced advantage|index|nifty)).)*$) (as you can see I want to lookahead positively and negatively at the same time in the second pattern) and I am combining both patterns using python UDF nested with excel IF and CONCAT functions like this:
=IF((REGEXFINDM(PQ___Python_Combo___2021[Scheme Type],CONCAT(AB4))<>"")*(REGEXFINDM(PQ___Python_Combo___2021[Scheme Name],CONCAT(AB3))<>"")=1,PQ___Python_Combo___2021[Net Asset Value],0) Where AB3 and AB4 cells contain above patterns and if the multiplication of combined conditions = 1, then function returns NAV else it returns 0
Reason behind using CONCAT is my pattern text sometimes hit 255 characters limit imposed by excel.
Hope I am clear in explaining my use case.
