合併多個資料來源的資料 (Power Query)

附註: Power Query 在 Excel 2016 中稱為「取得及轉換」。 此處所提供的資訊適用於兩者。 若要深入了解,請參閱 Excel 2016 中的取得及轉換 (機器翻譯)。

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

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

本教學課程內容

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

步驟 1:連線至 Excel 活頁簿

步驟 2:將第一列升階為表格欄標題

步驟 3:移除其他欄,僅顯示感興趣的欄

Power Query 步驟已建立

步驟 4:匯入產品查詢

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

步驟 1:連線到 OData 摘要

步驟 2:展開 [訂單_詳細資料] 表格

展開 [訂單_詳細資料] 表格連結

步驟 3:移除其他欄,僅顯示感興趣的欄

移除選取欄

步驟 4:計算每個 [訂單_詳細資料] 列的行總計

計算每個 [訂單_詳細資料] 列的行總計

步驟 5:轉換 [訂單日期] 年份欄

步驟 6:按照產品識別碼和年份群組列

步驟 7:重新命名查詢

最終查詢結果

Power Query 步驟已建立

步驟 8:停用查詢下載至 Excel 活頁簿

停用查詢下載

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

步驟 1:將 [產品識別碼] 合併至 [總銷售額] 查詢

步驟 2:展開合併欄

展開 [新增欄] 表格連結

Power Query 步驟已建立

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

將個別 [產品] 的 [總銷售額] 查詢載入 Excel 資料模型

個別 [產品] 的 [最終總銷售額] 查詢

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

在此工作中, 您將產品從產品和訂單 .xlsx檔案匯入至 Excel 活頁簿。

步驟 1:連線至 Excel 活頁簿

  1. 建立 Excel 活頁簿。

  2. [POWER QUERY] 功能區索引標籤中,按一下 [From File] (從檔案) > [From Excel] (從 Excel)。

  3. [Excel] 瀏覽對話方塊中,瀏覽至或輸入產品與訂單.xlsx 路徑,以匯入或連結至檔案。

  4. 在 [導覽器] 窗格中,按兩下 [產品] 工作表或按一下 [產品],然後再按一下 [編輯查詢]。 當您編輯查詢,或是連線到新的資料來源時,就會出現 [查詢編輯器] 視窗。

    附註: 請參閱本文結尾,觀看如何顯示 [查詢編輯器] 的小影片。

步驟 2:將第一列升階為表格欄標題

[Query Preview] (查詢預覽) 方格中,表格的第一列並未包含表格欄名稱。 將第一列上移至表格欄標題:

  1. 按一下資料預覽左上角的表格圖示 ( 資料表圖示 )。

  2. 按一下 [Use First Row as Headers] (使用第一列做為標題)。

將第一列上移至表格欄標題

步驟 3:移除其他欄,僅顯示感興趣的欄

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

  1. [Query Preview] (查詢預覽) 方格中,選取 [ProductID] (產品識別碼)、[ProductName] (產品名稱)、[CategoryID] (類別識別碼) 及 [QuantityPerUnit] (每單位數量) 欄 (使用 CTRL + 滑鼠左鍵或 Shift + 滑鼠左鍵)。

  2. [Query Editor] (查詢編輯器) 功能區中,按一下 [Remove Columns] (移除資料行) > [Remove Other Columns] (移除其他資料行),或在欄標題上按一下滑鼠右鍵,然後按一下 [Remove Other Columns] (移除其他資料行)。

    隱藏其他欄

Power Query 步驟已建立

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

工作

查詢步驟

公式

連線至 Excel 活頁簿

Source

Source{[Name="Products"]}[Data]

將第一列上移至表格欄標題

FirstRowAsHeader

PromoteHeaders

(產品)

移除其他欄,僅顯示感興趣的資料欄

RemovedOtherColumns

SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

步驟 4:匯入產品查詢

在此步驟中,您匯入的 [Products] (產品) 查詢轉換成 Excel 活頁簿。

  1. [Query Editor] (查詢編輯器) 功能區中,按一下 [Apply & Close] (套用並關閉)。 結果會顯示在新的 Excel 工作表中。

頁首

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

在這個工作中,您可以從 Northwind OData 摘要範例 (網址:http://services.odata.org/Northwind/Northwind.svc) 將資料匯入 Excel 活頁簿。

步驟 1:連線到 OData 摘要

  1. [POWER QUERY] 功能區索引標籤中,按一下 [From Other Sources] (從其他來源) > [From OData Feed] (從 OData 摘要)。

  2. [OData Feed] (OData 摘要) 對話方塊中,輸入 Northwind OData 摘要的 [URL]

  3. 按一下 [確定]

  4. [Navigator] (導覽器) 窗格中,按兩下 [Orders] (訂單) 資料表或按一下 [Orders] (訂單),然後按一下 [Edit] (編輯)。

附註: 當您將游標移到資料表上,會看到資料表預覽飛出畫面。

將游標停在資料來源上

步驟 2:展開 [訂單_詳細資料] 表格

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

在 Power Query 中,包含相關表格連結的欄具有 [Entry] (項目) 連結或 [Table] (表格) 連結。 [專案] 連結會流覽到單一相關記錄, 並表示與主旨資料表之間的一對一關聯性表格連結會流覽至相關的資料表, 並代表與主旨資料表之間的一對多關聯。 連結代表關聯式模型內資料來源中的導覽屬性。 針對 OData 摘要, 導覽屬性代表具有外鍵關聯的實體。 在資料庫 (例如 SQL Server) 中, 流覽屬性代表資料庫中的外鍵關係。

展開訂單_詳細表格格連結

展開 [Order_Details] (訂單_詳細資料) 表格之後,三個新欄和其他資料列會新增至 [Orders] (訂單) 表格中,巢狀或相關表格每列各一。

  1. [Query Preview] (查詢預覽) 窗格中,捲動至 [Order_Details] (訂單_詳細資料) 欄。

  2. 在 [ Order_Details ] 欄中, 按一下展開圖示 ( 展開 )。

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

    1. 按一下 [(選取所有資料行)],以清除所有欄。

    2. 按一下 [產品識別碼]、[單價] 和 [數量]。

    3. 按一下 [確定]

      展開訂單_詳細表格格連結

      附註: 在 Power Query 中,您可以展開欄所連結的表格,同時也能夠在展開主旨表格中的資料之前,先對連結表格的欄執行彙總操作。 如需如何執行彙總作業的詳細資訊,請參閱彙總欄中的資料

步驟 3:移除其他欄,僅顯示感興趣的欄

在此步驟中,您可以移除 [OrderDate] (訂單日期)、[ProductID] (產品識別碼)、[UnitPrice] (單價) 及 [Quantity] (數量) 以外的所有欄。 在上一個任務,您使用了 [Remove Other Columns] (移除其他欄)。 以此任務為例,您可以移除選取的欄。

移除選取欄

  1. [Query Preview] (查詢預覽) 窗格中,選取所有欄:

    1. 按一下第一個資料欄 ([OrderID] (訂單識別碼))。

    2. Shift + 滑鼠左鍵按一下最後一欄 ([Shipper] (貨運公司))。

    3. Ctrl + 滑鼠左鍵按一下 [OrderDate] (訂單日期)、[Order_Details.ProductID] (訂單_詳細資料.產品識別碼)、[Order_Details.UnitPrice] (訂單_詳細資料.單價) 及 [Order_Details.Quantity] (訂單_詳細資料.數量) 欄。

  2. 以滑鼠右鍵按一下所選取欄上的標題,然後按一下 [Remove Columns] (移除欄)。

步驟 4:計算每個 [訂單_詳細資料] 列的行總計

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

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

  1. 在 [查詢預覽] 窗格中, 按一下預覽左上角的表格圖示 ( 資料表圖示 )。

  2. 按一下 [Insert Column] (插入欄) > [Custom] (自訂)。

  3. [Insert Custom Column] (插入自訂欄位) 對話方塊的 [Custom Column Formula] (自訂欄公式) 文字方塊中,輸入 [Order_Details.UnitPrice] (訂單_詳細資料.單價) * [Order_Details.Quantity] (訂單_詳細資料.數量)

  4. [New column name] (新增欄位名稱) 文字方塊中,輸入 [Line Total] (總銷售額)。

  5. 按一下 [OK] (確定)。

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

步驟 5:轉換 [訂單日期] 年份欄

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

  1. [Preview] (預覽) 窗格中,以滑鼠右鍵按一下 [OrderDate] (訂單日期) 欄,然後按一下 [Transform] (轉換) > [Year] (年份)。

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

    1. 按兩下 [OrderDate] (訂單日期) 欄,並輸入 [Year] (年份) 或

    2. 以滑鼠右鍵依序按一下 [OrderDate] (訂單日期) 欄、[Rename] (重新命名),然後輸入 [Year] (年份)。

步驟 6:按照 [產品識別碼] 和 [年份] 將列分成群組

  1. [Query Preview] (查詢預覽) 窗格中,選取 [Year] (年份) 及 [Order_Details.ProductID] (訂單_詳細資料.產品識別碼)。

  2. 以滑鼠右鍵按一下其中一個標題,然後按一下 [Group By] (群組依據)。

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

    1. [New column name] (新增欄位名稱) 文字方塊中,輸入 [Total Sales] (總銷售額)。

    2. [Operation] (操作) 的下拉式清單中,選取 [Sum] (總和)。

    3. [Column] (欄) 下拉式清單中,選取 [Line Total] (行總計)。

  4. 按一下 [確定]

    針對彙總運算依對話方塊群組

步驟 7:重新命名查詢

將銷售資料匯入至 Excel 之前,請先命名查詢為 [Total Sales] (總銷售額):

  1. [Query Settings] (查詢設定) 窗格內的 [Name] (名稱) 文字方塊中,輸入 [Total Sales] (總銷售額)。

最終查詢結果

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

總銷售額

Power Query 步驟已建立

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

工作

查詢步驟

公式

連接到 OData 摘要

Source

Source{[Name="Orders"]}[Data]

展開 [Order_Details] (訂單_詳細資料) 表格連結

展開 [Order_Details] (訂單_詳細資料)

ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

移除其他欄,僅顯示感興趣的資料欄

RemovedColumns

RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

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

InsertedColumns

AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

轉換 [OrderDate] (訂單日期) 欄以轉換年份

RenamedColumns

RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

按照 [ProductID] (產品識別碼) 和 [Year] (年份) 群組列

GroupedRows

Table. 群組
(RenamedColumns1, {"Year", "Order_Details"}, {{[總銷售額], 每個清單。 Sum ([行總計]), 輸入 number}})

步驟 8:停用查詢下載至 Excel 活頁簿

由於 [Total Sales] (總銷售額) 查詢並不代表的最終 [Total Sales per Product and Year] (個別產品及年份的總銷售額) 報表,因此請停用查詢下載至 Excel 活頁簿中。 當 [Load to Worksheet] (載入至工作表) 選項在 [Queries settings] (查詢設定) 窗格中為 [Off] (關閉) 時,不會下載這個查詢的資料結果,但查詢仍然可以與其他查詢合併以建立想要的結果。 解說如何將此查詢與下一個任務中的 [Products] (產品) 查詢合併。

停用查詢下載

  1. [Query Settings] (查詢設定) 窗格中,取消核取 [Load to worksheet] (載入至工作表)。

  2. [Query Editor] (查詢編輯器) 功能區中,按一下 [Apply & Close] (套用並關閉)。 在 [Workbook Queries] (活頁簿查詢) 窗格中,[Total Sales] (總銷售額) 查詢會顯示 [Load is disabled] (已停用載入)。

    停用查詢下載

頁首

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

Power Query 可讓您合併或新增多個查詢以進行合併。 不論資料來源為何,[合併] 操作都可以在任何有表格式圖形的 Power Query 查詢上執行。 如需結合資料來源的詳細資訊,請參閱合併多個查詢

在這項任務中,您使用 [Merge] (合併) 和 [Expand] (展開) 查詢步驟合併 [Products] (產品)[Total Sales] (總銷售額) 查詢。

步驟 1:將 [產品識別碼] 合併至 [總銷售額] 查詢

  1. 在 Excel 活頁簿中,瀏覽至 [Sheet2] (工作表 2) 的 [Products] (產品) 查詢。

  2. [QUERY] (查詢) 功能區索引標籤上按一下 [Merge] (合併)。

  3. [Merge] (合併) 對話方塊中,選取 [Products] (產品) 做為主要表格,然後選取 [Total Sales] (總銷售額) 做為第二個或相關查詢以合併。 [Total Sales] (總銷售額) 會變成新的可展開欄。

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

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

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

    2. 按一下 [Save] (儲存)。

  6. 按一下 [確定]

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

    [Merge] (合併) 對話方塊

在您按一下 [OK] (確定) 後,[Merge] (合併) 操作會建立查詢。 查詢結果中包含來自主要表格的所有欄 ([Products] (產品)),及一個包含相關表格之導覽連結的欄 ([Total Sales] (總銷售額))。 [Expand] (展開) 操作將新的欄從關聯表格新增至主要或主旨表格。

合併最終版

步驟 2:展開合併欄

在這個步驟中, 您會使用 [名稱NewColumn ] 展開合併欄, 在 [產品] 查詢中建立兩個新欄: [] 和 [總銷售額]。

展開 NewColumn (新增欄) 表格連結

  1. 在 [查詢預覽] 格線中, 按一下 [ NewColumn ] 展開圖示 ( 展開 )。

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

    1. 按一下 [(選取所有資料行)],以清除所有欄。

    2. 按一下 [年份] 和 [總銷售額]。

    3. 按一下 [確定]

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

  4. [Total Sales] (總銷售額) [Sort Descending] (遞減排序),瞭解有哪些產品及產品在哪些年份銷售額最高。

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

展開表格連結

Power Query 步驟已建立

當您在 Power Query 中執行 [Merge] (合併) 查詢活動時,查詢步驟隨即建立並列在 [Query Settings] (查詢設定) 窗格的 [APPLIED STEPS] (套用的步驟) 清單中。 每個查詢步驟都有對應的 Power Query 公式,也稱為「M」語言。 如需 Power Query 公式語言的詳細資訊,請參閱深入瞭解 Power Query 公式

工作

查詢步驟

公式

合併 [ProductID] (產品識別碼) 至 [Total Sales] (總銷售額) 查詢

來源 ([Merge] (合併) 操作的資料來源)

NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

展開合併欄

ExpandNewColumn

ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

排序

(RenamedColumns,{{"Total Sales", Order.Descending}})

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

在此步驟中,您停用 [Load to Worksheet] (載入到工作表) 選項並將查詢載入 [Excel Data Model] (Excel 資料模型),以建立連線至查詢結果的報表。 除了將查詢結果載入到 Excel 工作表外,Power Query 還可讓您將查詢結果載入 [Excel Data Model] (Excel 資料模型)。 將資料載入 [Excel Data Model] (Excel 資料模型) 後,您可以使用 Power Pivot 及 Power View 進行進一步的資料分析。

載入個別 [Product] (產品) 的 [Total Sales] (總銷售額查詢) 至 Excel 資料模型

  1. [Query Settings] (查詢設定) 窗格中,取消核取 [Load to worksheet] (載入至工作表),並檢查 [Load to data model] (載入至資料模型)。

  2. 若要將查詢載入至 [Excel Data Model] (Excel 資料模型),請按一下 [Apply & Close] (套用並關閉)。

載入 Excel 資料模型

個別 [產品] 的 [最終總銷售額] 查詢

執行各個步驟後,您會看到 [Total Sales per Product] (個別產品的總銷售額) 查詢,其中合併來自 [Products and Orders.xlsx] (產品與訂單.xlsx) 檔案和 Northwind OData 摘要的資料。 這個查詢可以套用至 Power Pivot 模型。 此外,變更 Power Query 中的查詢會修改並重新整理 Power Pivot 模型中產生的表格。

附註: [查詢編輯器] 只會在您使用 Power Query 載入、編輯或建立新的查詢時才會出現。 下列影片顯示編輯 Excel 活頁簿中的查詢之後,便會出現 [查詢編輯器] 視窗。 若要在不載入或編輯現有活頁簿查詢的情況下, 查看 [查詢編輯器], 請從 [ Power Query ] 功能區索引標籤的 [取得外部資料] 區段中, 選取 [從其他來源] > 下列影片展示如何顯示 [查詢編輯器] 的一個方法。

如何在 Excel 中看到 [查詢編輯器]

頁面頂端

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×