Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Find articles, guides, information and community news

Most Recent
smpa01
Super User
Super User

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"

Capture.PNG

 

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"

Capture.PNG

 

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

Capture.PNG

 

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 !!!

 

 

 

 

Helpful resources

Join Blog
Interested in blogging for the community? Let us know.