The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 !!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.