Power Query M 語言教學:ChatGPT 協助 Excel 進階編輯器寫程式

Power Query M 語言是 Excel 資料整理的程式語言,平常使用 Power Query 操作時,其實就已經在自動產生程式碼了。這篇文章從實際操作出發,介紹如何查看 M 語言,並利用 ChatGPT 協助編寫程式,讓資料整理更加方便。

Power Query M 語言特色圖:左側套用的步驟來源、導覽、已將標頭升階、已變更類型,中央 let 與 in 之間的 Excel.Workbook、Table.PromoteHeaders 程式碼,右側 AI 對話,副標題為 ChatGPT 協助編寫 Excel 查詢

一、Power Query 取得 Excel 資料來源

這次的練習素材,是交通部觀光署公布的觀光遊憩據點遊客人次統計表,檔案內容是 115 年 1 月至 8 月主要觀光遊憩據點的遊客人次累計資料。贊贊小屋先開一份空白活頁簿,切換到功能區的「資料」索引標籤,點選最左邊的「取得資料」,在展開的選單中選擇「從檔案」,再從右側子選單點選「從 Excel 活頁簿」。同一個選單裡,還可以看到「從資料庫」「從 Azure」「從 Fabric 和 Power Platform」「從線上服務」「從其他來源」等分類;「從檔案」底下除了 Excel 活頁簿,也有「從文字/CSV」「從 XML」「從 JSON」「從 PDF」「從資料夾」與「從 SharePoint 資料夾」。

Excel 資料索引標籤:取得資料選單展開從檔案,子選單選取從 Excel 活頁簿,另可見從資料庫、從 Azure、從文字/CSV、從 PDF 等來源

「取得資料」就是 Power Query 在 Excel 裡的入口。不論資料來自另一份 Excel、CSV 文字檔、資料庫、網站或雲端服務,進入 Power Query 之後,都使用同一套編輯器整理,操作方式不需要因為來源不同而重新學一次。這些資料也不是一次性地複製貼上,Excel 會保留查詢與資料來源之間的連線,來源檔案更新之後,只要檔案位置與欄位結構沒有改變,按下「資料」索引標籤上的「全部重新整理」,就能依原本設定好的步驟重新取得資料。

對經常要處理外部檔案的人來說,這是 Power Query 最實用的地方:原始檔案維持原樣,所有整理動作都記錄在查詢裡。像這份觀光統計表會依月份持續更新,只要查詢建立好,之後拿到新的檔案,就不必每次從頭整理一遍。

不同來源,統一整理:Excel、CSV、資料庫、網站、雲端服務的資料匯入 Power Query,整理成資料預覽

二、匯入 Excel 檔案與選擇工作表

點選「從 Excel 活頁簿」之後,會開啟「匯入資料」視窗。贊贊小屋切換到 E 槽的「程式測試區」→「TEST」資料夾,選取「2觀光遊憩據點遊客人次-115年1月至8月-累計表」這個 Excel 活頁簿,下方「檔案名稱」欄位會帶入同樣的檔名,檔案類型維持「Excel 檔案」,確認之後按下右下角的「匯入」。

匯入資料視窗:在 E 槽程式測試區的 TEST 資料夾選取 2觀光遊憩據點遊客人次-115年1月至8月-累計表,按下匯入

接著出現的是「導覽器」視窗。左側顯示剛才選擇的檔案,展開之後,底下只有一張工作表「累計明細表- 以類型分」;點選這張工作表,右側就會出現資料預覽。預覽中可以看到第一個欄位名稱是報表標題「115年1月至8月主要觀光遊憩據點遊客人次累計表」,下一欄則是「Column2」,往下才看到「類型 Type」「觀光遊憩區」,以及「國家(自然)公園 National Parks」等資料。贊贊小屋確認選取的工作表正確之後,沒有直接載入,而是按下右下角的「轉換資料」。

導覽器視窗:選取工作表累計明細表- 以類型分,右側預覽顯示報表標題與國家(自然)公園資料,按下轉換資料

導覽器的用途,是在正式匯入之前先選擇要使用哪些資料。一份活頁簿裡可能有好幾張工作表、表格或已命名的範圍,導覽器會把它們全部列出來,並提供預覽,避免匯入錯誤的內容;如果需要同時匯入多個項目,也可以勾選左上方的「選取多重項目」。

導覽器下方的「載入」與「轉換資料」,差別在於要不要先整理資料。「載入」會依目前預覽的樣子,直接把資料放進 Excel 工作表;「轉換資料」則會先開啟 Power Query 編輯器,讓使用者在資料進入 Excel 之前調整欄位、篩選資料列或修改資料類型。這份觀光統計表的開頭是報表標題與說明文字,類型與名稱也包含中英文,直接載入並不方便後續分析,所以選擇「轉換資料」。

載入與轉換資料的差別:載入直接取得資料放進 Excel 工作表,轉換資料先進入 Power Query 編輯器整理資料再放進 Excel 工作表

三、Power Query 編輯器自動記錄操作步驟

按下「轉換資料」之後,就進入 Power Query 編輯器。視窗上方的功能區有「常用」「轉換」「新增資料行」「檢視表」等索引標籤;左側是「查詢」窗格,目前只有一個查詢「累計明細表- 以類型分」;中間是資料預覽,左下角的狀態列顯示這份資料有 34 個資料行、443 個資料列;右側則是「查詢設定」窗格,上方「屬性」的「名稱」就是查詢名稱,下方是「套用的步驟」。

Power Query 編輯器:公式列顯示 Table.TransformColumnTypes,資料預覽第 2 列才是類型、觀光遊憩區、縣市等欄名,右側套用的步驟為來源、導覽、已將標頭升階、已變更類型

這時候贊贊小屋還沒有做任何整理,「套用的步驟」裡卻已經有四個步驟:「來源」「導覽」「已將標頭升階」「已變更類型」。「來源」代表讀取哪一個檔案,「導覽」代表在導覽器中選了哪一張工作表,「已將標頭升階」是把第一列資料作為欄位名稱,「已變更類型」則是 Power Query 依照內容自動判斷每個欄位的資料類型。點選目前的「已變更類型」,上方公式列就會顯示這個步驟的程式碼,開頭是「= Table.TransformColumnTypes(已將標頭升階,」。從預覽也可以看出,因為原始檔案的第一列是報表標題,被升階成欄位名稱的是這串標題文字,真正的「類型」「觀光遊憩區」「縣市」「115年 1月」等欄名,則出現在預覽的第 2 列。

Power Query 會把每一次的介面操作都保存成一個查詢步驟,依照先後順序排列在「套用的步驟」中。點選任何一個步驟,中間的預覽就會回到該步驟完成時的資料狀態,方便檢查每一步做了什麼;之後如果再用功能區做篩選、移除資料列或新增資料行,也會一個接一個加進清單。

更重要的是,每一個步驟背後都有對應的 M 語言程式碼。公式列顯示的是目前所選步驟的那一行,而整個查詢的完整程式碼,則可以在「常用」索引標籤的「進階編輯器」中看到。換句話說,使用者在 Power Query 裡點選功能區時,其實就是在一行一行產生 M 語言。

操作步驟對應 M 語言:套用的步驟來源對應 File.Contents 與 Excel.Workbook,已將標頭升階對應 Table.PromoteHeaders,已變更類型對應 Table.TransformColumnTypes

四、Power Query 進階編輯器查看 M 語言

在 Power Query 編輯器的「常用」索引標籤中,「查詢」群組裡有一個「進階編輯器」按鈕。點下去之後,會開啟「進階編輯器」視窗,上方顯示查詢名稱「累計明細表- 以類型分」,中間就是這個查詢目前完整的 M 語言程式碼,下方顯示「未偵測到任何語法錯誤。」,右下角則有「完成」與「取消」。這段程式碼就是剛才四個套用步驟的完整內容:

Power Query 進階編輯器的原始 M 語言let
    來源 = Excel.Workbook(File.Contents("E:\程式測試區\TEST\2觀光遊憩據點遊客人次-115年1月至8月-累計表.xlsx"), null, true),
    #"累計明細表- 以類型分_Sheet" = 來源{[Item="累計明細表- 以類型分",Kind="Sheet"]}[Data],
    已將標頭升階 = Table.PromoteHeaders(#"累計明細表- 以類型分_Sheet", [PromoteAllScalars=true]),
    已變更類型 = Table.TransformColumnTypes(已將標頭升階,{{"115年1月至8月主要觀光遊憩據點遊客人次累計表#(lf)Visitors to the Principal Scenic Spots in Taiwan#(lf)January-August,2026", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}})
in
    已變更類型
進階編輯器視窗:累計明細表- 以類型分查詢的 M 語言程式碼,從 let 到 in,包含 Excel.Workbook、Table.PromoteHeaders、Table.TransformColumnTypes,下方顯示未偵測到任何語法錯誤

M 語言的查詢以 let 開頭、以 in 結尾。let 與 in 之間的每一行都是一個步驟,格式是「步驟名稱 = 運算式」,行尾用逗號分隔;in 後面寫的是查詢最後要輸出的步驟,這裡是「已變更類型」。對照「套用的步驟」,就可以看出兩者是同一份內容:「來源」「已將標頭升階」「已變更類型」直接就是程式碼裡的步驟名稱,第二行 #"累計明細表- 以類型分_Sheet" 則對應介面上的「導覽」。這個名稱包含空格與符號,所以 M 語言用 #"……" 的寫法把整個名稱包起來。

每個步驟都會使用前一個步驟的結果繼續處理。「來源」用 File.Contents 讀取 E 槽的 Excel 檔案,再交給 Excel.Workbook 解析活頁簿內容;導覽步驟從「來源」中取出 Item 為「累計明細表- 以類型分」、Kind 為 Sheet 的工作表資料;Table.PromoteHeaders 把第一列升階為標題;Table.TransformColumnTypes 再設定每個欄位的資料類型。這裡的 #(lf) 代表換行字元,因為原始報表的標題儲存格中,中英文是分成不同行輸入的。

理解這個結構之後,M 語言就不再那麼陌生:它不是另外一套獨立的程式,而是把介面上每一個操作,依照順序記錄下來的文字版本。M 語言的函數名稱,也常以「類別.功能」的形式命名,例如 Excel.Workbook、Table.PromoteHeaders,從名稱就能大致猜出用途。想看從空白查詢自己寫 M 函數的例子,可以參考 Query 中文字典 建立資料表的做法;每個函數的完整說明,則可以查詢 Microsoft 的 Power Query M 函式參考。

M 語言基本結構:let 定義步驟,來源 = Excel.Workbook(…) 與已將標頭升階 = Table.PromoteHeaders(…) 是資料轉換,in 輸出已將標頭升階,旁邊註記 #"名稱" 與 #(lf)

五、ChatGPT 協助編寫 Power Query M 語言

這段程式碼的來由,要從贊贊小屋與 ChatGPT 的另一段討論說起。贊贊小屋原本把這份觀光統計 Excel 交給 ChatGPT 分析,希望進行資料正規化,整理成方便從不同角度分析與彙總的資料,這段規劃過程記錄在 Power Query 範例 一文;ChatGPT 建議使用 Power Query,並且依照原始檔的欄位位置,整理出一張共 16 個步驟的「Power Query 操作流程總整理」,從「從活頁簿」匯入、「移除頂端資料列」,一直到「附加查詢為新增」與「關閉並載入至」。贊贊小屋在 Excel 匯入原始資料之後,沒有照著 16 個步驟逐一點選,而是打開進階編輯器,把裡面的程式碼整段複製,連同下面這段需求一起貼給 ChatGPT:

贊贊小屋傳給 ChatGPT 的需求我已經在 Excel 匯入原始資料了,在進階編輯器顯示如下,你可以幫我把「Power Query 操作流程總整理」裡的操作轉換成編輯器語言嗎?這樣我只要在最後載入 Excel 即可,謝謝!

需求文字的下方,接著就是進階編輯器裡從 let 開始的完整程式碼,包含「來源」那一行的檔案路徑,以及導覽步驟中的工作表名稱。贊贊小屋的想法很直接:既然 Power Query 的每個操作最後都會變成 M 語言,與其一步一步在介面上點選,不如請 ChatGPT 直接把整個流程寫成程式碼,自己只需要在最後把資料載入 Excel。

ChatGPT 對話中贊贊小屋貼出的需求:請把 Power Query 操作流程總整理轉換成編輯器語言,下方附上進階編輯器的 let 程式碼

先把既有的程式碼交給 ChatGPT,而不是只描述需求,好處在於這段程式碼已經包含 AI 無法憑空知道的資訊:檔案放在哪個資料夾、檔名是什麼、使用哪一張工作表,以及查詢目前進行到哪一步。ChatGPT 可以沿用同一個資料來源,從現有的查詢繼續往下寫,不需要猜測路徑或工作表名稱,產生的程式碼也比較容易直接放回原本的查詢中使用。

ChatGPT 協助編寫 M 語言需要的資訊:資料整理需求加上現有 M 語言的檔案路徑、工作表名稱、查詢起點,一起交給 ChatGPT 產生 M 語言程式碼

六、將 Power Query 操作流程轉為 M 語言

ChatGPT 分析之後回覆,表示這是很好的學習方式:「我們直接使用 Power Query 的 M 語言(進階編輯器),把前面 16 個操作步驟整合成一段完整程式碼。」接著列出贊贊小屋只需要做的六件事:開啟 Power Query 編輯器、點選「首頁」→「進階編輯器」、將原本程式碼全部取代、貼上它提供的完整程式碼、按「完成」,確認資料正確後再選擇「關閉並載入」。ChatGPT 也先提醒,目前的程式有一個地方需要修正:原始資料的第 1 列是報表標題、第 2 列是空白,第 3 列才是真正的欄位名稱,所以必須先移除前兩列,再把標題升階。

ChatGPT 回覆:直接使用 Power Query 的 M 語言(進階編輯器),把前面 16 個操作步驟整合成一段完整程式碼,並列出六個操作步驟與需要修正的提醒

ChatGPT 提供的程式碼依處理順序分成多個區段,並附上一張對照表,說明每個區段在做什麼、對應哪個 Power Query 函數。原本在介面上「移除頂端資料列」,對應的是 Table.Skip;「將第一列作為標題」是 Table.PromoteHeaders;「填滿」→「向下」是 Table.FillDown;「新增自訂資料行」是 Table.AddColumn;篩選資料列是 Table.SelectRows;原本流程中的「取消其他資料行樞紐」,在程式碼裡改用 Table.Unpivot,直接指定要轉成資料列的月份欄位;把兩個年度的資料合併,則使用 Table.Combine;最後再用 Table.TransformColumnTypes 設定資料類型,用 Table.ReorderColumns 調整欄位順序。拆分中英文名稱的部分,則用 Text.Split 與 Text.Trim 寫成一個可以重複使用的函數。

這裡最能看出程式碼的便利。依照原本的操作流程,115 年與 114 年的資料要先用「參照」建立兩個查詢,分別保留欄位、取消樞紐、新增年度,再用「附加查詢為新增」合併;ChatGPT 則是把「保留欄位、取消樞紐、新增年度與月份、排除空白」這一連串處理寫成一個自訂函數,分別帶入兩個年度呼叫一次,再把結果合併起來。原本需要在不同查詢之間來回點選的工作,變成同一段程式碼裡的幾行文字。

Power Query 的圖形介面與 M 語言,其實是操作同一套查詢系統的兩種方式。在介面上點選功能區,Power Query 會自動寫出對應的 M 語言;在進階編輯器直接寫 M 語言,「套用的步驟」也會依照程式碼裡的步驟名稱顯示出來。熟悉介面操作的人,可以先用滑鼠完成大部分工作;需要重複套用或步驟比較多的時候,再改用 M 語言一次寫完。透過 ChatGPT,即使還不熟悉 M 語言的語法,也能先描述想完成的操作,再從 AI 寫出來的程式碼回頭理解每個函數的用途。

Power Query 命令與 M 語言對照:移除頂端資料列對應 Table.Skip、將第一列作為標題對應 Table.PromoteHeaders、向下填滿對應 Table.FillDown、篩選資料列對應 Table.SelectRows、合併兩年度資料對應 Table.Combine

七、複製 M 語言並查看 Power Query 結果

ChatGPT 在程式碼前面寫著:「請將進階編輯器原本的程式碼全部刪除,改貼以下內容。」程式碼放在標示「powerquery」的程式碼區塊中,開頭從 let 開始,第一個區段的註解是「1. 匯入原始 Excel」,沿用原本的 Excel.Workbook 與檔案路徑,接著是取出工作表的「原始工作表」、用 Table.Skip(原始工作表, 2) 移除前兩列的「移除前兩列」,以及把下一列升階為標題的「升階標題」。程式碼區塊右上角有「複製代碼」按鈕,按一下就能複製整段程式碼,不必自己從頭選取,再回到 Power Query 的進階編輯器,刪除原本的內容後貼上。

ChatGPT 提供的 powerquery 程式碼區塊:請將進階編輯器原本的程式碼全部刪除,改貼以下內容,右上角為複製代碼按鈕,程式碼從 let 與匯入原始 Excel 開始

在 ChatGPT 整理出完整版的程式碼之後,贊贊小屋把它貼進進階編輯器並按下「完成」,回到 Power Query 編輯器。左側「查詢」窗格中目前有三個查詢,選取的是「累計明細表- 以類型分 (3)」;公式列顯示最後一個步驟「= Table.ReorderColumns(」;中間預覽的欄位依序是「類型」「上層遊憩區」「據點中文名稱」「據點英文名稱」「縣市中文名稱」,前幾列都是「國家(自然)公園」的「陽明山國家公園」「陽明山遊客中心」「Yangmingshan Visitor Center」「臺北市」,往下是「陽明書屋」「Yangmingshuwu」。左下角狀態列顯示「12 個資料行,999+ 個資料列」,右側「套用的步驟」則從「來源」「導覽」「移除前兩列」「升階標題」,一路排列到「標準欄名」「重新命名欄位」「填滿類型」「取得文字行」「加入上層遊憩區」「填滿上層遊憩區」「只保留據點」「加入據點中文名稱」「加入據點英文名稱」「加入縣市中文名稱」「加入縣市英文名稱」「月份欄位115」「月份欄位114」等步驟。

貼上 M 語言後的 Power Query 編輯器:查詢累計明細表- 以類型分 (3) 的預覽有類型、上層遊憩區、據點中文名稱、據點英文名稱、縣市中文名稱欄位,右側套用的步驟列出移除前兩列、升階標題、標準欄名等步驟

對照一開始只有 34 個資料行、上下層分類混在同一欄的原始資料,這個查詢的預覽已經把上層遊憩區、據點名稱與縣市分成獨立的欄位;同一個「陽明山遊客中心」連續出現在好幾列,資料列也超過 999 列,可以看出原本橫向排列的月份欄位,已經轉成一個月份一列的資料。M 語言的優點,就是可以把移除標題列、填滿分類、拆分中英文、取消樞紐、合併年度與設定資料類型等多個處理階段,組合成一個完整的查詢,貼上一段程式碼,就能建立整個查詢的處理步驟。

程式碼貼上之後,整理過程也沒有因此變成看不見的黑盒子。右側「套用的步驟」會依照程式碼中的步驟名稱逐一列出,點選任何一個步驟,都能看到資料在那個階段的樣子,和用介面操作建立的查詢一樣,可以一步一步檢查。確認資料正確之後,再按「常用」索引標籤最左邊的「關閉並載入」,就能把整理好的結果輸出到 Excel 工作表。

從 M 語言到查詢預覽:複製程式碼、貼入進階編輯器、完成、檢視查詢步驟與資料預覽、關閉並載入

利用 ChatGPT 學習 Power Query M 語言

回顧這次的操作,從 Excel 的「取得資料」匯入觀光統計檔案、在導覽器選擇工作表、進入 Power Query 編輯器觀察「套用的步驟」,到打開進階編輯器看見 M 語言,贊贊小屋發現,Power Query 其實一直在背後寫程式。把這段既有的程式碼連同整理需求交給 ChatGPT,再把它寫好的 M 語言貼回進階編輯器,原本需要在介面上逐一點選的十幾個步驟,就整合成一個完整的查詢。

M 語言原本是需要花時間學習語法才能自己寫的程式語言,有了 ChatGPT 之後,學習的順序可以倒過來:先說清楚想把資料整理成什麼樣子,讓 AI 寫出程式碼,再透過「套用的步驟」逐步檢查每個函數做了什麼。課程會提供完整的 ChatGPT 對話過程,讓學員進一步了解從需求提出到 M 語言編寫的實際流程。

利用 ChatGPT 學習 Power Query M 語言:理解資料整理需求、ChatGPT 協助編寫 M 語言、Power Query 檢視查詢步驟三階段循環,從操作中學習 M 語言
發布日期:

贊贊小屋 AI 工具教學:

線上課程:ChatGPT課程

ChatGPT 課程特色圖:用 AI 學會 Excel 與 VBA 自動化

分類:AI工具、ChatGPT Excel