title: Regexp in Power Query using JavaScript
body: <p>I need Regexp, while M / Power Query doesn't have native support to it. I found several variants of solution around the same excellent Web.Page &amp; JavaScript idea - <a href="https://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/" rel="noreferrer">Biccauntant</a>, <a href="https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf" rel="noreferrer">Hugoberry</a>. </p>
  
  <p>I had to adopt them (the resulting code is below) due to JavaScript limits.</p>
  
  <p>The main problem is that JavaScript has it's own tuff limit on the String variables - it is not possible to use "\" in it.</p>
  
  <p>My variant tackles the problem, but I'm not professional in JS, so the main question is - I suspect that the "\" problem is not the only one.</p>
  
  <p>So my question is - does anybody see some other problems with PQ &amp; JavaScript "co-operation" while using the Regexp? Regarding other pitfalls with strings, or whatever?</p>
  
  <p>I understand that Regexp has it's own escaping rules (this is not only JavaScript demand, but Regexp itself), so escaping of Regex itself is out of the scope of the question. I.e. regular expression should be properly escaped BEFORE it may be passed as the function parameter. In other words, it is supposed, that if a user wants to use regular expression with "\", he must use "\\" instead, and my function will convert it to "\\\\", which will be passed to JavaScript as Regexp expression.</p>
  
  <p>For PQ users - it turned out that the performance of the solution is quite good for hundreds of rows at least. But not forget that it is not possible to use the function in Power BI service, this is for PowerBI Desktop and Excel only.</p>
  
  <p>The code:
  <div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>(text as nullable text, pattern as nullable text) as logical =&gt;
      let 
          l = List.Transform({text, pattern}, each Text.Replace(_, "\", "\\")), 
          t = Text.Format("&lt;script&gt;document.write(new RegExp('#{1}').test('#{0}'))&lt;/script&gt;", l),
          w = Web.Page(t), 
          d = w[Data]?{0}?[Children]?{0}?[Children]?{1}?[Text]?{0}?, 
          result = text &lt;&gt; null and (pattern = null or (if d &lt;&gt; null then Logical.FromText(d) else error "Regular expression or text are not supported by JavaScript."))
      in
          result</code></pre>
  </div>
  </div>
  </p>