試論P(yáng)ower Query M 語言在數(shù)據(jù)處理中的應(yīng)用

王譯慶

（中國石油集團(tuán)共享運(yùn)營有限公司西安中心，西安 710018）

0 引 言

Power Query 是微軟公司研發(fā)的一個數(shù)據(jù)處理引擎，目前它被內(nèi)置到Excel、Power BI 等數(shù)據(jù)處理和分析軟件中。Power Query 包含一個獨(dú)立的圖形編輯器界面和一個Power Query M 語言的解釋器。用戶在圖形編輯器上操作，后臺會自動生成對應(yīng)Power Query M 語言的代碼,同時圖形編輯器提供對處理后的數(shù)據(jù)進(jìn)行實(shí)時預(yù)覽的功能。操作圖形編輯器可以滿足常見的數(shù)據(jù)處理轉(zhuǎn)換需求，但是一些復(fù)雜的數(shù)據(jù)處理還是需要手工編寫或修改Power Query M 語言代碼。本文嘗試對Power Query M語言的特性和應(yīng)用進(jìn)行討論，以實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)處理、轉(zhuǎn)換、分析等業(yè)務(wù)。

1 Power Query M 語言特性

Power Query M 語言是一個用于數(shù)據(jù)處理的函數(shù)式語言，因此它與常見的命令式編程語言有著很大的不同，按照《Power Query M language specification》的說法，Power Query M 語言是“一個大致純粹的、高階的、動態(tài)類型化的、部分延遲的函數(shù)式語言”［1］。下面我們來討論該語言的一些主要特性。

1.1 Let表達(dá)式

Power Query M 語言是一個用于數(shù)據(jù)處理的語言，在大多數(shù)情況下都是將數(shù)據(jù)讀入，處理后輸出。因此，一般情況下，Power Query M 語言程序的主體就是一個Let 表達(dá)式。該表達(dá)式的作用是完成一系列計算，然后指定一個變量進(jìn)行輸出。

上邊的代碼進(jìn)行了一系列計算，然后將計算出的z 變量作為結(jié)果輸出。

1.2 數(shù)據(jù)類型

Power Query M 語言主要用于數(shù)據(jù)處理，它的類型偏向于各種數(shù)據(jù)的組合，主要類型如下。

1.2.1 Any 類型

該語言的任何類型都是從Any 類型派生出來的，如果沒有顯式地指定某個變量的類型，那么Power Query M 語言解釋器就認(rèn)為這個變量是Any 類型，即可以是任何類型。

1.2.2 基元類型

基元類型可以是單個值的類型，包括數(shù)字（Number），字符串（Text），日期（Date）等。基元類型可以構(gòu)造出下文中要討論的結(jié)構(gòu)化類型。廣義上講，任何類型，包括Any 類型，也是基元類型，因?yàn)樗鼈円部梢詷?gòu)造出結(jié)構(gòu)化類型（嵌套的結(jié)構(gòu)化類型）。

1.2.3 結(jié)構(gòu)化類型

由一個或多個基元類型構(gòu)造出來的類型就是結(jié)構(gòu)化類型，包含列表類型（List）、記錄類型（Record）和表類型（Table）。

1.2.3.1 列表類型（List）

列表類型和其他編程語言的List 類型類似，是由一系列有順序的元素構(gòu)成，這些元素可以是任意不同的類型，包括其他的結(jié)構(gòu)化類型來形成嵌套。Power Query M 語言的列表類型數(shù)據(jù)由一對大括號“{}”表示，中間的多個元素用逗號分隔。例如，{1,2,3,"AB",{4,"D"}}，這個列表在Power Query 編輯器中呈現(xiàn)效果如圖1 所示。

圖1 列表類型示例

1.2.3.2 記錄類型（Record）

記錄類型類似其他編程語言的字典類型。用于表示有鍵值對（Key-Value Pair）的數(shù)據(jù)。Power Query M語言的記錄用一對中括號 “[] ”來包含，每個鍵值對的鍵（Key）在左邊，為沒有引號的字符串，值（Value）在右邊，可以是各種類型的值，按照其本身的表示法直接寫出，鍵和值用等號連接。不同鍵值對用逗號分隔，例如“[學(xué)號=0000001,姓名=“張三”,出生日期=#date(1990,02,26)]”。

1.2.3.3 表類型（Table）

表類型數(shù)據(jù)就是通常意義的表格，這是Power Query 數(shù)據(jù)處理中最常見的一種類型，因?yàn)镻ower Query 本身就是被設(shè)計來處理表格數(shù)據(jù)的。通常情況下，大多數(shù)Power Query 查詢都是從一個外部數(shù)據(jù)源中導(dǎo)入數(shù)據(jù)形成表格，然后使用各種函數(shù)（可以是內(nèi)置的，也可以是自定義的）對表格進(jìn)行數(shù)據(jù)轉(zhuǎn)換和處理，然后最終輸出一個處理后的表格。如果是用于技術(shù)研究和論證，也可以直接用Power Query M 語句來構(gòu)建表格。方法是先定義標(biāo)題行，然后是數(shù)據(jù)。例如，以下代碼就定義了一個如圖2 所示的表格。

圖2 表類型示例

表格還可以看作是由一條條結(jié)構(gòu)相同的記錄組成的集合。通過集合項訪問運(yùn)算符“{i}”訪問表格就可以得到單條記錄。例如，上邊的表格用過語句“表格{1}”訪問可以得到該表格的第一行記錄：“[學(xué)號=0000001,姓 名=“張三”,出生日期=#date(1990,02,26)]”［2］。

1.3 函數(shù)式語言特性

美國數(shù)學(xué)家阿隆佐·邱奇（Alonzo Church）發(fā)明的λ 演算，以變量綁定和替換的規(guī)則，研究了函數(shù)的抽象化定義、應(yīng)用以及遞歸的形式系統(tǒng)。在1937 年，英國科學(xué)家艾倫·麥席森·圖靈（Alan Mathison Turing）證明了λ 演算和圖靈機(jī)是等價的［3］，這說明λ 演算是圖靈完備性的，因此成為了函數(shù)式編程語言的理論基礎(chǔ)。而常用編程語言，例如C 語言是命令式編程語言，命令式編程語言和函數(shù)式編程語言在設(shè)計上有著很大的不同。隨著信息技術(shù)的發(fā)展，部分語言雖然是以命令式編程語言為基礎(chǔ)，但是它們吸收了函數(shù)式編程的一些理念，展現(xiàn)了多重編程范式的特性。例如，Python 語言和JavaScript 語言。純粹的函數(shù)式編程語言，例如，Haskell、Scheme 等，一般用于教學(xué)和學(xué)術(shù)研究，在工業(yè)上的應(yīng)用比起以命令式為主的多重編程范式語言相對要少一些，這些函數(shù)式語言在工業(yè)上主要用于數(shù)據(jù)分析、財務(wù)分析等領(lǐng)域［4］。Power Query M 語言就是一種大致純粹的函數(shù)式語言，具有和其他的函數(shù)式語言一樣的特性。

1.3.1 函數(shù)定義

Power Query M 語言的函數(shù)定義的寫法是λ 表達(dá)式的寫法：(x,y)=＞ x+y，這里“x”和“y”是參數(shù)，“x+y”是函數(shù)體。和其他函數(shù)式語言類似，Power Query M 語言的函數(shù)本身是一種類型，可以賦值給變量，也可以通過變量來調(diào)用。例如，下面的代碼就是一個函數(shù)定義和調(diào)用的示例。

1.3.2 純函數(shù)（Pure Function）

在計算機(jī)編程中，純函數(shù)具有以下特征：第一，對于相同的參數(shù)，每次運(yùn)行后，函數(shù)返回值總是相同的，不受上下文和環(huán)境的影響；第二，函數(shù)的運(yùn)行不產(chǎn)生副作用（不影響上下文和其他模塊的數(shù)據(jù)）。因此，純函數(shù)是數(shù)學(xué)函數(shù)在計算機(jī)程序中的模擬，和其他函數(shù)式語言一樣，Power Query M 語言的函數(shù)都是純函數(shù)。

1.3.3 頭等函數(shù)（First-class Function）和高階函數(shù)（Higher-order Function）

許多介紹函數(shù)式編程的文獻(xiàn)會提到，在函數(shù)式編程語言中，函數(shù)是“一等公民”。具體來說，就是函數(shù)可以作為別的函數(shù)的參數(shù)、函數(shù)的返回值，函數(shù)本身還可以賦值給變量，這種語言特性被稱為“頭等函數(shù)”。頭等函數(shù)特性使得函數(shù)的功能和設(shè)計非常靈活，能夠極大簡化映射、排序、遍歷等操作。一些流行的編程語言，例如Python 和JavaScript 也引入了頭等函數(shù)特性。一種編程語言，要具備頭等函數(shù)的特性，就要用到高階函數(shù)。高階函數(shù)是可以將其他函數(shù)作為參數(shù)和返回值的函數(shù)。Power Query M 語言具有頭等函數(shù)特性，同時，它內(nèi)置的許多庫函數(shù)都是高階函數(shù)。例如對表格機(jī)型篩選的Table.SelectRows 函數(shù)，就是一個高階函數(shù)，它的定義如下：

Table.SelectRows(table as table,condition as function) as table

可以看出，第一個Table 參數(shù)是要篩選的表格，第二個Condition 參數(shù)的類型就是一個函數(shù)，參數(shù)為表格中的每一條記錄，返回值為邏輯值的函數(shù)，返回值是篩選后的表格。該函數(shù)的作用是將Table 參數(shù)的表格的每一行記錄作為參數(shù)代入Condition 函數(shù)中，如果返回值為True，該行保留，否則，該行去除，最終返回篩選結(jié)果的表格數(shù)據(jù)。現(xiàn)在以表 1 為例，如果需要篩選出類型為“水果”的記錄，可以寫出如下代碼：

篩選結(jié)果=Table.SelectRows(表1,(r)=＞ r[類型]="水果")

表1 示例數(shù)據(jù)-商品

運(yùn)行結(jié)果見表2。在Power Query M 語言中，對于單個參數(shù)的函數(shù)，其參數(shù)可用下劃線代替：“Table.SelectRows(更改的類型,(_)=＞ _[ 類型]=" 水果")”，這種寫法可以使用“Each”關(guān)鍵字進(jìn)一步簡化：“Table.SelectRows(更改的類型,Each ([類型]="水果"))”，因此“Each”關(guān)鍵字相當(dāng)于“(_)=＞ _”［5］。

表2 篩選結(jié)果

1.3.4 引用透明（Referentially Transparent）

引用透明是函數(shù)式語言的另一個重要特性。如果一個表達(dá)式可以被其對應(yīng)的值替換（或者反過來）而不改變程序的行為，則該表達(dá)式稱為引用透明［6］。要做到引用透明，一方面要求程序中的函數(shù)必須都是純函數(shù)，另一方面，其變量必須是不可變的。許多命令式編程語言的函數(shù)既不是純函數(shù)，其變量也是可變的，因此它們是引用不透明（referentially opaque）的。

在1.3.2 節(jié)中，我們討論了純函數(shù)，下面我們來討論“變量的不可變性”。變量在許多編程語言中，都是一個十分重要的概念。對于命令式編程語言，變量可以在程序中通過賦值語句任意改變，這樣的變量就是“可變的”。例如，下列的Python 程序，對變量“a”進(jìn)行了多次賦值，它的運(yùn)行結(jié)果是“2”：

對于函數(shù)式編程語言來說，變量經(jīng)過一次賦值之后，就不能再賦值了，這種特性稱之為“變量的不可變性”。從這個角度上講，函數(shù)式編程“變量”應(yīng)該叫做“常量”，之所以還叫“變量”，是由于歷史習(xí)慣。對于Power Query M 語言這種函數(shù)式編程語言，變量同樣是不可變的，將上面的Python 代碼翻譯成等價的Power Query M 代碼，會出現(xiàn)如圖3 顯示的錯誤。

圖3 Power Query M 語言變量的不可變性

對于習(xí)慣了命令式編程語言的開發(fā)人員來說，給變量重新賦值是一個常見的操作，像上文的“a=a +1”在程序設(shè)計中很常見，因此許多類C 語言為此提供了一個簡化寫法：“a++”。變量的不可變性似乎是一個功能缺陷，但是從數(shù)學(xué)的角度來講，“a=a+1”這種寫法顯然是不成立的。引用透明的特性使得函數(shù)式語言比命令式語言更加貼近數(shù)學(xué)，更適合以數(shù)學(xué)的思維來解決問題，同時也能夠讓函數(shù)式語言的語句在不同的上下文環(huán)境中運(yùn)行結(jié)果相同，且運(yùn)行后也不影響上下文環(huán)境，在多線程、并行計算中比命令式語言更加有優(yōu)勢。

1.3.5 遞歸（Recursion）

引用透明特性使得函數(shù)式語言無法具備命令式語言常見的For 循環(huán)和While 循環(huán)這類迭代功能。函數(shù)式語言中的迭代通常是通過遞歸來完成的。遞歸函數(shù)調(diào)用自己，讓操作重復(fù)，直到它到達(dá)初始狀態(tài)，然后計算出結(jié)果。例如，使用Power Query M 語言通過遞歸方式實(shí)現(xiàn)階乘：

互遞歸的兩個函數(shù)相互引用，如果直接放到Let語句中會引發(fā)循環(huán)引用的錯誤，對于函數(shù)式語言來說，函數(shù)也是一種數(shù)據(jù)類型，因此將它們放到一個列表中就可以解決這個問題。

1.3.6 惰性求值（Lazy Evaluation）

惰性求值，也叫做延遲求值，是許多函數(shù)式編程語言都有的特性。它是指一個表達(dá)式被賦值給變量后并不會被計算，直到確定需要得到結(jié)果（例如，需要輸出結(jié)果）時才會計算出結(jié)果。與其相對的是“及早求值”，即遇到表達(dá)式就計算出結(jié)果，這是許多命令式語言所用的特性。對于Power Query M 語言來說，只有“列表”“記錄”和“表”類型中的元素以及Let 表達(dá)式是惰性求值的，剩余的其他表達(dá)式都是及早求值，這樣可以避免重復(fù)計算，提高了性能。

2 案 例

2.1 背景和需求

某工廠有A、B、C 三個特殊工作區(qū)域，在這些區(qū)域工作需要發(fā)放特殊津貼，每個區(qū)域的津貼標(biāo)準(zhǔn)都不相同，表3 是不同區(qū)域不同職位等級工作一天要發(fā)放的津貼標(biāo)準(zhǔn)（日標(biāo)準(zhǔn)）的對應(yīng)關(guān)系。

表3 特殊津貼日標(biāo)準(zhǔn)

員工每天根據(jù)工廠生產(chǎn)安排，在特定的工作區(qū)域工作，每個月在不同區(qū)域工作天數(shù)都不相同，員工在不同區(qū)域的工作天數(shù)會被輸入到人力資源系統(tǒng)的考勤模塊中。人力資源系統(tǒng)在內(nèi)部網(wǎng)絡(luò)中提供了一個讀取考勤數(shù)據(jù)的Web API 接口，調(diào)用后會返回一個Json 格式的數(shù)據(jù)，讀取出的數(shù)據(jù)如表 4。

表4 考勤數(shù)據(jù)

根據(jù)以上數(shù)據(jù)，每個員工特殊津貼的計算方法為對應(yīng)職位等級的區(qū)域日標(biāo)準(zhǔn)乘以該區(qū)域的出勤天數(shù)，然后分別相加，具體如公式（1）所示。

特殊津貼=∑區(qū)域日標(biāo)準(zhǔn)×該區(qū)域出勤天數(shù)（1）

因此，最后確定的需求是依據(jù)考勤數(shù)據(jù)讀取接口中獲取的數(shù)據(jù)來自動計算每個人的特殊津貼。考勤數(shù)據(jù)每月都會更新，所以考勤數(shù)據(jù)更新后，計算結(jié)果也要具備自動刷新的能力。

2.2 實(shí) 現(xiàn)

2.2.1 編寫特殊津貼計算函數(shù)

根據(jù)特殊津貼計算規(guī)則，需要定義一個特殊津貼的計算函數(shù)，該函數(shù)使用考勤數(shù)據(jù)的四個字段作為參數(shù)，分別是職位等級和三個區(qū)域的出勤天數(shù)。特殊津貼的日標(biāo)準(zhǔn)可以直接從表3 獲取。這樣，就可以用Power Query M 語言來實(shí)現(xiàn)這個函數(shù)。

內(nèi)置的Table.SelectRows 函數(shù)在1.3.3 節(jié)中已有介紹，Table.SelectRows 函數(shù)雖然完成了按照“職級”參數(shù)對表 3 進(jìn)行了篩選，但是它返回的類型是表格，這時，就需要用內(nèi)置的Table.First 函數(shù)將其轉(zhuǎn)化為一條記錄類型，以便直接計算結(jié)果。Table.First 函數(shù)的作用是將一個表格的第一行變成記錄類型的數(shù)據(jù)，在此處，篩選出的日標(biāo)準(zhǔn)數(shù)據(jù)只有一行，符合這里的需求。

2.2.2 完成對考勤數(shù)據(jù)的計算

Power Query 可以直接讀取Web 接口返回的數(shù)據(jù)，在本例中，考勤數(shù)據(jù)讀取接口在內(nèi)部網(wǎng)絡(luò)的URL地址為:“http://hr.erp.fab/api/Attendance”。開發(fā)者在設(shè)置好讀取來源后，Power Query 會自動生成數(shù)據(jù)導(dǎo)入部分的代碼，導(dǎo)入的結(jié)果為表格類型。然后將計算函數(shù)的調(diào)用代碼添加進(jìn)去就可以完成最終的計算，最終完成的查詢程序如下，運(yùn)行結(jié)果見表5。

表5 運(yùn)行結(jié)果

let

源=Json.Document(Web.Contents("http://hr.erp.fab/api/Attendance")),

轉(zhuǎn)換為表=Table.FromList(源,Splitter.SplitBy Nothing(),null,null,ExtraValues.Error),

展開=Table.ExpandRecordColumn(轉(zhuǎn)換為表,"Column1",{"工號","姓名","職位等級","a 區(qū)域出勤天數(shù)","b 區(qū)域出勤天數(shù)","c 區(qū)域出勤天數(shù)"},{"工號","姓名","職位等級","A 區(qū)域出勤天數(shù)","B區(qū)域出勤天數(shù)","C 區(qū)域出勤天數(shù)"}),

導(dǎo)入的考勤數(shù)據(jù)=Table.TransformColumnTypes(展 開,{{" 工號",type text},{" 職位等級",Int64.Type},{"A 區(qū)域出勤天數(shù)",type number},{"B 區(qū)域出勤天數(shù)",type number},{"C 區(qū)域出勤天數(shù)",type number}}),

//以上代碼是Power Query 數(shù)據(jù)導(dǎo)入模塊生成的，完成了考勤數(shù)據(jù)接口的調(diào)用

調(diào)用津貼計算函數(shù)=Table.AddColumn(導(dǎo)入的考勤數(shù)據(jù),"特殊津貼",each 特殊津貼計算函數(shù)([職位等級],[A 區(qū)域出勤天數(shù)],[B 區(qū)域出勤天數(shù)],[C 區(qū)域出勤天數(shù)]))

in

調(diào)用津貼計算函數(shù)

在上述查詢程序中，我們使用了內(nèi)置的Table.AddColumn 函數(shù)，它的定義是：

Table.AddColumn(table as table,newColumnName as text,columnGenerator as function,optional columnType as nullable type) as table

該函數(shù)的作用是給table 參數(shù)所指向的表格增加一列，newColumnName 參數(shù)是新增的列標(biāo)題，columnGenerator 參數(shù)需要傳入一個函數(shù)，其參數(shù)是table 參數(shù)中的每一列。上述查詢程序中columnGenerator 參數(shù)使用了“each”關(guān)鍵字進(jìn)行了簡化，如果完整寫出其等價語句則是：

調(diào)用津貼計算函數(shù)=Table.AddColumn(更改的類型,"特殊津貼",(r)=＞ 特殊津貼計算函數(shù)(r[職位等級],r[A 區(qū)域出勤天數(shù)],r[B 區(qū)域出勤天數(shù)],r[C 區(qū)域出勤天數(shù)]))

通過以上的工作，我們成功建立了一個根據(jù)考勤數(shù)據(jù)自動計算特殊津貼的查詢程序。如果考勤查詢接口的數(shù)據(jù)更新了，我們只需要重新運(yùn)行一下查詢程序，計算結(jié)果就會自動更新。

3 結(jié) 語

通過以上的討論，可以看出Power Query M 語言作為Power Query 數(shù)據(jù)處理引擎的開發(fā)語言，具有函數(shù)式開發(fā)語言的優(yōu)良特性，在數(shù)據(jù)轉(zhuǎn)換、分析、計算等領(lǐng)域具有非常大的優(yōu)勢。數(shù)據(jù)處理程序編寫完成后，Power Query 從數(shù)據(jù)源獲取數(shù)據(jù)后就可以直接輸出結(jié)果，不需要人工干預(yù)。用戶既可以從Power Query工具中自動生成Power Query M 語言代碼來完成一些簡單常用的數(shù)據(jù)轉(zhuǎn)換操作，也可以手工編寫Power Query M 語言代碼完成復(fù)雜的數(shù)據(jù)分析處理操作，滿足了業(yè)務(wù)上的不同需求。