Skip to content

Productivity › 試算表 › Power Query

Excel 中的 Power Query (Power Query in Excel) ​

Power Query 是 Excel 內建的輕量 ETL:把「匯入 → 清理 → 轉換 → 載入」記成可重跑的步驟,資料更新後按「全部重新整理」就重做一次。本篇聚焦 Excel 介面與工作流;Applied Steps、M 語言、資料重塑與 M vs DAX 的完整說明以 Power Query(BI 工具) 為主。

什麼時候用 ​

適合不必用
每週匯入結構相近的報表、清理步驟每次都一樣一次性的小表修正
來源欄位很多、只需要一部分純人工輸入的表單
多個檔案 / 工作表要合併分析很簡單、手動成本很低
想留下別人看得懂、可審查的清理紀錄—
層負責
Power Query 編輯器匯入、清理、轉換(準備資料)
工作表檢視、公式分析、報表(消費資料)
Power Pivot / 資料模型多表關聯、DAX 量值
text
原始來源 ──► Power Query(清理、重塑)──► 工作表 Table 或資料模型 ──► 樞紐分析 / 報表

Excel 操作流程 ​

步驟位置說明
1. 取得資料資料 → 取得資料 (Get Data) / 從文字/CSV / 從資料表/範圍來源:活頁簿、CSV、資料夾、網頁、資料庫
2. 轉換開啟 Power Query 編輯器每個操作記成右側「套用的步驟 (Applied Steps)」
3. 載入常用 → 關閉並載入至…載入成 Table、只建立連線、或加入資料模型
4. 更新資料 → 全部重新整理來源檔更新後重跑全部步驟
載入選項何時用
表格 (Table)結果要直接在工作表看或寫公式
只建立連線 (Connection only)中間查詢,只給其他查詢合併用
加入資料模型 (Data Model)要在 Power Pivot 建關聯、資料量大(不受工作表列數上限)

常用轉換 ​

類別操作
清理與型別改欄名、設定資料類型、移除錯誤 / 空白列、取代值、Trim / Clean、篩選
欄位選擇 / 移除欄、分割欄(依分隔符號)、合併欄、自訂欄、條件式欄、索引欄
重塑取消樞紐 (Unpivot, wide → long)、樞紐 (Pivot)、群組依據 (Group By)
合併附加查詢 (Append)、合併查詢 (Merge)

先改欄名與資料類型,後面的分組、合併、聚合都建立在型別正確之上。

Append vs Merge ​

Append(附加)Merge(合併)
方向縱向堆疊(加列)橫向補欄(依 key)
典型12 個月份的同格式報表合成一張交易表補上產品對照表的類別
前提欄名、欄數、型別一致,否則多出 null 欄兩邊 key 型別一致、key 品質穩定
對應pandas concat、SQL UNION ALLpandas merge、SQL JOIN
Join 類型保留
左方外部 (Left Outer)主表全部列 + 對上的資料(最常用)
內部 (Inner)只保留兩邊都有的 key
左方反向 (Left Anti)主表中對不上的列,用來找漏配的 key
完整外部 (Full Outer)兩邊全部

Merge 前先想清楚主表是誰、哪些列一定要保留、對不上的 key 怎麼處理;merge 後檢查列數是否暴增(key 不唯一造成一對多)。概念與 pandas 重塑與合併 相同。

合併資料夾中的所有檔案 ​

資料 → 取得資料 → 從檔案 → 從資料夾 → 合併並轉換。Power Query 以第一個檔案為範例建立轉換函數,套用到資料夾中每個檔案並 append;之後新檔丟進資料夾、重新整理即可。檔名會成為一欄(Source.Name),可保留來源。

進階功能 ​

功能用途
查詢相依性 (Query Dependencies)檢視 → 查詢相依性:看查詢之間的上下游
進階編輯器 (Advanced Editor)查看 / 編輯整段 M 程式碼(let ... in ...,區分大小寫)
參數 (Parameters)把檔案路徑、日期、門檻抽成參數;換環境或換期間時不必複製查詢
自訂函數把一段查詢轉成函數,套用到多個來源

M 語言範例與自訂欄語法見 Power Query:M 語言與自訂欄。

實務守則 ​

守則原因
保留原始來源,不在來源檔上手動改重新整理時能完整重跑
步驟取有意義的名稱(右鍵 → 重新命名)Applied Steps 本身就是文件
不把分析邏輯全塞進 Power Query準備資料歸 Power Query,量值歸 Power Pivot
檔案路徑用參數換電腦或分享時只改一處
改了前面的步驟,檢查後面的步驟刪欄、改名會讓後續步驟出錯

同一套 Power Query 也內建在 Power BI。需要排程更新、多人共用時,見 BI 工具;要完整寫程式控制時,改用 pandas。