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

image

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 下载
• 微软 DataConnector 开源项目
• 基于Power Query SDK的 PowerQueryNet 开源项目

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

参考

• Exporting data from PowerPivot
• Export a table or DAX query from Power Pivot to CSV using VBA
• Extracting raw data from a PowerPivot model using Python