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

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

一、Excel表格

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

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

二、Power Query編輯器

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

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

三、新增空白查詢

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

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

四、表格來源M函數

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

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

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

五、新增資料表欄位

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

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

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

六、將文字轉為清單

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

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

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

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

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

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轉換。

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

八、加總儲存格文字

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

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

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

九、查詢與連線

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

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

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文章合集

Power Query M函數新增資料表欄位,加總Excel儲存格文字 19
加入Line社群,口袋裡的Excel小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.