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