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

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

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
Comments
Advocate III
‎11-11-2021 07:00 AM

Hey I am trying to use this method to implement a regular expression function - it all works in desktop, but when I refresh in the service it doesn't seem to get the same results. What do I need to check for to enable this feature?

0
Super User
‎11-11-2021 07:18 AM

@cmeu  is it kindly possible to send me the pq with regex for me to test out in my workspace?

I am on a premium workspace and I have not come across a situation yet where a regex runs in desktop but fails in service.

 

BTW I have a post on running regex in PQ

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p...

Impactful Individual
‎11-16-2022 08:27 AM

I have the following Java script following the Jira documentation. 

HttpResponse<JsonNode> response = Unirest.get("https://mediagenix.atlassian.net/rest/api/3/project")
.basicAuth("xxx@yyyy.tv", "LNoi5zTdSjoH28qMJYkc0892")
.header("Accept", "application/json")
.asJson();

System.out.println(response.getBody());

 

How can I convert it into a power query? Thanks!

 

 

0
‎01-06-2023 02:24 AM

This is really cool but when I create a table visual in Power Bi desktop from the 3 columns (Column1, Column2 and Custom) it works

Lexx_0-1673000029201.png

But when I publish the report

the Custom column is blank 😞

Lexx_1-1673000297713.png

Is it possible to actually use this or is this just a toy example of what you can do on Power Bi desktop only?

If you can only use this in your own desktop environment, then why not use R or Python?

It is better supported and easier.

 

0
‎01-06-2023 04:36 AM

Ah I found out from another thread. Sadly you cannot use javascript in Power Bi service.

It is not supported by the data gateway.

 

So if you just want to use it locally on desktop and you do not need to actually use or share your data or publish any reports, then it works great.

 

Lex

0
Super User
‎01-06-2023 06:10 AM

@Lexx I have been successfully using Javascript in service (dataset, dataflow) and you can too once you set up a gateway for 

 

Web.Page

in service.

0
‎11-02-2023 07:17 PM

@smpa01, thanks for the great writeup! I'm curious if you've been able to make this work when referencing a Javascript class.

 

I have a scenario where a site requires some of the parameters passed to the POST request to be hashed. The challenge is the methods used are in a referenced library inside of a Javascript class. I have a set of code that works fine in the browser, but does not function in Power Query. It seems that functions work fine, just not classes.

0
Super User
‎11-06-2023 09:40 AM

@randomnote1  You can't use js the same way in PQ as you would in node or in a js ref by html. JS has very limited capacity and does not live up to its reputation in PQ being blazing fast (just-in-time compilation) cause in PQ it is extremely slow. It is nice to have in PQ but I did not have a lot of success for PROD material with js. For ES6 class, you are out of luck.

 

smpa01_0-1699292164023.png

 

Class in IE