Power Query M函數新增資料表欄位,加總Excel儲存格文字

Power Query的M語言源自Excel函數公式,不過它和SQL資料庫同樣以資料表為對象,功能更為強大。本文以簡單的儲存格文字加總為例,介紹如何新增資料處理欄位。

一、Excel表格

在工作表上有個Excel表格,有幾筆資料,都是由文字格式組成一連串數字。

二、Power Query編輯器

上方功能區「資料>取得及轉換資料」將「取得資料」下拉,點選「開啟Power Query編輯器」。

三、新增空白查詢

進入Excel外掛的Power Query編輯器中,於上方功能區中,「常用>新增查詢>新來源>其他來源」,點選「空白查詢」。

四、表格來源M函數

在Power Query的函數公式列中,輸入「= Excel.CurrentWorkbook(){[Name=”表格1″]}[Content]」,表示要從Excel目前活頁簿的「表格1」查詢取得資料。按Enter鍵便會立即匯入文章一開始看到的表格內容,注意到在右下角有個「套用的步驟」,操作完會多一個「來源」,這是Power Query都會有的第一個步,取得來源資料。

接著將游標移到公式列左邊有個「fx」,輔助視窗會出現「加入步驟」,表示取得資料後要進行下一步。

五、新增資料表欄位

輸入公式:「= Table.AddColumn(來源,”欄位1″,each 1)」,雖然是已經陌生的M語言,但從字面大致上能瞭解其意思,要在資料表新增欄位,沿用Excel函數公式的結構,這個 Table.AddColumn函數有三個引數,第一個「來源」指的是資料表對象,第二個「欄位1」是新增欄位的名稱,第三個「each 1」表示新增欄每筆資料值都是1。

按Enter鍵執行公式命令後,果然新增「欄位1」,內容都是1,而且右下方「套用的步驟」會多一個「自訂1」,表示是編寫M語言執行的步驟。

六、將文字轉為清單

再輸入公式:「= Table.AddColumn(自訂1,”欄位2″,each Text.ToList([數字]))」,其中第一個和第二個引數的作用和先前一樣,只是配合流程稍作修改,第三個引數「each Text.ToList([數字])」是將資料表「數字」欄位資料中的文字一個一個結合轉換為清單。

確認後新增的「欄位2」內容都是「List」,表示它是像陣列一樣由幾項資料組成的清單,性質類似文章一開始所看到的表格,這裡只是沒有呈現內容,只有以「List」表示其性質。

同樣道理,右下方「套用的步驟」會多一個「自訂2」。到了這裡,讀者應該會比較瞭解Power Query運用方式,也會開始熟悉用稍微不同於Excel的方式編寫函數語言處理資這料。

七、將清單資料類型轉為數值

Excel有一些像是VALUE、TEXT、N等轉換資料類型的函數,Power Query當然也需要:「= Table.AddColumn(自訂2,”欄位3″,each List.Transform([欄位2],Number.From))」,其中的「List.Transform([欄位2],Number.From)」意思是將每一組清單轉換為數值類型的資料。

在Excel設計函數公式時,如果有用到MID、LEFT、RIGHT等文字函數處理資料後,如果要進行數學計算要將文字轉換成數值格式。這裡也是同樣道理,因為先前是以 Text.ToList得到文字清單,其資料類型為文字,後續想要進行計算,所以也要用Power Query的方式List.Transform轉換。

八、加總儲存格文字

終於到了最後一層公式:「= Table.AddColumn(自訂3,”欄位4″,each List.Sum([欄位3]))」,經過前面幾道流程,這裡的「List.Sum」應該不難理解了,結果是非常漂亮,果然得到原來Excel儲存格內的文字分割加總的值。

沒問題點選左上角「常用>關閉>關閉並載入」,結束Power Query編輯器,將處理好的資料匯回Excel。

九、查詢與連線

回到熟悉的Excel,經過Power Query一番處理,手上的資料已經是另一個境界了。

Excel表格、Power Query編輯器、SQL Server資料庫

熟悉SQL資料庫語言的讀者,對於「查詢」這兩個字應該不陌生,它是依照使用者所設定條件取得資料庫特定資料的操作。不熟悉SQL的讀者,應該多多少少知道如今大數據分析的基礎就是在於資料庫,因此相關技術很重要且實用。

當初Excel在開發表格這項工具時,其實就已經把一些資料庫的概念加進去了,而如今,Excel在外掛了一個VBA設計程式的外掛之餘,又加了一個強大的Power Query編輯器,等於是要用SQL的方式處理Excel資料。806301878=>41、666080754=>42、……,這任務如果沒有Power Query幫忙,要在Excel算這個東西有點困難,可能一樣要花心思設計函數公式,不過很顯然Power Query的Table、List這些用語是直接以資料庫概念進行,相較於Excel函數較為進階,是很專業的進行資料整理。

Excel的開發路線很容易理解,一方面因應大數據時代,推陳出新才不會被時代淘汱,另一方面微軟產品本來就包山包海,一直有Access和SQL Server這樣的資料庫軟體,把相關技術導入到Excel也是順理成章。
所以也是很自然的,贊贊小屋規劃之後會有SQL Server相關分享,敬請期待。

更多學習:Power BI文章合集

加入Line社群,口袋裡的Excel小教室!

最新文章: