cancel
Showing results for 
Search instead for 
Did you mean: 

Find articles, guides, information and community news

Most Recent
Memorable Member
Memorable Member

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

 

 

 

 

Frequent Visitor

Creating Custom India Map with District wise demarcation

Read more...

Microsoft
Microsoft

Sometimes you may need to handle multiple data source with different table structures ,and you only need to use some of fields for analysis.  

Currently, power query seems. Therefore, if you have to deal with huge amount of sources and have to manually process with structure initial, that will be a problem.

Read more...

Super User II
Super User II

Many of my reports feature the slicer panel that hide/show on via a button. I also include additional visuals on a page this way, such as the forecast line chart in the Supplier Insights report. It is an alternative to creating another page in the report while also keeping the number of shown visuals low on a page.

Read more...

Community Support
Community Support

The article is about how to change the granularity of date in X-axis of line chart with Power BI.

Read more...

Helpful resources

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