Power Query 作为桌面端数据清理和转换的工具，能极大解放生产力，将繁琐的数据处理工作从重复的劳动中解放出来。那么，Power Query 能否对外提供计算服务呢？或者说 Power Query 有没有对外提供的编程接口？ 根据我的探索，似乎没有，但在网络上找到下面的两种 walkaround 方式，都比较小众。所以如果真的需要数据处理、数据分析服务的话，不如选择其他的方案，比如 pandas 等等，拥有更大的自由度。
- 方式一：将数据加载到 Power Pivot，通过 ADO 方式调用 Power Pivot 的编程接口
- 方式二：利用微软的 Power Query SDK，在 .NET 平台使用 M 语言，获取查询结果。
本篇主要介绍第一种方式，第二种方式给出一些参考链接。
利用 Power Pivot 的数据模型编程接口
貌似 PQ 没有对外的编程接口，但是 Power Pivot 有通过 Excel 工作簿的编程接口，所以我们可以将数据加载到 Power Pivot， 然后在外部调用。因为本文主要是讲 Power Query ，所以对 Power Pivot 不做展开。Power Pivot 是微软推出的 Excel COM 加载项 （COM Add-in），可以在微软官方免费下载并安装，然后启用加载项即可。安装之后，启用方法如下：通过【文件】>【选项】打开如下界面，选择加载项类型的【COM加载项】，点击转到按钮。
image
然后在出现的对话框中，勾上"Microsoft Power Pivot for Excel"。如果想不启用该插件，也是通过相同的路径进入该界面，去掉这个勾。
image
接下来，以之前文章讲解的 PQ 实现的进出存查询为例，进一步讲解如何通过 VBA + ADO 调用 PQ 的查询结果。打开上一篇示例数据的 Excel 文件，选择
stock_balance 查询，点击右键菜单的【加载到】菜单：
image
选择“将此数据添加到数据模型”：
image
然后在【数据】选项卡，点击【管理数据模型】功能项：
image
这样就进入了 Power Pivot 的界面。暂时不对 Power Pivot 的细节展开。
image
打开一个新的 Excel 工作簿，按下 Alt + F11 进入 VBE (Visual Basic Editor) 环境。在 VBE 环境中，通过【工具】>【引用】添加 Microsoft ActiveX Data Model 的引用。这个是 COM 库，运行 ADO 需要。
image
新建一个模块 (Module)，在模块中新建一个函数 ExportExcelDataModel。该函数实现将 Excel Data Model 导出到工作表：
Public Function ExportExcelDataModel(
excelFilePath As String,
modelName As String,
targetSheet As Worksheet)
'''Reference: Microsoft ActiveX Data Objects
Dim wbTarget As Workbook 'target workbook
Dim ws As Worksheet
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQueryString As String
'Suppress alerts and screen updates
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Err.Clear
On Error GoTo ErrHandler
Set wbTarget = Application.Workbooks.Open(excelFilePath)
'Make sure the model is loaded
wbTarget.Model.Initialize
wbTarget.Model.Refresh
Set conn = wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
sQueryString = "EVALUATE '" & modelName & "'"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sQueryString, conn
targetSheet.Cells.ClearContents
' Write header
Dim colIndex As Integer
For colIndex = 0 To rs.Fields.Count - 1
targetSheet.Range("A1").Offset(0, colIndex).Value = rs.Fields(colIndex).Name
Next
' Write Lines
targetSheet.Range("A1").Offset(1, 0).CopyFromRecordset rs
rs.Close
Set rs = Nothing
' Close workbook
wbTarget.Close
ExitPoint:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set rs = Nothing
Exit Function
ErrHandler:
MsgBox "An error occured - " & Err.Number & "," & Err.Description, vbOKOnly
Resume ExitPoint
End Function
因为本文的主题是 PQ，所以不对代码的细节进行讲解，只稍微提一下 ADO 读取 Excel Data Model 的要点：
- 通过
someWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection获得数据连接 -
CopyFromRecordSet方法要求 RecordSet 的 Cursor Location 为adUseClient，否则结果错误，并没有抛出 Exception 或 Error，而是数据出现错误。如果手工代码循环的方式获取，则没有问题。
然后再添加一个子例程，调用函数 ExportExcelDataModel，下面的调用过程既是调用代码，也能体现函数的调用方法。
Public Sub DoExport()
Dim filePath As String
filePath = ThisWorkbook.Path & "\pqservice.xlsx"
Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Extended Properties=""Excel 12.0;HDR=No"";" & _
"data source=" & filePath
' Update cell in another excel workbook
Dim sql As String
sql = "UPDATE [Criteria$A1:B3] SET F2=5 WHERE F1='month'"
conn.Open
conn.Execute sql
conn.Close
' Retrieve data
Dim sht As Worksheet
Set sht = Sheet1
Call ExportExcelDataModel(filePath, "stock_balance", sht)
sht.Activate
End Sub
因为需要将筛选条件：月份，从当前工作簿传递到目标工作簿，我采用了 ADO 直接读写 Excel 工作表的方法。但我平时很少用到 ADO 读写 Excel 工作表的方式，因为数据尽可能存放在数据库中，而不是 Excel。在目标工作簿 -- 即提供 Power Query 服务的 Excel 工作表中，筛选条件界面如下：
image
所以用
sql = "UPDATE [Criteria$A1:B3] SET F2=5 WHERE F1='month'"
表示更新的 SQL 语句。连接字符串中 HDR = No，表示不启用 Header Row，所以此语句 F1 表示 A 列，F2 表示 B 列。Criteria 是工作表名称 (worksheet name)。
本示例代码提供的功能：
- 将目标工作表的 B3 单元格值修改为 5 (月份条件)
- 然后对数据据模型进行刷新操作，确保获取的是最新计算的结果
- 将 Data Model 的计算结果写入 RecordSet 对象，再将 RecordSet 对象数据写入当前工作簿的 Sheet1 (函数的功能）
从理论上来说，这种方法适用于所有能操作 COM 对象 (ADO ActiveX) 的编程语言。
Power Query SDK
微软提供了 Power Query SDK，从而赋予了在 .net 平台中可以直接使用 M 语言的功能。尽管官方的说法，Power Query SDK 已经在 Visual Studio 2019 中可以使用，但我在安装的时候没有成功，也不打算在 Visual Studio 2017 或更老的版本中折腾，这里只能给出相关的连接，有兴趣的读者请自行探索。
Power Query SDK 是一扩展名为 vsix 的文件，在 Visual Studio 中安装的方法请参考：vs2015如何安装vsix扩展工具
网上为数不多的参考文章：
- Running M Queries In Visual Studio With The Power Query SDK
- Creating your first connector - Hello World
- Starting to Develop Custom Connectors
示例数据和代码
github - Consuming Power Query Service
网友评论