要想Power Query效率高，SQL还是少不了

 戳蓝字“Excel星球”关注我哦。

HI，大家好，我是星光。

在以前的推文「什么是Excel Power Query」里，给大家介绍了PQ和函数、VBA、SQL之间的异同。这三者虽然相互独立，但也可以彼此协作。这期就给大家分享一下，如何在PQ中使用SQL语言。

为什么要在PQ中使用SQL语言呢？PQ单身过日子不好吗？

主要原因是SQL的运行效率通常高于PQ，它可以有选择的从数据源摘取部分数据。不过，它在细节问题的处理上又没有PQ灵活，操作也没有PQ的可视化傻瓜化——这俩就是典型的做不了恋人，但偶尔做个朋友也不错。

我举个例子。

如上图所示，假设该表名为成绩表，有几十万行数据。现在，我们需要分析班级为一班、科目为数学的成绩数据。

如果单纯的使用Power Query，它才不管三七二十一，先一口气把几十万行的全部数据都加载了，然后再做筛选处理……这就很不科学。

更推荐使用SQL协作处理。

在【数据】选项卡下依次单击【获取数据】→【自其它源】→【从ODBC】，打开ODBC编辑框。

在编辑框，数据源名称保持dBASE Files不变。高级选项的连接字符串按以下格式输入：

driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=工作簿全路径

在以上语句中，driverid=1046表示Excel的版本，如果是03版，可以设置为driverid=790。dbq=工作簿全路径 指定了目标工作簿的全路径。

编辑框的SQL语句是可选的，可以根据需要编写，本例为：

select * from [成绩表$] where 班级='1班' and 科目='数学'

设置完成后，结果如下图所示：

点击【确定】按钮，转换数据到Power Query编辑器即可▼

万万没想到几十万行的数据筛选后就两行……这模拟数据随机的我都无语了。

……

如果你对Power Query的M函数比较熟悉，会发现以上操作被系统自动录制成了Odbc.Query函数。

代码看不全可以左右拖动...

let    源 = Odbc.Query("driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=C:\Users\guoxinjian\Desktop\如何在PQ中使用SQL语言\模版数据.xlsx;dsn=dBASE Files", "select * from [成绩表$] where 班级='1班' and 科目='数学'")in    源

该函数有3个参数，第1个参数是数据库连接字符串，第2个参数是查询语句，第3个参数很少用，无视它。

与之类似的还有OleDb.Query函数，该函数也可以执行SQL语言。依然以上述案例来说，以下M函数也可以按条件筛选读取数据到Power Query。

第5行代码是OleDb.Query函数...▼

let    str = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=",    cnstr = str &"C:\Users\guoxinjian\Desktop\如何在PQ中使用SQL语言\模版数据.xlsx",    sql = "select * from [成绩表$] where 班级='1班' and 科目='数学'",    源 = OleDb.Query(cnstr,sql )in    源

……

今天给大家分享的内容就这样，有啥问题可以在VIP会员群中提问交流↓👇↓ 挥挥手，咱们明天再见。

👀

👇本文由公众号“Excel星球”首发。

点击阅读原文系统学习Excel！