Power Query 終極指南：從 Excel 地獄到自動化天堂的10大核心技巧 (附練習檔)

你好，我是 Kiro。

如果你正在閱讀這篇文章，很有可能，你經歷過這樣的「Excel 地獄」：每個月初，你都得花上數小時、甚至一整天，重複著同樣的噩夢——手動複製、貼上來自不同檔案的報表，用 VLOOKUP 對到眼花，再小心翼翼地調整格式，只為做出一份老闆要的月報。

這個過程不僅枯燥、耗時，更可怕的是，只要一個小小的手誤，整份報告的數字就可能全盤皆錯。

今天，我們要終結這場噩夢。

這篇文章，就是你的「出獄指南」。我們將要學習的，是一個名為 Power Query 的工具。但請先忘掉你對「新工具」的恐懼，因為它並不是一個需要從零學起的陌生軟體。

真相是：Power Query 是 Excel 和 Power BI 內建的、共享的「數據自動化引擎」。

無論你是想將 Excel 技能提升到極致，還是準備踏入 Power BI 的世界，你學的都是同一套核心武功。這篇文章將以 Power BI 的介面為主進行示範，但請記住，你在這裡學到的每一個技巧，幾乎都能在 Excel 的 Power Query 編輯器中完美重現。

這是一本 Power Query 的武功總集，我們將透過一個「打造自動化區域銷售儀表板」的完整專案，讓你一次掌握十大核心技巧。

邊讀邊做，學得更快！

我為這篇文章準備了一套完整的 Excel 練習檔。免費訂閱我的電子報，即可立即前往會員資源中心下載，並跟著文章一起動手操作！

📂 練習資料夾 Automated_Sales_Report 包含：Monthly_Sales 資料夾 (內含多個月份的銷售檔)Product_Master.xlsx (產品主檔)Regional_Targets_Wide.xlsx (主管提供的寬格式目標檔)Sales_2024_03.xlsx (未來要新增的3月資料)

🎁 現在就打開 Power BI Desktop，跟著我一步一步操作吧！

為什麼 Power Query 是遊戲規則的改變者？

Power Query 的革命性，在於它將數據處理從「一次性的手動操作」，轉變為「可重複的自動化流程」。我們的專案目標就是自動整合 Monthly_Sales 資料夾裡的所有檔案。過去你需要手動複製貼上，現在你只需設定一次，未來只要把三月份的檔案丟進去，按下「重新整理」即可。

Power Query 會錄製你的每一步操作。錄製一次，終身受用。

技巧一：連接萬物 & 自動化合併 (The Foundation)

我們的第一個任務，是將 Monthly_Sales 資料夾內所有的銷售數據自動合併。

1. 在 Power BI 主畫面，點擊「取得資料」>「檔案」。
2. 選擇「資料夾」並點擊「連線」，然後選擇 Automated_Sales_Report/Monthly_Sales 資料夾。
3. Power BI 會顯示資料夾內的所有檔案。點擊「合併」>「合併與轉換資料」。
4. 在範例檔案視窗中，選擇 Sheet1 作為範本，然後點擊「確定」。

魔法發生了！ 現在，請將目光聚焦到 Power Query 編輯器左側的「查詢窗格」。你會看到 Power Query 自動為你建立了多個項目，其中最關鍵的是這兩個：

• 主查詢 (Monthly_Sales): 這是最終合併後的大表，名稱預設為你選擇的資料夾名稱。
• 輔助查詢 (轉換範例檔案): 這是一個特殊的「轉換範本」查詢。

【Power Query 的黃金準則】：先清理「範本」，再處理「總表」

在動手之前，你必須先建立一個清晰的SOP心法，這將決定你的自動化流程是否穩固：

把 Power Query 想像成一座工廠的兩段式產線：第一站：零件標準化 (在「轉換範例檔案」中操作)
任何需要對每一個原始檔案進行的內部清理，都應該在「轉換範例檔案」這個查詢中完成。這就像在組裝汽車前，先把每一顆螺絲都清洗、打磨到標準規格。檢查點： 問自己，「這個動作，是不是每個月份的銷售表都需要做一次？」如果是，就在這裡做。第二站：總成組裝 (在「主查詢」中操作)
只有當所有零件都標準化之後，才回到「主查詢」進行跨表格的整合或基於合併後結果的計算。這就像在主要產線上，將標準化的引擎、輪胎、車殼組裝成一台完整的汽車。檢查點： 問自己，「這個動作，是不是需要看到所有月份的資料後才能做？」如果是，就在這裡做。

現在，讓我們遵循這個黃金準則，開始我們的清理之旅。

技巧二：基礎建設 - 結構與類型的奠基

在進行任何內容清理之前，我們必須先完成兩項最重要的基礎建設：創造正確的欄位結構，以及設定正確的資料類型。這一步將決定後續所有操作的成敗。

📍 當前位置：請點擊左側查詢窗格中的「轉換範例檔案」查詢。

步驟 A: 創造結構 (欄位外科手術)

我們的原始資料沒有獨立的 Region 欄位，它是被隱藏在 OrderID 裡 (例如 台北-1001)。

1. 選取 OrderID 欄，點擊「常用」>「分割資料行」>「依分隔符號」。
2. 選擇「--自訂--」，分隔符號輸入 -。
3. 【專家習慣養成】 操作完成後，Power Query 會自動增加一個「已變更類型」步驟。立刻點擊它旁邊的 X 將其刪除。我們將主動掌控所有類型變更。
4. 現在，將新產生的 OrderID.1 和 OrderID.2 欄位，分別重新命名為 Region 和 OrderNumber。

步驟 B: 設定類型 (最重要的標準化)

這是「零件標準化」產線的核心品管程序。在進行任何計算或內容清理前，必須先確保所有欄位的類型都正確。

1. 按住 Ctrl + A 鍵，一次性選取所有欄位。
2. 點擊「轉換」>「偵測資料類型」。讓 Power Query 為你做第一次的自動判斷。
3. 手動覆核與修正： 仔細檢查每個欄位標題左側的圖示，確保它們都正確：
   • OrderDate: 🗓️ (日期)
   • Quantity: 123 (整數)
   • UnitPrice: 1.2 (小數)
   • OrderNumber: ABC (文字) - 這一點很重要！訂單號碼雖然是數字，但我們不對它進行計算，將其設為文字可以避免未來潛在的問題。
   • 其他所有欄位應為 ABC (文字)。

完成這一步後，你的資料才算真正擁有了穩固的「地基」。

技巧三：內容拋光 (在「範本」中標準化內容)

地基打好後，我們就可以開始對內容進行「拋光」了。

📍 當前位置：請繼續停留在「轉換範例檔案」查詢中。

• 修剪 (Trim) & 取代值 (Replace): 新建立的 Region 欄位有「台北」和「臺北」的混用問題。選取 Region 欄，先右鍵 -> 「轉換」 -> 「修剪」 來清除潛在空格，再右鍵 -> 「取代值」，將「臺北」統一為「台北」。
• 格式化 (Format): ProductSKU 欄位有大小寫混亂。選取該欄，右鍵 -> 「轉換」 -> 「大寫」。
• 取代特殊字元: Notes 欄位中有換行符號。選取該欄，點擊「常用」>「取代值」，在「要尋找的值」輸入 #(lf)，並在「進階選項」中勾選「以特殊字元取代」，將其清除。

技巧四：智慧邏輯判斷 (在「範本」中新增標籤)

因為我們在技巧二中已經將 UnitPrice 設定為數字格式，現在可以安心地進行比較了。

📍 當前位置：請繼續停留在「轉換範例檔案」查詢中。

1. 點擊「新增資料行」>「條件資料行」。
2. 設定邏輯：新資料行名稱 ValueTier，如果 UnitPrice 大於 3000，則輸出 高單價，否則輸出 一般。

技巧五：資料變形術 (獨立練習)

這個技巧是獨立的練習，讓我們暫時跳出目前的流程。

1. 在左側查詢窗格的空白處按右鍵 -> 「新增查詢」 -> 「Excel 活頁簿」，連接到 Automated_Sales_Report/Regional_Targets_Wide.xlsx。
2. 【專家習慣養成】 在左側查詢窗格，對著剛剛建立的新查詢 (Sheet1) 按右鍵，選擇「重新命名」，將其改為 Regional_Targets。為查詢取個有意義的名稱是維持專案清晰的關鍵。
3. 你會看到一張典型的「寬表格」。選取 Region 欄，然後右鍵 -> 「取消其他資料行的樞紐分析」。
4. 新產生的「屬性」欄包含 Q1_Target, Q2_Target 等。選取此欄，點擊「轉換」>「取代值」，將 _Target 取代為空白。
5. 將「屬性」和「值」欄位，重新命名為 Quarter 和 Target。一鍵完成轉換！

(➡️ 深度實戰：想徹底搞懂為什麼這一步如此重要嗎？請務必閱讀【長表格 vs. 寬表格：一篇搞懂資料清理的核心】)

技巧六：數據大一統 (在「總表」中進行總成組裝)

現在，所有銷售資料的「零件」都已經標準化了。是時候回到我們的主產線 (Monthly_Sales)，把它們和其他外部零件（產品資訊）組裝在一起了。

📍 當前位置：請點擊左側查詢窗格中的主查詢 Monthly_Sales。

⚠️ 常見陷阱：回到主查詢時出現錯誤？

在你進行任何操作前，請先檢查右側的「套用的步驟」。你可能會看到某個步驟出現錯誤，提示「找不到資料行 'OrderID'」。

• 原因： 這是正常的！因為我們在「轉換範例檔案」中已經將 OrderID 拆分並移除了。但主查詢中由系統自動生成的某個早期步驟，仍然在嘗試尋找這個已經不存在的舊欄位。
• 解決方案： 在「套用的步驟」中，找到那個報錯的步驟（通常是名為「已變更類型」的步驟），點擊它旁邊的 X 將其刪除。錯誤就會立刻消失。

開始合併：

1. 首先，我們需要將「產品主檔」這個外部零件也導入工廠。在左側查詢窗格空白處按右鍵 -> 「新增查詢」，連接到 Automated_Sales_Report/Product_Master.xlsx。
2. 【專家習慣養成】 將新查詢重新命名為 Product_Master。
3. 回到我們的主查詢 Monthly_Sales。
4. 點擊「常用」>「合併查詢」。
5. 在跳出的視窗中，將主銷售表 (Monthly_Sales) 與 Product_Master 表，都透過 ProductSKU 欄進行「左方外部」聯結。
6. 點擊新產生的 Product_Master 欄位標題右側的「展開」圖示 🔄。
7. 在跳出的選單中，勾選 ProductName, Category 和 UnitCost。
8. 【關鍵一步】 取消勾選下方的「使用原始資料行名稱作為前置詞」。這個小動作能確保你展開後的欄位名稱是乾淨的 (Category)，而不是冗長的 (Product_Master.Category)。

見證奇蹟的時刻：自動化的真正威力

你已經成功建立了一個完整的自動化數據清理流程。現在，讓我們來驗收成果。

1. 在 Power Query 編輯器中，點擊左上角的「關閉並套用」，將乾淨的資料載入到 Power BI 模型中。
2. 模擬下個月的到來： 現在，請把三月的資料 Sales_2024_03.xlsx，放到Monthly_Sales 資料夾。
3. 見證奇蹟： 回到 Power BI Desktop 主畫面，在「常用」索引標籤中，找到並點擊那個神奇的「重新整理」按鈕。

恭喜！你已經用一種比 VLOOKUP 更高效、更穩固的方式，完成了跨表資料的整合。

(➡️ 深度實戰：想了解不同聯結類型的差異嗎？請參考【合併 vs. 附加查詢：VLOOKUP 的終極進化】)

技巧七至十：晉升數據架構師 (未來篇)

恭喜你！掌握了以上六大核心技巧和「黃金準則」，你已經具備了打造專業自動化流程的堅實基礎，足以解決絕大多數的真實商業問題。你已經不再是過去那個被 Excel 重複勞動所困住的自己了。

但對於追求卓越、渴望將技能提升至「架構師」等級的你，還有四個進階主題，它們是通往極致效率與彈性的終極鑰匙。

為了確保每個主題都能得到應有的深度，我將在未來的文章中，為它們撰寫專屬的深度實戰指南。在這裡，我先為你做個精彩預告，讓你一窺數據自動化世界的更高層次：

預告一、技巧七：參數的魔法 (Parameters)

你是否曾將報表交給同事，卻因為檔案路徑不同而頻繁出錯？參數將徹底解決這個問題。你將學會如何讓你的報表變得完全「可攜」，在任何環境下都能一鍵更新，這是實現團隊協作與報表標準化的關鍵一步。

預告二、技巧八：M 語言初探 (Introduction to M Language)

你剛剛完成的每一步點擊，其實都在背後生成了一種名為 M 語言 的程式碼。我們將會一起「揭開引擎蓋」，學習如何透過微調這些程式碼，來完成圖形介面無法實現的、更複雜的客製化轉換。這將是你突破工具限制、實現無限可能的超能力。

預告三、技巧九：數據的濃縮藝術 (Grouping)

當你的資料量達到數十萬、甚至數百萬筆時，效能就是一切。分組是在 Power Query 階段就對數據進行「預先聚合」的秘密武器，能極大地減少載入到最終模型的資料量，讓你的儀表板快如閃電。

預告四、技巧十：無中生有 (Indexing & Custom Columns)

有時候，你需要的洞見，來自於原始資料中不存在的欄位。自訂與索引資料行就是你的「煉金術」，你將學會如何憑空創造出新的資訊——從計算利潤 ([售價] - [成本])，到為每一筆資料賦予獨一無二的流水號，讓你的分析維度更加豐富。

結論：你的自動化之旅，由此開始

恭喜你，你已經完成了 Power Query 十大核心招式的巡禮，更重要的是，你學會了「先範本，後總表」的專業工作流程。

請記住，Power Query 不僅僅是一個工具，它是一種思維方式——一種將混亂、重複的手動流程，拆解、重構成為清晰、可靠的自動化系統的思維方式。主動審視並掌控「套用的步驟」，正是這種思維的核心體現。

掌握了它，你釋放的不只是雙手，更是你的大腦。你將有更多的時間去思考數據背後的商業意義，而不是深陷在數據處理的泥沼中。這，就是你從「報表工匠」蛻變為「數據分析師」的起點。

🚀 從心法到實戰，只差一步

覺得這些技巧很強大，但渴望將它們應用在真實的商業專案中嗎？

在我的 Hahow 好學校線上課程 【Power BI x AI 終極實戰：打造高效數據分析工作流】 中，我們將會深入應用這篇文章提到的所有技巧，親手處理來自政府公開資料庫的真實、混亂的原始數據，並最終打造出三個令人驚豔的商業儀表板。

這不只是一堂工具課，更是一趟將你打造成真正數據專家的旅程。

👉 點擊這裡，立即加入超過千名學員的行列，開啟你的數據自動化之旅！

🎁 想持續升級你的數據決策系統嗎？

覺得這篇文章對你有幫助嗎？這只是個開始。

免費加入，立即解鎖『會員資源中心』 (內含完整電子書、練習檔案與更多專屬內容)！

你將不僅能立即下載排版精美的 【數據分析師的養成心法 (2025 終極指南)】 完整版電子書 (PDF) ，更重要的是，你將開始每週收到我的獨家框架、實踐案例與工具推薦。

讓我們一起，將數據轉化為智慧，打造屬於自己的理想人生。