RegEx in Power BI and Power Query in Excel with Java Script

From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for RegEx (regular expressions): The Web.Page function can execute JS code and you can pass strings from you M-environment to it with simple string concatenation. So escape the js-code and use the ampersand (“&”) to reference the string. As a function it looks like so:

let
fx=(x,y)=>Web.Page(
“<script>
var x=”&x&”;var y=new RegExp(‘”&y&”‘,’g’);var b=x.match(y);document.write(b);
</script>”)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in fx(“””hello012δΈ­ε›½1235″””,”\\d+”)

This code extracts all decimals (“\\d+”) from the string provided as the first argument and concatenates them with a comma. But just try for yourself by pasting the code into the advanced editor.

This doesn’t work in PowerBI service, so if you need it there, you can switch to an R-script that can be used with a personal gateway. But for Excel users, this is the way to go.

Please vote for a native implementation in M here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8892295-regular-expressions-support-in-power-query

Enjoy and stay queryious πŸ˜‰

Comments (15) Write a comment

  1. Too complicated =)
    ====
    let
    fx=(s, reg)=>Web.Page(“document.write(‘”&s&”‘.match(/”&reg&”/));”),
    tbl = fx(“qwer1234asdf”,”\d+”),
    result = tbl{0}[Data]{0}[Children]{1}[Children]{0}[Text]
    in
    result

    Reply

  2. Pingback: Power BI Lingo, RegEx, Data and BI Summit and more... (April 26, 2018) | Guy in a Cube

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz