Power Query 作為桌面端數(shù)據(jù)清理和轉(zhuǎn)換的工具件甥，能極大解放生產(chǎn)力藏研，將繁瑣的數(shù)據(jù)處理工作從重復(fù)的勞動中解放出來漠嵌。那么咐汞，Power Query 能否對外提供計算服務(wù)呢判哥？或者說 Power Query 有沒有對外提供的編程接口？ 根據(jù)我的探索碉考，似乎沒有，但在網(wǎng)絡(luò)上找到下面的兩種 walkaround 方式挺身，都比較小眾侯谁。所以如果真的需要數(shù)據(jù)處理、數(shù)據(jù)分析服務(wù)的話章钾，不如選擇其他的方案墙贱，比如 pandas 等等，擁有更大的自由度贱傀。

• 方式一：將數(shù)據(jù)加載到 Power Pivot惨撇，通過 ADO 方式調(diào)用 Power Pivot 的編程接口
• 方式二：利用微軟的 Power Query SDK，在 .NET 平臺使用 M 語言府寒，獲取查詢結(jié)果魁衙。

本篇主要介紹第一種方式，第二種方式給出一些參考鏈接株搔。

利用 Power Pivot 的數(shù)據(jù)模型編程接口

貌似 PQ 沒有對外的編程接口剖淀，但是 Power Pivot 有通過 Excel 工作簿的編程接口，所以我們可以將數(shù)據(jù)加載到 Power Pivot纤房， 然后在外部調(diào)用纵隔。因為本文主要是講 Power Query ，所以對 Power Pivot 不做展開炮姨。Power Pivot 是微軟推出的 Excel COM 加載項 （COM Add-in）捌刮，可以在微軟官方免費(fèi)下載并安裝，然后啟用加載項即可舒岸。安裝之后绅作，啟用方法如下：通過【文件】>【選項】打開如下界面，選擇加載項類型的【COM加載項】蛾派，點(diǎn)擊轉(zhuǎn)到按鈕棚蓄。

image

image

image

選擇“將此數(shù)據(jù)添加到數(shù)據(jù)模型”：

image

然后在【數(shù)據(jù)】選項卡，點(diǎn)擊【管理數(shù)據(jù)模型】功能項：

image

這樣就進(jìn)入了 Power Pivot 的界面姜性。暫時不對 Power Pivot 的細(xì)節(jié)展開瞪慧。

image

打開一個新的 Excel 工作簿，按下 Alt + F11 進(jìn)入 VBE (Visual Basic Editor) 環(huán)境部念。在 VBE 環(huán)境中弃酌，通過【工具】>【引用】添加 Microsoft ActiveX Data Model 的引用。這個是 COM 庫儡炼，運(yùn)行 ADO 需要妓湘。

image

新建一個模塊 (Module)，在模塊中新建一個函數(shù) ExportExcelDataModel乌询。該函數(shù)實現(xiàn)將 Excel Data Model 導(dǎo)出到工作表：

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榜贴，所以不對代碼的細(xì)節(jié)進(jìn)行講解，只稍微提一下 ADO 讀取 Excel Data Model 的要點(diǎn)：

• 通過 someWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection 獲得數(shù)據(jù)連接
• CopyFromRecordSet 方法要求 RecordSet 的 Cursor Location 為 adUseClient妹田，否則結(jié)果錯誤唬党，并沒有拋出 Exception 或 Error，而是數(shù)據(jù)出現(xiàn)錯誤鬼佣。如果手工代碼循環(huán)的方式獲取初嘹，則沒有問題。

然后再添加一個子例程沮趣，調(diào)用函數(shù) ExportExcelDataModel屯烦，下面的調(diào)用過程既是調(diào)用代碼，也能體現(xiàn)函數(shù)的調(diào)用方法房铭。

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

因為需要將篩選條件：月份驻龟，從當(dāng)前工作簿傳遞到目標(biāo)工作簿，我采用了 ADO 直接讀寫 Excel 工作表的方法缸匪。但我平時很少用到 ADO 讀寫 Excel 工作表的方式翁狐，因為數(shù)據(jù)盡可能存放在數(shù)據(jù)庫中，而不是 Excel凌蔬。在目標(biāo)工作簿 -- 即提供 Power Query 服務(wù)的 Excel 工作表中露懒，篩選條件界面如下：

image

所以用

sql = "UPDATE [Criteria$A1:B3] SET F2=5 WHERE F1='month'"

表示更新的 SQL 語句。連接字符串中 HDR = No砂心，表示不啟用 Header Row懈词，所以此語句 F1 表示 A 列，F(xiàn)2 表示 B 列辩诞。Criteria 是工作表名稱 (worksheet name)坎弯。

本示例代碼提供的功能：

• 將目標(biāo)工作表的 B3 單元格值修改為 5 (月份條件)
• 然后對數(shù)據(jù)據(jù)模型進(jìn)行刷新操作，確保獲取的是最新計算的結(jié)果
• 將 Data Model 的計算結(jié)果寫入 RecordSet 對象，再將 RecordSet 對象數(shù)據(jù)寫入當(dāng)前工作簿的 Sheet1 (函數(shù)的功能）

從理論上來說抠忘，這種方法適用于所有能操作 COM 對象 (ADO ActiveX) 的編程語言撩炊。

Power Query SDK

微軟提供了 Power Query SDK，從而賦予了在 .net 平臺中可以直接使用 M 語言的功能崎脉。盡管官方的說法拧咳，Power Query SDK 已經(jīng)在 Visual Studio 2019 中可以使用，但我在安裝的時候沒有成功囚灼，也不打算在 Visual Studio 2017 或更老的版本中折騰骆膝，這里只能給出相關(guān)的連接，有興趣的讀者請自行探索啦撮。

• Power Query SDK 下載
• 微軟 DataConnector 開源項目
• 基于Power Query SDK的 PowerQueryNet 開源項目

Power Query SDK 是一擴(kuò)展名為 vsix 的文件，在 Visual Studio 中安裝的方法請參考：vs2015如何安裝vsix擴(kuò)展工具

網(wǎng)上為數(shù)不多的參考文章：

• Running M Queries In Visual Studio With The Power Query SDK
• Creating your first connector - Hello World
• Starting to Develop Custom Connectors

示例數(shù)據(jù)和代碼

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