How to use JavaScript inside power query for data …

TLDR- javascript syntax can be executed inside pqwry through web.page

I recently found out that JavaScript syntax can be run inside Power query for data extraction/wrangling. The way to achieve this is through Web.Page.

For example, following is the HTML that returns the JavaScript execution value

<!DOCTYPE html>
<html>
<body>

<h2>JavaScript Statements</h2>

<p>A <b>JavaScript program</b> is a list of <b>statements</b> to be executed by a computer.</p>

<p id="demo"></p>

<script>
x = 5;        // Statement 2
y = 6;        // Statement 3
z = x * y;    // Statement 4
z1 = 'The value of z is ' + z + '.';
document.write(z1);
</script>

</body>
</html>

Paste the above code in a notepad and save it as .html and run it in a browser to see how it executes.

The way to execute the above inside Power Query through parametarization is following

//query name fx1
let
  fx=(a,b)=>
    Web.Page(
        "<script>
            x = '"&Number.ToText(a)&"';
            y= '"&Number.ToText(b)&"';
            z= x*y;
            z1 = 'The value of z is ' + z + '.'; 
            document.write(z1);
        </script>") [Data]{0}[Children]{0}[Children]{1}[Text]{0}
    /*Data = fx{0}[Data],
    Children = Data{0}[Children],
    Children1 = Children{1}[Children]*/
in
    fx

The way to execute the above code inside PQWRY is following

let
Source = Table.FromColumns({{10..20},{30..40}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx1([Column1],[Column2]))
in
    #"Added Custom"

If you ant to utilize the js as it is then the parameterized query would be following

let
  fx=(a,b)=>
    Web.Page(
        "<script>
            x = '"&a&"';
            y= '"&b&"';
            z= x*y;
            z1 = 'The value of z is ' + z + '.'; 
            document.write(z1);
        </script>") [Data]{0}[Children]{0}[Children]{1}[Text]{0}
    /*Data = fx{0}[Data],
    Children = Data{0}[Children],
    Children1 = Children{1}[Children]*/
in
    fx

but in that case the parameters must be text string

let
Source = Table.FromColumns({{10..20},{30..40}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx2([Column1],[Column2]))
in
    #"Added Custom"

Why js executes inside power query? – because there is a browser inside powerquery that executes the javascript.

What is the power query browser version? run https://www.whatismybrowser.com/ inside Web.Contents

What js syntax can I run? you need to refer to – https://developer.mozilla.org/en-US/docs/Web/JavaScript to see what syntax has current PQWRY browser support.

In the next post, I will discuss about some of the situations where js precedes powerquery syntax for data extraction.

Thank you !!!

Source link