如何在 Excel 中使用 Power Query：完整指南

Power Query 是 Excel 中功能強大的商業智能工具，可讓您根據需要導入、清理和轉換數據。如果您曾經花費數小時清理數據（我們有）或努力合併來自各種來源的數據（我們也有），此功能將使您的生活更輕鬆。

學習如何在 Excel 中使用 Power Query 的最佳方法是執行一些常見任務，例如導入數據和使用 Power Query 編輯器轉換生成的表格並合併數據。

本文通過分步指南展示清晰的 Power Query 示例，幫助您入門。通過掌握 Power Query，您可以將您的工作提升到一個新的水平。

你準備好了嗎？我們開工吧。

目錄

• 如何知道您是否可以訪問 Power Query
• 如何使用 Power Query 導入數據
• 如何使用 Power Query 編輯器
  • 數據預覽
  • 特性
• 如何使用多個數據源
• 如何使用 Power Query 追加數據
• 如何使用 Power Query 合併數據
• 使用 Power Query 轉換數據的 5 種方法
  • 1.擴展和壓縮數據
  • 2.排序數據
  • 3.格式化數據
  • 4. 添加和刪除列
  • 5. 改變數據類型
• 使用公式和 M 語言
  • 公式欄
  • 編寫和修改 M 代碼
• 什麼是 M 語言語法？
• 如何刷新數據連接
• 如何將 Power Query 與數據模型集成
• 如何使用 Power Query 和 VBA 實現自動化
• Power Query 高級功能
• 協作與共享
• 讓我們總結一下

如何知道您是否可以訪問 Power Query

在開始使用 Power Query 之前，您需要確保您擁有具有該功能的 Excel 版本。

以下是按版本分類的 Power Query 的簡要概述：

• Excel 2010 和 Excel 2013：作為插件提供，需要下載和安裝。

• Excel 2016：作為“獲取和轉換”集成到 Excel 中。

• Excel 2019：改進了性能和功能。

• Excel 2021 和 Excel for Microsoft 365：與雲數據源集成。

• Excel for Mac：僅適用於 Microsoft 365 版本 16.69 及更高版本。

要驗證該工具是否已集成到您的 Excel 工作簿（2016 或更高版本）中，請轉到“數據”選項卡並查找此圖片中的“獲取和轉換數據”部分：

如果您在 Windows 上有 Excel 2010 或 2013，則可以下載、安裝和啟用 Power Query 加載項。

如何使用 Power Query 導入數據

Power Query 工具允許您導入和連接到範圍廣泛的數據源：

• 文件，包括 CSV、XML 和 JSON 格式

• Web URL 或 API 端點

• 數據庫，包括 Access、SQL Server、Oracle 和大多數現代數據庫

• Azure 服務、SharePoint 文件和 OData 源

最簡單的數據源是一個包含多行數據的簡單 CSV 文件。創建一個文本文件並添加這四個名字和姓氏，用逗號分隔：

• 喬，博客

• 安妮，瑞安

• 詹姆斯，斯圖爾特

• 瑪麗·布朗

請務必使用 .CSV 擴展名保存文件。您的文件將如下所示：

要使用 Power Query 加載數據，請執行以下步驟：

1. 轉到“數據”選項卡。

2. 單擊“獲取和轉換數據”選項卡中的“獲取文件”按鈕。

3. 選擇您的示例文件。

您可能需要等待幾秒鐘，該工具才能將數據拉入預覽窗格。Power Query 即使是一個小的導入也會使用相當多的機器資源，所以請耐心等待。

提示：關閉其他應用程序以加快性能。

Power Query 將顯示帶有數據預覽的“文本/CSV 導入”窗口。

如果數據看起來正確，您可以繼續加載或轉換數據：

• 加載按鈕會將數據直接加載到 Excel 中。

• “轉換數據”按鈕可讓您在加載數據之前修改數據。

您是否注意到示例文件沒有標題？如果它直接加載到 Excel 中，標題將是“Column 1”和“Column 2”。

相反，您可以將數據拉入 Power Query 編輯器並進行一些更改。為此，請單擊“轉換數據”按鈕。

Power Query 編輯器現在打開，這將我們帶到下一部分。

如何使用 Power Query 編輯器

在本節中，我們將探討使用 Power Query 編輯器的一些重要方面。

數據預覽

在數據預覽區域中，您可以查看導入的數據並與之交互。您可以篩選行、修改列數據類型以及重塑數據以滿足您的需要。

完成後，Excel 會將轉換後的數據加載到工作表中。

使用示例文件，您可以直接在此窗格中編輯列名。

為此：

1. 右鍵單擊列標題。

2. 在下拉菜單中單擊“重命名”。

3. 在標題框中輸入首選名稱。

當您進行更改時，預覽窗格右側的“應用步驟”部分會更新以反映每個轉換，使您可以根據需要跟踪和修改數據處理步驟。

特性

為您的查詢設置屬性對於組織和跟踪您的工作至關重要。

“查詢設置”窗格位於 Power Query 窗口的右側。

1. 單擊“所有屬性”鏈接。

2. 編輯查詢的名稱以使其具有描述性。

3. 添加說明以提供更多信息。

很容易忘記上週查詢的目的。在這種情況下，查詢名稱已更改，並添加了說明查詢目的的說明。

如果您想了解有關可用選項的更多信息，請查看此Power Query 編輯器概述。

如何使用多個數據源

Power Query 編輯器提供了兩種主要的方法來組合來自多個源的數據：追加和合併。

Appending 垂直連接表，將它們堆疊在彼此之上。合併允許您根據公共列連接表。

要查看其工作原理，請創建與第一個結構相同的第二個小 CSV 文件。這次，將兩個名字（名字和姓氏）放入文件中：

• 喬，博客

• 勞拉，甘蔗

請注意，其中一個名稱與前一個文件重複。

現在按照以下步驟將數據提取到您的 Microsoft Excel 工作簿中。

1. 關閉 Power Query 編輯器窗口並保存更改。

2. 添加一個新的工作表。

3. 按照上一節中的步驟導入第二個文件。

4. 單擊變換按鈕。

5. 將列標題重命名為“名字”和“姓氏”。

Excel 工作表中的右窗格顯示當前的查詢和連接。您現在應該在窗格中看到兩個查詢。

如何使用 Power Query 追加數據

在本節中，您會將第二個示例 CSV 文件附加到第一個。

請按照以下步驟附加數據：

1. 右鍵單擊 Queries & Connections 窗格中的第一個表（Customer Names）。

2. 選擇“編輯”以在 Power Query 編輯器中打開表格。

3. 在 Combine 部分點擊 Append Queries。

4. 從要追加的表的下拉列表中選擇第二個表。

結果將是第一個表中的兩個額外行。

請注意，一行是重複的——這就是追加操作的工作原理。它將一個表中的數據添加到另一個表中，而不管數據是否已經存在。

您可以通過以下步驟刪除 Power Query 編輯器中的重複數據：

1. 單擊“名字”列標題。

2. 按住“Ctrl”鍵（或 Mac 上的“Cmd”鍵）。

3. 單擊兩個列標題。

4. 在 Power Query 編輯器功能區的主頁選項卡中單擊“刪除行”。

5. 從下拉菜單中選擇“刪除重複項”。

如何使用 Power Query 合併數據

合併數據時，必須選擇查詢之間的匹配列。

假設您有兩個以逗號分隔的文件，其中一個包含客戶電話號碼，另一個包含地址。這兩個文件看起來像這樣：

按照前面部分中的說明將每個文件加載到它自己的 power 查詢中。然後你可以練習合併表格。

對於我們的示例數據，兩列（名字和姓氏）都應被選為匹配列。按住Ctrl鍵可選擇多列。

按著這些次序：

1. 右鍵單擊查詢和連接窗格中的第一個表。

2. 選擇“編輯”以在 Power Query 編輯器中打開表格。

3. 從“組合”組中選擇“合併查詢”。

4. 在表的下拉列表中選擇第二個表。

5. 單擊第一個表中的第一列。

6. 按住 Ctrl 鍵並單擊第一個表中的第二列。

7. 按住 Ctrl 鍵並單擊第二個表中的兩列。

8. 將默認連接運算符保留為“Left Outer”。

9. 單擊“確定”應用更改。

“左外”連接運算符確保消除重複數據。

在 Power Query 編輯器中，您將看到一個名為“表”或“嵌套表”的新列，其中包含來自第二個查詢的合併數據。

1. 單擊此列標題中的展開圖標（兩個箭頭）。

2. 取消選中“使用原始列作為前綴”框（這避免了長列名稱）。

3. 單擊“確定”。

4. 單擊“關閉並加載”。

合併表有四列，合併了電話和地址詳細信息。您可能還有一些整理工作要做。

例如，您可能想要編輯表的名稱 (Merge1) 和下圖中的地址列。

使用 Power Query 轉換數據的 5 種方法

使用 Power Query 可以輕鬆轉換 Excel 文件中的數據，使您能夠有效地清理、組織和操作數據。

一些最常見的數據轉換方法是：

• 擴展和壓縮數據

• 排序數據

• 格式化數據

• 添加和刪除列

• 改變數據類型

1.擴展和壓縮數據

擴展和壓縮數據對於使數據集更易於管理非常重要。Power Query 允許您展開或折疊表格以獲得更有條理的視圖。

上一節向您展示瞭如何通過包含相關表中的數據來進行擴展。

您還可以將其壓縮為僅包含基於您選擇的聚合（例如總和、計數或百分比）的匯總數據。

2.排序數據

要對數據進行排序，您可以通過升序或降序值快速更改順序。

單擊列名稱旁邊的向下箭頭，然後選擇“升序排序”或“降序排序”。

3.格式化數據

Power Query 編輯器中的轉換選項卡提供了很多選項。

例如，格式下拉菜單可讓您將列或整個表格中的所有數據更改為大寫或小寫。

4. 添加和刪除列

Power Query 簡化了添加、刪除和修改列的過程。

要刪除列，只需右鍵單擊不需要的列的標題，然後選擇“刪除”。這將從表中永久刪除該列及其所有數據。

要添加新列，請選擇您希望新列出現的現有列。然後使用“從示例添加列”功能創建自定義列或在兩個或多個列之間執行計算。

5. 改變數據類型

使用一致且準確的數據類型對於準確分析至關重要。Power Query 允許您輕鬆更改數據類型：

1. 右鍵單擊列標題。

2. 選擇“更改類型”。

3. 從提供的列表中選擇所需的數據類型。

在處理百分比數據時，更改數據類型特別有用。

例如，您可能需要將小數點轉換為百分比，或將基於文本的百分比轉換為數字百分比，以便於計算和比較。

使用公式和 M 語言

在 Excel 中使用 Power Query 時，您會遇到 M 語言，它是創建高級公式、處理數據和生成強大查詢的核心。在本節中，您將了解：

• 公式欄

• M代碼

• M語言語法的基本要素

公式欄

Excel 的 Power Query 編輯器中的公式欄是您輸入公式和 M 代碼的區域。

要使用它，請通過從“查詢和連接”窗格中選擇一個查詢來轉到 Power Query 編輯器。公式欄位於數據預覽窗格上方。

編寫和修改 M 代碼

要在 Excel 中創建多功能且功能強大的查詢，您需要編寫和修改稱為 M 代碼的 Power Query 語言。以下是有關如何開始的一些提示：

• 使用 Applied Steps 窗格編寫分步代碼，使跟踪和修改查詢邏輯變得容易。

• 將 M 代碼與 Excel 表格、數據透視表和外部數據源（如 Azure 和文本文件）相結合，以創建動態和自動化的報告。

• 調整查詢選項以優化您的數據加載首選項並提高性能。

當您熟悉 M 代碼時，您會發現您可以自動化和優化許多過程，使您在 Excel 中的數據分析更加高效和強大。

要查看查詢背後的代碼，請執行以下步驟：

1. 在 Power Query 編輯器中打開查詢。

2. 單擊“主頁”選項卡中的“高級編輯器”。

這是我們的示例合併表背後的代碼（您的可能看起來不同）：

什麼是 M 語言語法？

了解 M 語言語法對於創建有效的 Power Query 公式至關重要。請記住以下幾點：

• M 語言區分大小寫，因此請注意大小寫。

• 使用方括號 [] 來引用列、記錄和列表。

• 要引用查詢中的前一步，請使用其變量名稱（例如，#”Step Name”）。

• 使用“in”語句輸出查詢公式步驟。

隨著您獲得Power Query 語言流的經驗，您將發現轉換和分析數據的新方法，使您的 Excel 工作簿成為更有價值的資產。

看看下面的視頻，是時候拿出刷子清理你凌亂的數據了。

如何刷新數據連接

Power Query 的主要功能之一是能夠輕鬆刷新 Power Query 連接。

當您的數據源已更新並且您需要工作簿中的最新信息時，您可以在“查詢和連接”窗格中刷新數據連接。

如果您沒有在工作表右側看到窗格，您可以通過轉到數據表並單擊“查詢和連接”來打開它。

當“查詢和連接”窗格打開時，它會列出所有查詢。按著這些次序：

1. 右鍵單擊要刷新的查詢。

2. 從上下文菜單中選擇刷新。

這將使用來自已連接數據源的最新數據更新您的工作簿。

如何將 Power Query 與數據模型集成

Excel 的數據模型是一項強大的功能，可讓您根據數據關係創建商業智能工具，如數據透視表。

數據模型基本上是一個隱藏的數據庫，用於保存導入的數據和表之間的關係。

它是 Power Pivot 功能的一部分，內置於當前版本的 Excel 中。您可能必須將 Power Pivot 作為加載項安裝才能在以前的版本中訪問它）。

您可以通過以下步驟將 Power Query 數據連接與數據模型集成：

1. 如前幾節所述，使用 Power Query 加載數據。

2. 不要將它直接加載到工作簿中，而是從“加載”菜單中選擇“加載到”。

3. 在“導入數據”對話框中選中“將此數據添加到數據模型”複選框。

如何使用 Power Query 和 VBA 實現自動化

通過 VBA (Visual Basic for Applications) 集成，您可以在 Power Query 中創建自定義數據連接並自動執行數據轉換步驟。

您可以利用現有的 VBA 技能，通過合併來自 Microsoft Access、SharePoint 和 Analysis Services 等平台的數據來構建高級數據模型。

儘管 Power Query 沒有對 VBA 的本機支持，但您可以使用 VBA 代碼間接打開、刷新和操作 Power Query 查詢。

這使您可以創建強大的自動化數據工作流並簡化數據分析過程。

下面是一些示例代碼，用於刷新當前工作簿中的高級查詢。

:

將 wb 調暗為工作簿，將 pq 調暗為對象

設置 wb = ThisWorkbook

設置 pq = wb.Queries(“客戶名稱”)

 pq.刷新

這是 Excel VBA 宏中的代碼：

Power Query 高級功能

Excel 的 Power Query 提供了大量高級功能來簡化和增強您的數據分析過程。通過學習如何有效地使用這些功能，您可以將數據分析技能提升到一個全新的水平。

內置的數據分析工具包括：

• 用於將復雜邏輯應用於數據轉換的條件列。

• 分組和聚合功能以有意義的方式匯總您的數據。

• Pivot 和 Unpivot 操作重塑您的數據以便於分析。

您會在 Power Query 編輯器的“轉換”選項卡中找到其中的許多功能。

此外，您可以將 Power Query 連接到LuckyTemplates 等商業智能工具，以獲得更高級的數據分析和可視化選項。

協作與共享

與團隊成員共享數據連接、查詢和轉換對於有效協作至關重要。

您可以通過將工作簿保存到網絡驅動器、SharePoint或其他一些共享存儲位置來與查詢共享工作簿。

您還可以通過以下步驟導出單個查詢背後的 M 代碼：

1. 在 Power Query 編輯器中編輯查詢。

2. 單擊“主頁”選項卡中的“高級編輯器”。

3. 在高級編輯器窗口中復制整個 M 代碼。

4. 打開文本編輯器（例如，記事本）並將復制的 M 代碼粘貼到文本編輯器中。

5. 保存文本文件並將其發送給您的同事。

讓我們總結一下

Power Query具有一系列功能和用戶友好的界面，是希望有效處理外部數據的 Excel 用戶的必備工具。

這個有價值的功能有助於簡化創建報告、數據清理和對數據表執行複雜分析的過程。使用

在處理 Excel 文件時，Power Query 最終可以節省您的時間並提高效率。