使用 Power Query 來轉換資料

使用 Power Query，你可以連接許多不同的資料來源，並將資料轉換成你想要的形狀。

在本文中，您將學習如何使用 Power Query 建立查詢，方法是發現：

  • Power Query 中的 Get Data 體驗如何運作。
  • 如何使用並善用 Power Query 使用者介面。
  • 如何執行常見的轉換，例如分組和合併數據。

如果你是Power Query新手，可以在開始前註冊免費試用版Power BI。 你可以利用 Power BI 資料流來嘗試本文中描述的 Power Query Online 體驗。

你也可以免費下載Power BI Desktop。

本文中的範例會連線到並使用 Northwind OData 資料源。

連接至 OData 資料流

注意

想了解更多包含Power Query的Microsoft產品資料來源，請造訪 Where to get data。

首先，從取得資料體驗中找到 OData 供電連接器。 您可以在右上角的搜尋列中搜尋 OData。

強調 OData 連接器資料取得體驗的「選擇資料來源」頁面截圖。

選擇這個連接器後，畫面會顯示連線設定和憑證。

  • 在 URL 欄位中，輸入 Northwind OData 摘要 URL：https://services.odata.org/V4/Northwind/Northwind.svc/
  • 針對 內部部署數據閘道，請保留為無。
  • 針對 認證類型，請保留為匿名。

請選取 [下一步] 按鈕。

Navigator 現在會開啟，您可以在其中選取要從數據源連線到的數據表。 選擇 Customers 資料表以載入資料的預覽，然後選擇 轉換資料。

Power Query Navigator 頁面截圖，強調 Customers 表格。

對話框接著將客戶資料表的資料載入 Power Query 編輯器。

將您的數據連接、指定驗證方法、選取要連接的特定物件或資料表的過程稱為 取得資料體驗。 此經驗詳見《 獲取資料總覽 》文章。

注意

想了解更多關於 OData 供電接頭的資訊，請前往 OData Feed。

Power Query 編輯器的組成部分

小提示

要在 Power BI 中找到 Power Query 編輯器，請在查詢中選擇 Transform data。

Power BI 主頁列的截圖，其中以醒目方式顯示了 Transform data 按鈕。

Power Query 編輯器代表 Power Query 的使用者介面。 在此使用者介面中，您可以新增或修改查詢、藉由將描述分組或新增至查詢步驟來管理查詢，或使用不同檢視將查詢及其結構可視化。 Power Query 使用者介面包含五個不同的元件。 下表中的數字對應於螢幕擷取畫面中的編號標註。

Power Query 使用者介面截圖，每個元件都標示並編號。

# 組件 Description
1 功能區 功能區導航體驗，提供多個分頁以新增變換、選擇查詢選項，並使用不同功能區按鈕完成各種任務。
2 查詢面板 您可以查看所有可用的查詢。
3 目前檢視 你的主要工作視圖，預設會預覽你查詢的資料。 您也可以啟用 圖表檢視 以及資料預覽檢視。 您也可以在 架構檢視 和數據預覽檢視之間切換，同時維護圖表檢視。
4 查詢設定 目前選擇的查詢視圖，包含相關資訊，如查詢名稱、查詢步驟及各種指標。
5 狀態列 一個顯示查詢相關重要資訊的條狀圖，例如執行時間、總欄位與列數，以及處理狀態。 此列也包含用來變更目前檢視的按鈕。

注意

目前 Power Query 線上版僅提供結構與圖表檢視。

在你在 Power Query 編輯器中轉換資料之前，了解如何展開或摺疊 UI 窗格，以及切換檢視圖會有幫助。 選擇合適的視窗可以讓您專注於對您來說最重要的檢視。 以下章節將介紹 Power Query 使用者介面中可用的窗格與檢視。

Power Query 編輯器功能區

功能區是您在 Power Query 編輯器中找到大部分轉換和動作的元件。 它有多個索引標籤，其值取決於產品整合。 每個分頁都有特定的按鈕和選項，其中一些可能在整個 Power Query 體驗中顯得重複。 這些按鈕和選項可讓您輕鬆存取您可能需要的轉換和動作。

Power Query使用者介面主頁的標準色帶檢視截圖。

Power Query 介面反應靈敏，會嘗試調整螢幕解析度，讓你獲得最佳體驗。 在您想要使用精簡版功能區的情況下，功能區右下角也有折疊按鈕，可協助您切換至精簡功能區。

Power Query使用者介面主頁頁的緊湊色帶檢視截圖。

您可以選取功能區右下角的展開圖示，切換回標準功能區檢視。

展開與摺疊 Power Query 窗格

你可能會注意到，在 Power Query 的使用者介面中，有一些圖示可以幫助你摺疊或展開某些檢視或區段。 例如，[查詢] 窗格右上角有一個圖示，會在選取時折疊查詢窗格，並在再次選取時展開窗格。

螢幕快照，強調在查詢窗格上用來折疊和展開的圖示。

在 Power Query 檢視間切換

除了能在 Power Query 使用者介面中摺疊某些窗格和區塊外，你也可以切換顯示的檢視內容。 要切換視圖，請到功能區中的 View 分頁，找到 Preview 和 Layout 群組，這些群組控制Power Query使用者介面的外觀。

建議您嘗試所有這些選項，尋找最讓您感到舒適的檢視和版面配置。 例如，從功能區選取 [架構檢視]。

強調Power Query功能區的檢視索引標籤內的結構檢視按鈕。

狀態列右側也包含圖表、數據和架構檢視的圖示。 您可以使用這些圖示在檢視之間變更。 您也可以使用這些圖示來啟用或停用您選擇的檢視。

查詢窗格已折疊的螢幕快照，目前視圖已切換至架構檢視，並強調了架構檢視圖示。

什麼是 Power Query 中的結構檢視

架構檢視可讓您快速且直接地與數據表架構的元件互動，例如數據行名稱和數據類型。 當您想要執行架構相關動作時，建議您使用架構檢視。 這些動作可以移除數據行、重新命名數據行、變更數據行數據類型、重新排序數據行，或複製數據行。

注意

若要深入瞭解架構檢視，請移至 使用架構檢視。

例如，在架構檢視中，選取 [訂單] 和 [CustomerDemographics] 欄旁的複選標記，然後在功能區中選取 [移除欄] 動作。 此選擇會套用轉換，以從您的資料中移除這些欄位。

已選取 [訂單] 和 [CustomerDemographics] 數據行的螢幕快照，以及強調的 [移除數據行] 選項。

什麼是 Power Query 中的圖表檢視

現在，您可以切換回資料預覽視圖，並啟用圖表視圖，以更可視化的方式查看您的資料和查詢。

截圖強調圖表檢視和資料預覽檢視圖示。

圖表檢視可協助您可視化查詢的結構，以及它如何與專案中的其他查詢互動。 查詢中的每個步驟都有不同的圖示，可協助您辨識所使用的轉換。 線條連接步驟以說明相依關係。 由於資料預覽檢視與圖表檢視皆已啟用，圖表檢視會顯示在資料預覽的上方。

Power Query工作區截圖，顯示圖表檢視與資料預覽。

注意

若要深入瞭解圖表檢視，請移至 圖表檢視。

在圖表中依國家/地區分組客戶

若要計算每個國家/地區的客戶數，請新增 Group by 轉換。 啟用圖表檢視時，選取加號。 您可以搜尋要新增至查詢的新轉換。 依 搜尋 群組，然後選取轉換。

在圖表視圖中搜尋群組的截圖。

群組的對話框將依 出現。 你可以把 「按營運群組 」設成依國家/地區分組，並計算每個國家/地區的客戶列數。

  1. 保持選取 [基本] 單選按鈕。
  2. 選擇國家 /地區 來分組。
  3. 輸入 Customers 作為新的欄位名稱，然後選取 [計數列] 作為操作。

基本群組對話框的截圖，輸入了客戶資料。

選取 [確定] 以執行作業。 你的資料預覽會刷新，顯示各國/地區的總客戶數量。

另一種啟動 「按群組 」對話框的方法是使用功能區中的 「按群組 」按鈕，或選擇並長按（右鍵點擊）國家 /地區 欄位。

截圖強調色帶中的「按群組」按鈕，以及國家/地區欄位選單中的「按群組」選項。

為了方便起見，Power Query 中的轉換通常可以從多個地方存取，使用者可以選擇使用自己偏好的體驗。

新增查詢

現在你有一個查詢能提供各國/地區客戶數量，可以透過找出每個國家/地區的供應商總數來為這些資料增添脈絡。

首先，您必須新增 供應商 數據。 選取取得資料。 在 [選擇資料來源中，搜尋 OData。 然後選取 [OData 連接器]。

「選擇資料來源」頁面的截圖，強調了 OData 連接器。

OData 連線體驗會重新出現。 輸入連線設定，即如連線到 OData 服務所述，連接到 Northwind OData 服務。 在 導覽器 體驗中，搜尋並選取 供貨商 數據表。

Power Query導航器中選取了 Northwind OData 供應商的截圖。

選擇 Create 以將新查詢加入 Power Query 編輯器。 展開 查詢 面板，現在會同時顯示 客戶 和 供應商 查詢。

強調客戶和供應商查詢之 [查詢] 窗格的螢幕快照。

再次開啟 [依 群組] 對話框，這次在 [轉換] 索引標籤下的功能區上，選取 [依 群組] 按鈕。

Transform 標籤功能區部分截圖，強調為「Group by」。

在「 按群組 」對話框中，將 「按營運群組 」設為依國家/地區分組，並計算每個國家/地區的供應商列數。

  1. 保持選取 [基本] 單選按鈕。
  2. 選擇國家 /地區 來分組。
  3. 在新增欄位名稱 中輸入 供應商，然後在 作業中選擇 [計算列數]。

基本群組對話框的截圖，已輸入供應商資料。

注意

若要透過 轉換深入瞭解 群組，請移至 群組或摘要資料列。

參考查詢

當你參照查詢時，你會建立一個新的查詢，該查詢會指向現有查詢的輸出。 現在你已經有了客戶查詢和供應商查詢，下一步目標是將它們合併為一。 你可以使用多種方法來達成此目標，包括客戶資料表中的合併選項、重複查詢或引用查詢。 在這個例子中，請選擇並長按（右鍵點擊） 客戶資料表 ，再選擇 參考資料來建立參考。 此動作會建立一個新的查詢，參考 Customers 查詢。

[客戶] 功能表的螢幕快照，其中強調 [參考] 選項。

建立此新查詢後，將查詢名稱改為「國家/地區分析」，並透過清除供應商查詢中的啟用載入選項，停用客戶資料表的載入。

第三個查詢名稱的螢幕快照已變更為 [國家/地區分析] 和 [供貨商啟用載入] 選項未標示並強調。

合併查詢

合併查詢 作業會根據一或多個數據行中的相符值，將兩個現有數據表聯結在一起。 在此範例中，目標是將 客戶 和 供貨商 資料表聯結成一個資料表，僅限於同時擁有 客戶 和 供貨商的國家/地區。

在國家/地區分析查詢中，從功能區的「首頁」選選「合併查詢」選項。

Power Query 工作區的螢幕擷取畫面，已選取 [國家/地區分析] 查詢，並強調 [常用] 索引標籤中的 [合併查詢] 選項。

合併作業的新對話框隨即出現。 然後，您可以選取要與目前查詢合併的查詢。 在 合併的右側表格中，選擇 供應商 查詢，然後從兩個查詢中選擇國家 /地區 欄位。 最後，選取 內連接 的類型，因為您只想要 客戶 和 供應商 所在的國家/地區進行分析。

合併查詢對話框的截圖，兩項查詢都選擇了國家/地區欄位，並選取了內部連接類型。

選擇 確定 按鈕後，會在你的 國家/地區分析 查詢中新增一個欄位，包含 供應商 查詢的資料。 選取 供應商 欄位旁的圖示，系統會顯示一個選單，讓您選擇要展開哪些欄位。 只選取 [供貨商] 欄位，然後選取 [確定] 按鈕。

「供應商」欄的截圖，當時「供應商」選單已開啟且僅選取了「供應商」。

此 展開 作業的結果是只有12行的表格。 將 [Suppliers.1] 欄位重新命名為僅 [供應商]，方法是按兩下欄位名稱並輸入新名稱。

已更名的供應商欄位截圖。

注意

若要深入瞭解 合併查詢 功能，請移至 合併查詢概觀。

套用的步驟

每個套用到查詢的轉換都會被儲存為查詢設定窗格中 [套用步驟] 區段的一個步驟。 如果您需要檢查查詢從步驟到步驟的轉換方式，您可以選取步驟並預覽查詢在該特定點的解析方式。

您也可以選取並按住查詢（以滑鼠右鍵按下）查詢，然後選取 [屬性] 選項，以變更查詢的名稱或新增查詢的描述。 例如，從國家/地區分析查詢中，選取並按住（按一下滑鼠右鍵）合併查詢步驟，然後將查詢名稱變更為「與供應商合併」，並將描述變更為「從 Suppliers 查詢取得 Suppliers by Country/region 的資料」。

步驟屬性對話框的截圖，已填寫更改的名稱和描述。

這項變更之後，您可以將滑鼠停留在圖表檢視中的合併查詢圖示上，或將滑鼠停留在 [套用的步驟] 窗格中步驟旁的新圖示上，以讀取其描述。

截圖顯示滑鼠移到哪裡可以查看步驟屬性。

注意

若要深入瞭解 套用的步驟，請移至 使用套用的步驟清單。

新增欄位前，先停用 圖表檢視 ，只使用 資料預覽。

新增資料欄

你可以新增一個由現有欄位計算出來的新欄位。 將客戶與供應商的資料集中於單一表格，您現在可以計算每個國家/地區的客戶與供應商比率。 選擇國家 /地區分析 查詢的最後一步，然後同時選擇 「客戶 」和 「供應商 」欄位。 在功能區的 新增資料行 索引標籤中，於 從數字 群組內選取 標準，然後從下拉式清單中選取 整數除法。

Power Query工作區截圖，新增欄位標籤並選擇標準，並強調除法（整數）選項。

這項變更會建立名為 Integer-division 的新數據行，您可以重新命名為 Ratio。 這項變更是您查詢的最後一個步驟，併為數據具有客戶和供應商的國家/地區提供客戶與供應商的比率。

數據分析

資料剖析是 Power Query 的一項功能，幫助你更好地理解你的資料。 藉由啟用數據分析功能，您可以取得查詢欄位內數據的意見反應，例如值分佈、數據行品質等等。

在查詢流程中多加使用此功能。 你可以隨時啟用或關閉此功能。 下圖顯示所有啟用於 國家/區域分析 查詢的資料分析工具。

資料剖析資訊的截圖，底部是 Ratio 欄位的詳細資料。

注意

若要深入了解 資料探勘，請前往 使用資料探勘工具。

進階編輯器

如果你想觀察 Power Query 編輯器在每個步驟中所建立的程式碼，或是想自己創建造型程式碼，可以使用進階編輯器。 要開啟advanced editor，請選擇色帶上的 View 標籤，然後選擇 進階編輯器。 隨即出現視窗，其中顯示現有的查詢程序代碼。

進階編輯器的螢幕快照，其中包含沒有語法錯誤的程序代碼。

你可以直接在 進階編輯器 視窗編輯程式碼。 編輯器會指出您的程式代碼是否沒有語法錯誤。 若要關閉視窗，請選取 完成 或 取消 按鈕。

存取 Power Query 說明

在使用 Power Query 的 Microsoft 應用程式中，可以獲得不同層級的協助服務。 本節說明這些協助層級。

Excel 中的 Power Query 幫助

有幾種方式可以取得專門適用於Excel Power Query的Power Query幫助資訊。 存取線上Excel Power Query文件的一種方式是在Power Query編輯器中選擇 File>Help>Help。

要在Excel中取得內嵌Power Query說明資訊，請在Excel色帶上選擇Help分頁，然後在搜尋框輸入Power Query。

Power BI Desktop 中的 Power Query 幫助

Power BI Desktop 上執行的 Power Query 編輯器中有一個 Help 分頁，但目前所有選項都指向提供 Power BI 資訊的位置。 你也可以在 Power BI Desktop 的 Power Query 編輯器中，選擇 File>，然後選擇其中一個說明選項，來找到相同的 Power BI Help 資訊。

Power BI 服務、Power Apps、Dynamics 365 Customer Insights 和 Azure Data Factory 中的 Power Query 說明

Power BI 服務、Power Apps、Dynamics 365 Customer Insights 和 Azure Data Factory 中的 Power Query Help 分頁包含了關於 Power Query 的重要資訊來源連結。

說明標籤的截圖，裡面有Power Query文件，裡面顯示所有幫助、社群和回饋選項。

說明標籤中的選項包括下列連結：

  • Power Query 文件：連結至Power Query文件
  • 鍵盤快捷鍵：連結至Power Query文件中的鍵盤快捷鍵文章。
  • Support：連結至Power Query目前運行的Microsoft產品的支援網站。
  • Power Query 網站：連結至Power Query網站。
  • Power Query部落格：Power Query部落格連結
  • Power Query 社群：連結至Power Query社群。
  • 提交點子：連結到Power Query目前運行的Microsoft產品點子網站。
  • Send feedback：在Power Query開啟一個視窗。 視窗會要求你評分使用 Power Query 的體驗，並提供任何你想提供的額外資訊。

注意

目前，Azure Analysis Services 沒有包含任何內嵌的 Power Query 幫助連結。 不過，你可以獲得 Power Query M 函式的協助。 下一節包含詳細資訊。

Power Query M 函式的說明

除了前面提到的 Power Query 說明功能外，你也可以在 Power Query 編輯器中獲得個別 M 函式的協助。 步驟會依你使用的版本略有不同：Power Query Desktop（整合進 Power BI Desktop 等應用程式）或 Power Query Online（整合進 Power BI 服務 和 Dataflows 等服務）。 以下步驟說明如何取得此內嵌說明。

  1. 開啟Power Query編輯器後，選擇插入步驟（）按鈕。
  2. 在公式列中，輸入您想要檢查的函式名稱。
    1. 如果你用的是 Power Query Desktop，輸入等號、空格和函式名稱。
    2. 如果你正在使用 Power Query Online，請輸入函式名稱。
  3. 選取函式的屬性。
    1. 如果你用的是 Power Query Desktop，在 Query Settings 選區，Properties，選擇 All properties。
    2. 如果你使用 Power Query Online，在 Query Settings 面板中，選擇 Properties。

這些步驟會開啟所選函式的內嵌說明資訊，並讓您輸入函式所使用的個別屬性。

Date.AddDays M 函式描述和語法的螢幕快照，以及每個函式參數的進入點。

總結

在本文中，你使用 Power Query 建立了一系列查詢，為 Northwind 公司在國家/區域層級提供客戶與供應商比率分析。

你學會了 Power Query 使用者介面的組成部分，如何在查詢編輯器中建立新查詢，參考查詢，合併查詢，理解已套用步驟的部分，以及新增欄位。 您也已瞭解如何使用資料分析工具來進一步瞭解您的數據。

Power Query 是一個強大的工具，可以用來連接許多不同的資料來源，並將資料轉換成你想要的形狀。 本文所列情境是示範，展示如何利用 Power Query 將原始數據轉化為重要且可行的商業洞察。

  • Last updated on