在Power Query中使用VB/JavaScript

VB/JavaScript并不是Power Query中直接支持的语言,但可以间接运行,是近期才被发掘的隐藏黑科技。
本文中介绍的方法,对于高级用户可根据案例举一反三,通过改写代码实现自己需要的功能。而对于普通用户,可以将代码拿来直接用,不需要理解。

函数Web.Page用于将Html源码解析为表格结构,比如先写一段简单的Html:

<html>
<body>
	<p>Hello World!</p>
</body>
</html>

在浏览器中打开后显示出其中的内容:"Hello World!"。
将这段Html作为Web.Page的参数写入Power Query,解析后并经过多次深化也能找到这段内容。

JavaScript是一种运行在浏览器中的解释型编程语言,在Html中引入JS可以为网页添加各式各样的动态功能。
比如再写一段简单的引入了JS的Html:

<html>
<body>
    <script>
	document.write('Hello '+'World!');
    </script>
</body>
</html>

在浏览器中打开后显示的内容依然为:"Hello World!",但这个内容并不是事先写死的,而是通过JS脚本将两个字符串'Hello ''World!'动态拼接在一起而得到的。
浏览器能够显示出经过JS运算后的结果,那么在Power Query中又将返回怎样的结果呢?动手测试一下:

同样也返回了经过JS运算后的结果,而不是返回原来的JS代码,这同时也说明了JS是可以被运行在M语言中的。
其中的Html代码也可以简化,去掉其他标签,只留script标签,写作:

= Web.Page("<script>document.write('Hello '+'World!');</script>")



这个就厉害了,这意味着我们只需要修改参数中的JS代码,便能够在Power Query中实现原本编程语言才能实现的功能。
举几个例子:

1、正则表达式

JS中可以使用match等方法使用正则,如'hello123中国456'.match(/\d+/g)返回["123","456"]。
将这个表达式替换'Hello World!',作为Web.Page的参数,便能够在M中实现正则,返回"123,456"。
将字符串和正则表达式分别作为变量str和re,封装为自定义函数:

(str as text,re as text)=>
Web.Page(Text.Format(
	"<script>
		document.write('#[str]'.match(#[re]));
	</script>",
	[str=str,re=re]))
		[Data]{0}[Children]{0}[Children]{1}[Text]{0}

新建一个空查询,复制以上代码粘贴到高级编辑器并将查询命名为RegExp,便能够通过调用自定义函数RegExp使用正则了。
测试= RegExp("hello123中国456","/\d+/g")返回"123,456"。

2、导出表格

以往Power Query只能批量导入不能导出,而使用JS便能打破这个限制,前提是必须开启ActiveX控件。
本文中以下大部分案例都需要开启,就不一一重复提醒。开启方法如下:

  1. 打开IE浏览器,打开Internet选项,或直接按快捷键Alt+X+O。
  2. 依次点击安全-Internet-自定义级别。
  3. 找到以"对未标记为可安全执行脚本的ActiveX控件"开头的选项,选择启用。


没错,开启后将使你的电脑变得不安全,所以可在当有需要的时候开启,不需要的时候再关闭。
同样封装为自定义函数,代码为:

(tb as table,path as text)=>
let
    Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(tb)))),
    Export = Web.Page(
                    "<script>
                            var fso=new ActiveXObject('Scripting.FileSystemObject');
                            var f1=fso.CreateTextFile('"&Text.Replace(path,"","/")&"',true);
                            var arr="&Json&";
                            f1.WriteLine(arr.join('\n'));
                            f1.WriteBlankLines(1);
                            f1.Close(); 
                    </script>")
in
    Export

新建一个空查询,复制以上代码粘贴到高级编辑器并将查询命名为Export。
自定义函数需要两个参数,第一参数为需要导出的表,第二参数指定导出的路径,包含文件名和扩展名,但使用的CreateTextFile方法只支持csv和txt。
测试= Export(表,"C:\Users\rages\Desktop\test.csv"),在桌面上生成了一个test.csv文件。
可使用List.Transform批量导出多个表,但以防同时导出时产生冲突,最好再使用Function.InvokeAfter设置延时。

由于该方法是将表先转换为Json再传递到JS,所以导出的数据量有限制。
Ivan Bondarenko对代码进行了测试,当导出20万行15列的表格时没有问题,但当行数增加到三倍时出现了错误。
所以当需要导出的表的量级很大时,可以将一张表拆分为多张表循环导出,Ivan的博客中对此有更深入的介绍。

3、打开并重新保存表格,解决导入错误问题

经常会遇到一些玄学xls,Power Query读取的时候报错,提示"外部表不是预期的格式"。还有一些虽然不报错,但表格内明明有内容读出来却是空表,最典型的就是电商行业生意参谋导出的文件。
遇到这种问题,往往打开文件什么都不做,直接保存后再导入就没问题了。但是每天重复去打开那么多文件还是很麻烦的,有了JS就可以解决这个烦恼。

(path)=>Web.Page(
"<script>
	var oXL = new ActiveXObject('Excel.application');
	oWB = oXL.Workbooks.open('"&Text.Replace(path,"","/")&"');
	oWB.Save();
	oXL.Quit();
</script>")

新建一个空查询,复制以上代码粘贴到高级编辑器并将查询命名为Open_Save,参数中填写文件路径,在导入文件的上一个步骤中调用自定义函数。
测试= Open_Save("C:\Users\rages\Desktop\【生意参谋平台】PC店铺流量来源-2016-11-19_2016-11-19.xls")

未完。。。

16 Replies to “在Power Query中使用VB/JavaScript”

    1. 曾贤志老师第三季的课最后几节就有,主要是用于文字筛选。结合PQ 本身的功能已经能够满足绝大部分使用场景。
      好用。

  1. 请教大佬,为什么我这段代码在powerquery中执行没有反应呢?
    let
    url = "http://basic.10jqka.com.cn/api/stock/export.php?export=main&type=report&code=688036",

    visitWebsite = Web.Page("

    var a = document.createElement('a');
    var blob = new Blob([a.Body]);
    var url = window.URL.createObjectURL(blob);
    var filename = '123.xls';
    a.href = '"&url&"';
    a.download = filename;
    a.click();
    a.remove();

    ")
    in
    visitWebsite

  2. 请问,第三条中用Open_Save打开指定路径的xls文件之后要怎么操作,我试了还是不能正常获取到需要的表

  3. 大佬~学习了~不想打赏都不行呀哈哈~
    这个对于数据难清洗的,数据量不大的话,好使!

  4. 老师,第三步可以详细讲解下如何调用自定义函数不,“在导入文件的上一个步骤中调用自定义函数” 看不懂这一步

  5. 这边文章什么时候更新啊。用PQ爬网站数据时候,不是会遇到一些需要登陆的网站吗。是不是可以用嵌套JS的代码自动登陆并获取Cookie啊?该怎么写?

  6. 我发现,想封装一个正则表达式的函数并不简单,还要考虑js和pq的转义字符,还有连续空格的转换。

    还有1个不解的地方,我有1个53w行的表,用json解析时,有些列要拆分,但有些不用。最后我发现,拆分成每1w行调用1次json解析是最快的,目前也不知道是为什么。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注