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

你好，我是 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，則輸出 高單價，否則輸出 一般。

➡️ 深度實戰： 想徹底搞懂何時該用「條件資料行」，何時又該升級到 M 語言來處理複雜的商業規則嗎？我們在一篇專門的文章中，對此進行了深入的探討與實戰演練：【Power Query 的 IF 條件判斷：從無程式碼到一行M語言的進階邏輯】

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

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

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)。

「合併查詢」的威力不止於整合內部表格。在實務中，我們更常利用它來連接外部 Web 數據源，以豐富和標準化我們的內部資料。一個最經典的應用，就是自動從維基百科抓取 ISO 國家代碼，來徹底解決內部數據中「國家名稱」混亂的世紀難題。

➡️ 深度實戰教學：Power Query 自動化教學：3步驟抓取維基百科，打造標準國家維度表

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

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

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

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

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

技巧七：環境切換術 (Changing Data Source)

在你享受自動化帶來的便利時，一個殘酷的現實很快就會找上門：當你的資料夾換了個位置，或是公司的伺服器換了 IP 位址，你那完美的自動化流程就會瞬間崩潰，報出一堆找不到來源的錯誤。

這就像你搬了家，卻忘了更新你的 GPS 導航系統，它依然執著地想從你的舊地址出發。

這個技巧，就是要教你如何成為一位聰明的搬家師傅，在不動到任何查詢步驟的前提下，快速、安全地告訴 Power Query：「嘿，我們搬到新家了！」。

真實痛點：從「我的電腦」到「團隊共享資料夾」

想像一下，你遵照前面的教學，在你的個人電腦桌面上，完成了整個自動化銷售報表的開發。現在，主管要求你將這個報表移到公司的網路共享資料夾上，讓整個團隊都能使用。

當你把整個專案資料夾複製過去，然後打開 Power BI 檔案，按下「重新整理」時……災難發生了，Power Query 顯示「找不到資料來源」。

Power Query 會像一個固執的管家，堅持要去你桌面的舊路徑找檔案，結果當然是一無所獲。

⚠️ 新手的陷阱：直接修改「來源」步驟的齒輪

當錯誤發生時，我們最直覺的反應，就是回到右側「套用的步驟」，找到最頂部的「來源」步驟，然後點擊它旁邊那個看起來很誘人的小齒輪 ⚙️。

點擊後，確實會彈出一個讓你重新選擇檔案路徑的視窗。你看似解決了這一個查詢的問題。但這種作法存在兩個致命的缺陷：

1. 治標不治本： 如果你的專案中有多個查詢（例如，一個連到 Monthly_Sales 資料夾，一個連到 Product_Master.xlsx），你就必須逐一地、重複地去修改每一個查詢的「來源」步驟。這不僅效率低落，而且極易遺漏。
2. 缺乏全域視野： 你只看到了單一棵樹（一個查詢），卻失去了對整座森林（所有資料來源）的掌控。你無法一目了然地知道，這個專案到底連接了多少個外部資料來源。

這是一種「頭痛醫頭、腳痛醫腳」的作法，雖然直覺，但絕非專業的工作流程。

✅ 專業的解決方案：使用「資料來源設定」(The Safest Way)

真正專業的作法，是退一步，從一個更高層級的視角來管理你的所有資料連結。

1. 在 Power Query 編輯器中，點擊「常用」索引標籤下的「資料來源設定」。
2. 在跳出的視窗中，你會看到這個專案中所有使用到的資料來源路徑，被清晰地條列出來。這就是你的「總水閥控制室」。
3. 開始搬家：
   • 針對單一檔案 (如 Product_Master): 選取 Product_Master.xlsx 這個來源，點擊下方的「變更來源...」按鈕。一個你非常熟悉的「瀏覽」視窗會彈出，你只需要像平常開啟檔案一樣，指向新位置的 Product_Master.xlsx 檔案即可。
   • 針對資料夾 (如 Monthly_Sales): 選取 Monthly_Sales 這個資料夾來源，同樣點擊「變更來源...」，然後透過「瀏覽」按鈕，指向新位置的 Monthly_Sales 資料夾。
4. 將所有來源都更新到新的路徑後，點擊「關閉」。
5. 回到主編輯器，再次按下「重新整理」。

魔法再次發生！ 所有的查詢都恢復了正常。你成功地完成了搬家，而且完全沒有動到任何一行程式碼，也沒有改變任何一個你精心設定的清理步驟。

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

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

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

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

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

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

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

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

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

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

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

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

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

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