Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI developers team,
I realized that javascript executes inside Power query through Web.Page like following
let
Source = Web.Page(
"<script>
var square = function (x){return x*x;};
var c = square(12);
document.write(c);
</script>"),
Data = Source{0}[Data],
Children = Data{0}[Children],
#"Expanded Children" = Table.ExpandTableColumn(Children, "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"})
in
#"Expanded Children"
But there are certain things that don't. For e.g. var square changed to const square in the above exmaple
let
Source = Web.Page(
"<script>
const square = function (x){return x*x;};
var c = square(12);
document.write(c);
</script>"),
Data = Source{0}[Data],
Children = Data{0}[Children],
#"Expanded Children" = Table.ExpandTableColumn(Children, "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"})
in
#"Expanded Children"
One more example of non-execution
//doesnot execute
let
Source = Web.Page(
"<script>
var obj = {foo:'bar,baz:'42'};
var values = Object.keys(obj);
document.write(values);
</script>"),
Data = Source{0}[Data],
Children = Data{0}[Children],
Children1 = Children{0}[Children]
in
Children1
I have tried with few and some syntax excutes while other don't.
E.g. Regex positive lookbehind does not work
/(?<=[a-zA-Z]+[/]+[a-zA-Z]+[ ])\d+/gm
but negative lookahead works.
/\d+(?![a-zA-Z]+[/]+[a-zA-Z]+[ ])/gm
String
123INVOICE 7320210279
Rather than trying to figure out which works and which doesn't is it kindly possible to advise which javascript excution engine is used inside PQWRY. Is it equivalent to an engine <IE9?
If I can have this infomration I can either tune my script to match the version or not use the syntax at all.
Many thanks in advance.
Solved! Go to Solution.
Hi @smpa01
If you create a Web request to a browser testing website like WhatIsMyBrowser.com or Cyscape.com then you can get info about the browser used within PQ. For example, my PBI Desktop reports it is using IE 11
Regards
Phil
Proud to be a Super User!
Hi @smpa01
If you create a Web request to a browser testing website like WhatIsMyBrowser.com or Cyscape.com then you can get info about the browser used within PQ. For example, my PBI Desktop reports it is using IE 11
Regards
Phil
Proud to be a Super User!
@PhilipTreacy many thanks for this. It definitely saves me a lot of time.
I am still having problem running js syntax inside PQ that has full support in IE11. E.g. Object.keys has full support in IE11.
https://caniuse.com/?search=object.keys
let
Source = Web.Page(
"<script>
var obj = {foo:'bar,baz:'42'};
var values = Object.keys(obj);
document.write(values);
</script>"),
Data = Source{0}[Data],
Children = Data{0}[Children],
Children1 = Children{0}[Children]
in
Children1
I don't know why is that.
How do you get your script to accept external parameters? - will do a blogpost
What can Javascript do that M can't ? - will do a blogpost .
For starter, REGEX can be natively run in PQ rather than running it through R's stringr (R libraries can't be run in service which is extremely painful, if you have a lot of REGEX wrangling).
Secondly, Different complex Looping for difficult data wranglings.
Once I figure out which syntax and function can be run in PQ , the full power of js (limited to PQ's browser version) can be unleashed for tasks that were thought out of reach in PQ.
Hi @smpa01
I'd be surprised if the browser engine inside PQ is actually a full implementation of IE11. The browser test sites probably identify IE11 by the user agent string. Sorry I can't shed any more light on this.
Regards
Phil
Proud to be a Super User!
@PhilipTreacy Many thanks for your time.
this is a nice find (and you should be able to infer the rendering engine from the windows task manager) but what's the business value of this? How do you get your script to accept external parameters? What can Javascript do that M can't ?
@lbendlinparameterized explained at https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
72 | |
68 | |
46 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |