學習如何結合多個資料來源 (Power Query)

套用到
Microsoft 365 Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016

在這個教學中，你可以使用 Power Query 的查詢編輯器，從本地 Excel 檔案匯入包含產品資訊的資料，以及來自 OData 訂閱源（包含產品訂單資訊）的資料。 你執行轉換與彙整步驟，並將兩個來源的資料結合，產生一份「每產品與年度總銷售額」報告。   

要進行這個教學， 你需要產品工作 手冊。 在 [另存新檔] 對話方塊中，將檔案命名為產品與訂單.xlsx。

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

在這個任務中，你要將產品和 Orders.xlsx (下載並重新命名的檔案匯入 Excel 工作簿) ，將列升為欄頭，移除部分欄位，並將查詢載入工作表。

步驟一：連接 Excel 工作簿

  1. 建立 Excel 活頁簿。
  2. 選擇資料>，從工作簿的檔案>中取得資料>。
  3. 在 「匯入資料 」對話框中，瀏覽並找到你下載的 Products.xlsx 檔案，然後選擇 「開啟」。
  4. 在 導航 器面板中，雙擊 產品 表。 Power Query 編輯器 會出現。

步驟二：檢視查詢步驟

預設情況下，Power Query 會自動增加多個步驟，方便你使用。 請在查詢設定欄格中檢視「已應用步驟」中的每個步驟以了解更多。

  1. 右鍵點擊 「來源 」步驟，然後選擇 編輯設定。 這個步驟是在你匯入工作簿時產生的。
  2. 右鍵點擊 「導航 」步驟，然後選擇 「編輯設定」。 這個步驟是在你從 導航 對話框中選擇表格時產生的。
  3. 右鍵點擊 「變更型別 」步驟，然後選擇 「編輯設定」。 此步驟由 Power Query 建立，推斷每個欄位的資料型態。 選擇公式列右側的向下箭頭即可查看完整公式。

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

在此步驟中，您可以移除 [ProductID] (產品識別碼)、[ProductName] (產品名稱)、[CategoryID] (類別識別碼) 及 [QuantityPerUnit] (每單位數量) 以外的所有欄。

  1. 在 資料預覽中，選擇 ProductID、 ProductName、 CategoryID 和 QuantityPerUnit 欄位 (使用 Ctrl+Click 或 Shift+Click) 。
  2. 選擇 移除欄位>移除其他欄位。
    隱藏其他欄

步驟 4：載入產品查詢

在此步驟中，你會將 產品 查詢載入 Excel 工作表。

  • 選擇 「Home>Close & Load」。 查詢會出現在新的 Excel 工作表中。

摘要：任務 1 中建立的 Power Query 步驟

當你在 Power Query 執行查詢活動時，查詢步驟會被建立並列在 查詢設定 窗格的 已套用步驟 清單中。 每個查詢步驟都有對應的 Power Query 公式，也稱為「M」語言。 欲了解更多關於 Power Query 公式的資訊，請參閱 「在 Excel 中建立 Power Query 公式」。

工作 查詢步驟 公式
匯入 Excel 工作簿 來源 = Excel.Workbook (File.Contents (“C：\Products and Orders.xlsx”) ，null，true)
選擇產品表 導航 = 來源{[Item=“Products”，Kind=“Table”]}[Data]
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 摘要訂單資料

在此任務中，你需要從 http://services.odata.org/Northwind/Northwind.svc 的 Northwind OData 範例資料匯入 Excel 工作簿 ， 展開Order_Details表，移除欄位，計算行總數，轉換訂單日期，依 ProductID 和年份分組資料列，重新命名查詢，並停用查詢下載到 Excel 工作簿。

步驟 1：連接 OData 訊號

  1. 選擇資料> 從OData 動態中取得其他來源>的資料>。
  2. 在 [OData Feed] (OData 摘要) 對話方塊中，輸入 Northwind OData 摘要的 [URL]。
  3. 選取 [確定]。
  4. 在 導航 器面板中，雙 擊訂單表 。

步驟二：展開Order_Details表

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

在 Power Query 中，包含相關資料表的欄位儲存格值為 Record 或 Table 。 這些稱為結構化柱。 紀錄 表示單一相關紀錄，代表與目前資料或主要資料表一對一的關係。 Table 表示相關資料表，代表與目前或主要資料表的一對多關係。 結構化欄位代表具有關聯模型的資料來源中的關係。 例如，結構化欄位表示 OData 資料流中具有外鍵關聯的實體，或在 SQL Server 資料庫中擁有外鍵關聯。

展開 Order_Details 表格後， 訂單表會 新增三欄和額外列，分別對應巢狀或相關表格的每一列。

  1. 在 資料預覽中，橫向捲動到 Order_Details 欄。

  2. 在 Order_Details 欄中，選擇展開圖示 (展開 ) 。

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

    1. 選擇 (選取所有欄位) 以清除所有欄位。

    2. 選擇 ProductID、 UnitPrice 和 Quantity。

    3. 選取 [確定]。
      展開訂單_詳細表格格連結

      注意

      在 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. 右鍵點擊選取的欄位標頭，然後選擇 「移除其他欄位」。

步驟四：計算每列Order_Details行的行總和

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

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

計算每個 Order_Details (訂單_詳細資料) 列的行總計

步驟 5：轉換 OrderDate 年份欄位

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

  1. 在 資料預覽中，右鍵點擊 訂單日期 欄位，選擇 轉換>年份。

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

    1. 按兩下 [OrderDate] (訂單日期) 欄，並輸入 [Year] (年份) 或
    2. Right-Click 訂單 日期 欄位，選擇 「重新命名」，並輸入 年份。

步驟 6：依 ProductID 和年份分組列

  1. 在 資料預覽中，選擇 年份 與 Order_Details.ProductID。

  2. Right-Click 其中一個標頭，選擇「群組成」。

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

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

  4. 選取 [確定]。
    針對彙總運算依對話方塊群組

步驟 7：重新命名查詢

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

  • 在查詢設定欄目中，姓名框輸入「總銷售」。

結果：任務二的最終查詢

在執行每個步驟之後，在 Northwind OData 資料摘要上會有 [Total Sales] (總銷售額) 的查詢。

總銷售額

摘要：任務 2 中建立的 Power Query 步驟

當你在 Power Query 執行查詢活動時，查詢步驟會被建立並列在 查詢設定 窗格的 已套用步驟 清單中。 每個查詢步驟都有對應的 Power Query 公式，也稱為「M」語言。 欲了解更多關於 Power Query 公式的資訊，請參閱了解 Power Query 公式。

工作 查詢步驟 公式
連接到 OData 摘要 Source = OData.Feed (“http://services.odata.org/Northwind/Northwind.svc”，null，[實作=“2.0”])
選取表格 瀏覽 = 來源{[Name=「命令」]}[資料]
展開 [Order_Details] (訂單_詳細資料) 表格連結 展開 [Order_Details] (訂單_詳細資料) = Table.ExpandTableColumn (訂單，「Order_Details」、「{“ProductID”、“UnitPrice”、“Quantity}、{”Order_Details.ProductID“、”Order_Details.UnitPrice“、”Order_Details.Quantity})
移除其他欄，僅顯示感興趣的資料欄 RemovedColumns = Table.RemoveColumns (#「展開Order_Details」、「」、「CustomerID」、「EmployeeID」、「RequiredDate」、「ShippedDate」、「ShipVia」、「Freight」、「ShipName」、「ShipAddress」、「ShipCity」、「ShipRegion」、「ShipPostalCode」、「ShipCountry」、「Customer」、「Employee」、「Shipper」)
計算每個 Order_Details (訂單_詳細資料) 列的行總計 新增自訂功能 = Table.AddColumn (RemovedColumns，「Custom」，每個 [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”， 每個 List.Sum ([Line Total]) ， type number}})

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

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 會分析每個資料來源，並將它們歸類為定義的隱私權層級：公用、組織和私人。 欲了解更多隱私等級資訊，請參閱 設定隱私等級。

    [Merge] (合併) 對話方塊

結果

合併操作會建立一個查詢。 查詢結果包含主要資料表 (產品) 的所有欄位，以及一個結構化的表格欄位，指向相關資料表 (總銷售) 。 選擇 展開 圖示，從次要或相關資料表新增欄位到主資料表。

合併最終版

步驟 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. 選擇 「Home>Close & Load」。
  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 (產品、{“ProductID”}、#“總銷售額”、“{”Order_Details.ProductID“}、”總銷售額“、JoinKind.LeftOuter)
展開合併欄 擴展總銷售額 = Table.ExpandTableColumn (來源，「總銷售額」、「年度」、「總銷售額」}、{「總銷售.年度」、「總銷售.總銷售額」})
重新命名兩欄 更名柱 = Table.RenameColumns (#“擴展總銷售額”，{{“年度銷售總額”，“年度”}，{“總銷售.總銷售額”，“總銷售額”}})
按銷售總數依序排序 排序行 = Table.Sort (#“Renamed Columns”，{{“Total Sales”， Order.Ascending}})

另請參閱

適用於 Excel 的 Power Query 說明