Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Find articles, guides, information and community news

Most Recent
smpa01
Super User
‎01-29-2021 04:07 PM
Super User

How to use JavaScript inside power query for data extraction- Part1

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

 

 

 

 

js to PQWRY.pbix
Labels:

Helpful resources

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