了解如何結合多個資料來源 (Power Query)

Applies To
Excel for Microsoft 365 Excel 2024 Excel 2021

在本教學課程中，使用 Power Query 的查詢編輯器，從包含產品資訊的本機 Excel 檔案，以及包含產品訂單資訊的 OData 摘要中匯入資料。 執行轉換和彙總步驟，並結合這兩個來源的資料，以建立 [每個產品的總銷售額] 報表和 [年份]。   

若要完成本教學課程，您需要 [產品 ] 活頁簿。 在 [另存新檔] 對話方塊中，將檔案命名為產品與訂單.xlsx。

任務 1：將產品匯入至 Excel 活頁簿

在這項工作中，您將產品從在上一節中下載並重新命名 () Excel 活頁簿中的產品和 Orders.xlsx 檔案匯入 Excel 活頁簿。 接著，您可以將列升級為欄標題、移除某些欄，並將查詢載入至工作表。

步驟 1：連線到 Excel 活頁簿

  1. 建立 Excel 活頁簿。
  2. 選取 [資料>]，從活頁簿的檔案>取得資料>。
  3. 在 [匯入資料 ] 對話方塊中，瀏覽並找到您下載的 Products.xlsx 檔案，然後選取 [ 開啟]。
  4. 在 [ 導覽器 ] 窗格中，按兩下 [產品 ] 資料表。 隨即會顯示 Power Query 編輯器。

步驟 2：檢查查詢步驟

根據預設，為方便起見，Power Query 會自動新增數個步驟。 檢查 [查詢設定] 窗格中 [套用的步驟] 下的每個步驟以深入了解。

  1. 以滑鼠右鍵按一下 [ 來源 ] 步驟，然後選取 [ 編輯設定]。 此步驟是在您匯入活頁簿時建立的。
  2. 以滑鼠右鍵按一下 [瀏覽 ] 步驟，然後選取 [ 編輯設定]。 當您從 [導覽 ] 對話方塊中選取資料表時，就會建立此步驟。
  3. 以滑鼠右鍵按一下 [變更的類型 ] 步驟，然後選取 [ 編輯設定]。 此步驟是由 Power Query 建立，可推斷每個資料行的資料類型。 選取資料編輯列右側的向下箭頭以查看完整的公式。

步驟 3：移除其他資料欄位以只顯示感興趣的資料欄位

在此步驟中，您會移除 [ProductID]、[ ProductName]、[ CategoryID] 和 [QuantityPerUnit] 以外的所有資料欄位。

  1. 在 [資料預覽] 中，使用 Ctrl+Click 或 Shift+Click () 選取 ProductID、 ProductName、 CategoryID 和 QuantityPerUnit 欄。
  2. 選取 [移除資料欄位]、[>移除其他資料欄位]。
    顯示 [隱藏其他資料行] 的螢幕擷取畫面。

步驟 4：載入產品查詢

在此步驟中，您將 [產品] 查詢載入 Excel 工作表。

  • 選取 [首頁>] 關閉 & 載入。 查詢會出現在新的 Excel 工作表中。

摘要：在工作 1 中建立的 Power Query 步驟

當您在 Power Query 中執行查詢活動時，它會建立查詢步驟，並將它們列在 [查詢設定] 窗格的 [套用的步驟] 清單中。 每個查詢步驟都有對應的 Power Query 公式，也稱為「M」語言。 如需 Power Query 公式的詳細資訊，請參閱 Power Query 文件。

工作 查詢步驟 公式
匯入 Excel 活頁簿 來源 = Excel.Workbook (File.Contents (“C：\Products and Orders.xlsx”) ， null， true)
選取 [產品] 資料表 瀏覽 = 來源{[item=“products”，kind=“表格”]}[資料]
Power Query 會自動偵測資料行的資料類型 變更的類型 = Table.TransformColumnTypes ( Products_Table，{{“ProductID”， Int64.Type}， {“ProductName”， type text}， {“SupplierID”， Int64.Type}， {“CategoryID”， Int64.Type}， {“QuantityPerUnit”， type text}， {“UnitPrice”， type number}， {“UnitsInStock”， Int64.Type}， {“UnitsOnOrder”， Int64.Type}， {“ReorderLevel”， Int64.Type}， {“Discontinued”， type logical}})
移除其他欄，僅顯示感興趣的資料欄 已移除其他資料欄位 = Table.SelectColumns (FirstRowAsHeader，{“ProductID”， “ProductName”， “CategoryID”， “QuantityPerUnit”})

任務 2：匯入 OData 摘要訂單資料

在此工作中，您會從位於 的範例 Northwind OData 摘要 http://services.odata.org/Northwind/Northwind.svc將資料匯入 Excel 活頁簿，展開 Order_Details 資料表、移除欄、計算行加計、轉換 OrderDate、依 ProductID 和 Year 將列分組、重新命名查詢，以及停用查詢下載至 Excel 活頁簿。

步驟 1：連線到 OData 摘要

  1. 從OData 摘要從其他來源>選取>資料>。
  2. 在 [OData Feed] (OData 摘要) 對話方塊中，輸入 Northwind OData 摘要的 [URL]。
  3. 選取 [確定]。
  4. 在 [ 導覽] 窗格中，按兩下 [ 訂單] 資料表。

步驟 2：展開Order_Details資料表

在此步驟中，您展開與 [Orders] (訂單) 表格相關的 [Order_Details] (訂單_詳細資料) 表格，以從 [Order_Details] (訂單_詳細資料) 合併 [ProductID] (產品識別碼)、[UnitPrice] (單價) 及 [Quantity] (數量) 欄位至 [Orders] (訂單) 表格。 [Expand] (展開) 操作會將欄從相關表格合併至主題表格。 當查詢執行時，來自相關資料表 (Order_Details) 的資料列會合併成含有主要資料表 [ 訂單) ] (的資料列。

在 Power Query 中，包含相關資料表的資料行的儲存格值為 [記錄] 或 [表格]。 這些稱為結構化欄。 記錄 表示單一相關記錄，代表與目前資料或主要資料表的一對一關聯性。 Table 表示相關資料表，並代表與目前或主要資料表的一對多關聯性。 結構化資料行代表資料來源中具有關聯式模型的關聯。 例如，結構化資料行表示在 OData 摘要中具有外部索引鍵關聯的實體，或在 SQL Server 資料庫中具有外部索引鍵關聯。

展開 [Order_Details 資料表之後，[ 訂單 ] 資料表會新增三個新資料行和其他資料列，巢狀或相關資料表中的每一資料列各一個。

  1. 在 [資料預覽] 中，水平捲動至 Order_Details 欄。

  2. 在 [Order_Details ] 欄中，選取展開圖示 ( ) 。

  3. 在 [展開] 下拉式清單中：

    1. 選取 ([選取所有資料欄位]) 以清除所有資料欄位。

    2. 選取 [ProductID]、[ UnitPrice] 和 [ Quantity]。

    3. 選取 [確定]。
      顯示 [展開Order_Details表格] 連結的螢幕擷取畫面。

      注意

      在 Power Query 中，您可以展開從資料行連結的資料表，並彙總連結資料表的資料行，再展開主旨資料表中的資料。 如需如何執行彙總作業的詳細資訊，請參閱從欄 (Power Query) 彙總資料 。

步驟 3：移除其他資料欄位以只顯示感興趣的資料欄位

在此步驟中，您會移除 [OrderDate]、[ ProductID]、[ UnitPrice] 和 [Quantity ] 資料行以外的所有資料欄位。 

  1. 在 [資料預覽] 中，選取以下列欄位：

    1. 選取第一欄 OrderID。
    2. Shift+按一下最後一欄， 託運人。
    3. Ctrl + 滑鼠左鍵按一下 [OrderDate] (訂單日期)、[Order_Details.ProductID] (訂單_詳細資料.產品識別碼)、[Order_Details.UnitPrice] (訂單_詳細資料.單價) 及 [Order_Details.Quantity] (訂單_詳細資料.數量) 欄。

  2. 以滑鼠右鍵按一下選取的資料行標題，然後選取 [移除其他資料行]。

步驟 4：計算每個Order_Details列的行總計

在此步驟中，您建立 [Custom Column] (自訂的欄) 來計算每個 [Order_Details] (訂單_詳細資料) 列的行總計。

  1. 在 [資料預覽] 中，選取預覽左上角 () 的資料 表圖示。
  2. 選取 [新增自訂資料欄位]。
  3. 在 [自訂欄位] 對話方塊的 [自訂欄位公式] 方塊中，輸入 [Order_Details.UnitPrice] * [Order_Details.Quantity]。
  4. 在 [新增欄名稱 ] 方塊中，輸入 「行總計」。
  5. 選取 [確定]。

顯示 [計算每個Order_Details列的行總計] 的螢幕擷取畫面。

步驟 5：轉換 OrderDate 年份資料行

在此步驟中，您轉換 [OrderDate] (訂單日期) 欄以轉換訂購日期年份。

  1. 在 [資料預覽] 中，以滑鼠右鍵按一下 [OrderDate ] 欄，然後選取 [轉換>年份]。

  2. 重新命名 [OrderDate] (訂單日期) 欄為 [Year] (年份)：

    1. 按兩下 [OrderDate ] 欄，然後輸入 [年份] 或
    2. 以滑鼠右鍵按一下 [OrderDate ] 欄，選取 [重新命名]，然後輸入 [年份]。

步驟 6：依 ProductID 和年份將資料列組成群組

  1. 在 [資料預覽] 中，選取 [年份] 和 [Order_Details.ProductID]。

  2. 以滑鼠右鍵按一下其中一個標題，然後選取 [ 分組依據]。

  3. 在 [Group By] (群組依據) 對話方塊中：

    1. 在 [New column name] (新增欄位名稱) 文字方塊中，輸入 [Total Sales] (總銷售額)。
    2. 在 [Operation] (操作) 的下拉式清單中，選取 [Sum] (總和)。
    3. 在 [Column] (欄) 下拉式清單中，選取 [Line Total] (行總計)。

  4. 選取 [確定]。
    螢幕擷取畫面，顯示彙總作業的 [分組依據] 對話方塊。

步驟 7：重新命名查詢

將銷售資料匯入 Excel 之前，請重新命名查詢：

  • 在 [查詢設定] 窗格中的 [ 名稱 ] 方塊中，輸入 [總銷售額]。

結果：工作 2 的最終查詢

執行每個步驟之後，您會在 Northwind OData 摘要上產生 [總銷售額] 查詢。

顯示總銷售額的螢幕擷取畫面。

摘要：在工作 2 中建立的 Power Query 步驟

當您在 Power Query 中執行查詢活動時，它會建立查詢步驟，並將它們列在 [查詢設定] 窗格的 [套用的步驟] 清單中。 每個查詢步驟都有對應的 Power Query 公式，也稱為「M」語言。 如需 Power Query 公式的詳細資訊，請參閱 Power Query 文件。

工作 查詢步驟 公式
連接到 OData 摘要 Source = OData.Feed (“http://services.odata.org/Northwind/Northwind.svc”， null， [Implementation=“2.0”])
選取表格 瀏覽 = 來源{[Name=“訂單”]}[資料]
展開 [Order_Details] (訂單_詳細資料) 表格連結 展開 [Order_Details] (訂單_詳細資料) = Table.ExpandTableColumn (Orders， “Order_Details”， {“ProductID”， “UnitPrice”， “Quantity”}， {“Order_Details.ProductID”， “Order_Details.UnitPrice”， “Order_Details.Quantity”})
移除其他欄，僅顯示感興趣的資料欄 RemovedColumns = Table.RemoveColumns (#“Expand Order_Details”，{“OrderID”， “CustomerID”， “EmployeeID”， “RequiredDate”， “ShippedDate”， “ShipVia”， “Freight”， “ShipName”， “ShipAddress”， “ShipCity”， “ShipRegion”， “ShipPostalCode”， “ShipCountry”， “Customer”， “Employee”， “Shipper”})
計算每個 Order_Details (訂單_詳細資料) 列的行總計 已新增自訂 = Table.AddColumn (RemovedColumns， “Custom”， each [Order_Details.UnitPrice] * [Order_Details.Quantity])
= Table.AddColumn (#“展開Order_Details”， “行總計”， 每個 [Order_Details.UnitPrice] * [Order_Details.Quantity])
變更為更有意義的名稱 Lne Total 重新命名的欄位 = Table.RenameColumns (InsertedCustom，{{“Custom”， “Line Total”}})
轉換 [OrderDate] (訂單日期) 欄以轉換年份 擷取年份 = Table.TransformColumns (#“Grouped rows”，{{“Year”， Date.Year， Int64.Type}})
變更為
更有意義的名稱、OrderDate 和 Year		 重新命名的欄 1 Table.RenameColumns
(TransformedColumn,{{"OrderDate", "Year"}})
按照 [ProductID] (產品識別碼) 和 [Year] (年份) 群組列 GroupedRows = Table.Group (RenamedColumns1， {“Year”， “Order_Details.ProductID”}， {{“Total Sales”， each List.Sum ([Line Total]) ， type number}})

任務 3：合併 [產品] 和 [總銷售額] 的查詢

Power Query 可讓您合併或附加多個查詢。 不論資料來源為何，您都可以對任何具有表格式圖形的 Power Query 查詢執行合併作業。 如需合併資料來源的詳細資訊，請參閱合併多個查詢 (Power Query) 。

在此工作中，您可以使用合併查詢和展開作業來合併 [產品] 和 [總銷售額] 查詢，然後將 [每個產品的總銷售額] 查詢載入 Excel 資料模型。

步驟 1：將 ProductID 合併至總銷售額查詢

  1. 在 Excel 活頁簿中，移至 [產品] 工作表索引標籤上的 [產品] 查詢。

  2. 選取查詢中的儲存格，然後選取 [查詢>合併]。

  3. 在 [合併] 對話方塊中，選取 [產品 ] 做為主要資料表，然後選取 [總銷售額] 做為要合併的次要或相關查詢。 [總銷售額] 會變成一個帶有展開圖示的新結構化欄。

  4. 若要依 [ProductID] (產品識別碼) 將 [Total Sales] (總銷售額) 對應至 [Products] (產品)，請從 [Products] (產品) 表格選取 [ProductID] (產品識別碼) 欄，並從 [Total Sales] (總銷售額) 表格選取 [Order_Details.ProductID] (訂單_詳細資料.產品識別碼) 欄。

  5. 在 [Privacy Levels] (隱私權層級) 對話方塊中：

    1. 針對兩個資料來源的隱私權隔離層級選取 [Organizational] (組織)。
    2. 選取 [儲存]。

  6. 選取 [確定]。

    注意

    [Privacy Levels] (隱私權層級) 可防止使用者不小心合併多個資料來源中的資料，而這些資料來源可能是私人或組織。 視查詢而定，使用者可能不小心將資料從私人資料來源傳送至另一個惡意的資料來源。 Power Query 會分析每個資料來源，並將它們歸類為定義的隱私權層級：公用、組織和私人。 如需隱私權層級的詳細資訊，請參閱設定隱私權層級 (Power Query) 。

    顯示 [合併] 對話方塊的螢幕擷取畫面。

結果

合併作業會建立查詢。 查詢結果包含主要資料表 (Products) 的所有資料行，以及相關資料表 [總銷售額) ] 的單一資料表結構化資料行 (。 選取 [展開] 圖示，從次要資料表或相關資料表將新資料行新增至主要資料表。

顯示 [合併最終] 的螢幕擷取畫面。

步驟 2：展開合併的資料行

在此步驟中，展開名稱為 NewColumn 的合併資料行，以在 [產品 ] 查詢中建立兩個新資料行： [年份] 和 [總銷售額]。

  1. 在 [資料預覽] 中，選取 [ 展開 ] 圖示 ([) ] 旁邊的 [新增欄]。

  2. 在 [展開] 下拉式清單中：

    1. 選取 ([選取所有資料欄位]) 以清除所有資料欄位。
    2. 選取 [年份 ] 和 [銷售總額]。
    3. 選取 [確定]。

  3. 將這兩欄重新命名為 [Year] (年) 和 [Total Sales] (總銷售額)。

  4. 若要找出產品在哪些年份的銷售量最高，請選取 [依總銷售額遞減排序]。

  5. 將查詢 [Rename] (重新命名) 為 [Total Sales per Product] (個別產品的總銷售額)。

結果

顯示 [展開表格] 連結的螢幕擷取畫面。

步驟 3：將 [每個產品的總銷售額] 查詢載入 Excel 資料模型

在此步驟中，您會將查詢載入 Excel 資料模型，以便建立連接到查詢結果的報表。 將資料載入 Excel 資料模型之後，您可以使用 Power Pivot 來進一步進行資料分析。

  1. 選取 [首頁>] 關閉 & 載入。
  2. 在 [匯入資料 ] 對話方塊中，請務必選取 [ 將此資料新增至資料模型]。 如需使用此對話方塊的相關資訊，請選取問號 (?)。

結果

您有一個 [每個產品的總銷售額 ] 查詢，其中結合了來自 Products.xlsx 檔案和 Northwind OData 摘要的資料。 此查詢會套用至 Power Pivot 模型。 此外，查詢的變更會修改並重新整理 [資料模型] 中產生的資料表。

摘要：在工作 3 中建立的 Power Query 步驟

當您在 Power Query 中執行合併查詢活動時，會建立查詢步驟，並列在 [查詢設定] 窗格的 [套用的步驟] 清單中。 每個查詢步驟都有對應的 Power Query 公式，也稱為「M」語言。 如需 Power Query 公式的詳細資訊，請參閱 Power Query 文件。

工作 查詢步驟 公式
合併 [ProductID] (產品識別碼) 至 [Total Sales] (總銷售額) 查詢 來源 ([Merge] (合併) 操作的資料來源) = Table.NestedJoin (Products， {“ProductID”}， #“Total Sales”， {“Order_Details.ProductID”}， “Total Sales”， JoinKind.LeftOuter)
展開合併欄 拓展總銷售額 = Table.ExpandTableColumn (Source， “Total Sales”， {“Year”， “Total Sales”}， {“Total Sales.Year”， “Total Sales.Total Sales”})
重新命名兩欄 重新命名的欄位 = Table.RenameColumns (#“Expanded Total Sales”，{{“Total Sales.Year”， “Year”}， {“Total Sales.Total Sales”， “Total Sales”}})
以遞增排序總銷售額 排序列 = Table.Sort (#“Renamed columns”，{{“Total Sales”， Order.Ascending}})

另請參閱

適用於 Excel 的 Power Query 說明