瞭解如何在 Power Query (合併多個)

在此教學課程中,您可以使用 Power Query 的查詢編輯器,從包含產品資訊的Excel檔案,以及包含產品訂單資訊的 OData 資料摘要中,匯出資料。 您可以執行轉換和匯總步驟,併合並這兩個來源的資料,以產生「每個產品與年份的總銷售額」報表。   

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

在此工作中,您將從) 檔案上方下載並重新命名的產品和 Orders.xlsx (產品,導入至 Excel 活頁簿、將列升級為欄標題、移除部分欄,以及將查詢載入至工作表。

步驟 1:連線至 Excel 活頁簿

  1. 建立 Excel 活頁簿。

  2. 選取資料>從>取得資料>從活頁簿取得資料

  3. 在 [ 輸入資料Products.xlsx中,流覽並找到您下載Products.xlsx檔案,然後選取 [ 開啟

  4. 在 [ 導航器> 窗格中,按兩下 [ 產品資料 表。 Power Query 編輯器會出現。

步驟 2:檢查查詢步驟

根據預設,Power Query 會自動新增數個步驟,方便您使用。 檢查查詢和查詢窗格中設定步驟,以深入瞭解。

  1. 以滑鼠右鍵按一下 [來源設定。 此步驟是在您輸入活頁簿時建立。

  2. 以滑鼠右鍵按一下流覽步驟,然後選取 [編輯設定。 此步驟是在您從 [導航模式> 對話方塊中選取 資料表時 建立。

  3. 以滑鼠右鍵按一下 [已變更的類型步驟,然後選取[編輯設定。 此步驟是由 Power Query 建立,可推斷出每個資料行的資料類型。 選取編輯欄右邊的向下箭箭,以查看完整的公式。

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

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

  1. [資料預覽] 中,選取[ProductID]、[產品名稱]、[CategoryID] 和[QuantityPerUnit]欄 (Ctrl+Click 或 Shift+click) 。

  2. 選取移除欄>移除其他欄

    隱藏其他欄

步驟 4:載入產品查詢

在此步驟中,您將產品查詢載入至Excel工作表。

  • 選取首頁>關閉&載入。 查詢會顯示在工作表Excel中。

摘要:在任務 1 中建立 Power Query 步驟

當您在 Power Query 中執行查詢活動時,查詢步驟會建立列在查詢設定窗格中的已應用步驟清單中。 每個查詢步驟都有對應的 Power Query 公式,也稱為「M」語言。 有關 Power Query 公式的資訊,請參閱在 Excel中建立 power Query 公式

工作

查詢步驟

公式

將新Excel頁簿

Source

= Excel。活頁簿 (檔案.contents ("C:\Products and Orders.xlsx") 、null、true)

選取產品資料表

導航

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query 會自動偵測欄資料類型

已變更類型

= Table.TransformColumnTypes (Products_Table,{{"ProductID",Int64.Type},{"ProductName",type},{"SupplierID", Int64.Type}、{"CategoryID"、Int64.Type}、{"QuantityPerUnit"、type}、{"UnitPrice"、type}、{"UnitsInStock"、Int64.Type}、{"UnitsOnOrder"、Int64.Type}、{"ReorderLevel"、Int64.Type}、{"已停用",輸入 logical}})

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

移除其他欄

= Table.selectColumns (FirstRowAsHeader,{"ProductID","ProductName","CategoryID","QuantityPerUnit"})

在此工作中,您將資料從 HTTP://services.odata.org/Northwind/Northwind.svc 的 Northwind OData 進紙範例中將資料導入至您的 Excel活頁簿、展開 Order_Details 資料表、移除欄、計算行總計、轉換 OrderDate、按 ProductID 和 Year 分組列、重新命名查詢,以及停用查詢下載至 Excel 活頁簿。

步驟 1:連線 OData Feed

  1. 選取資料>OData>取得其他>資料來源的資料

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

  3. 選取 [確定]。

  4. 在 [ 導航器> 窗格中,按兩下 [ 訂單資料 表。

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

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

在 Power Query 中,包含相關資料表的欄在儲存格中具有值 RecordTable。 這些稱為結構化欄。 記錄代表單一相關記錄,並代表與目前資料或主資料表的一對一 關聯。 資料表會指出相關的資料表,並代表與目前或主要資料表的一對多關聯。 結構化資料行代表資料來源中具有關系模型的關係。 例如,結構化資料行會指出 OData 資料摘要中具有外鍵關聯或資料庫外鍵關聯SQL Server實體。

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

  1. 資料預覽中,水準卷Order_Details欄。

  2. 在Order_Details 欄中 ,選取展開圖示 ( 展開 ) 。

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

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

    2. 選取ProductID、UnitPriceQuantity

    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. 以滑鼠右鍵按一下選取的欄標題,然後選取 [移除其他欄

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

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

  1. 資料預覽中,選取 ( 資料表圖示 ) 左上角的資料表圖示。

  2. 按一下 [新增自訂欄> 。

  3. [自訂欄 ] 對話方塊中的 [自訂欄 公式 ] 方塊中,輸入 [Order_Details.UnitPrice] * [Order_Details.Quantity]

  4. 在新增 欄名稱方塊 中,輸入 行合計

  5. 選取 [確定]。

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

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

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

  1. [資料預覽中,以滑鼠右鍵按一下OrderDate 資料行,然後選取[>年份

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

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

    2. Right-Click OrderDate 資料行上,選取重新 命名,然後輸入 Year

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

  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之前,請重新命名查詢:

  • 在查詢設定窗格中,在名稱方塊中輸入總銷售額

結果:任務 2 的最終查詢

在執行每個步驟之後,在 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,[Implementation="2.0"])

選取表格

瀏覽

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

展開 [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",{"OrderID", "CustomerID","EmployeeID","RequiredDate","ShippedDate","ShipVia","Freight","ShipName","ShipAddress","ShipCity","ShipRegion","ShipPostalCode","ShipCountry","Customer","Employee","Shipper"})

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

新增自訂

= Table.AddColumn (移除Columns,"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 (#"群組列",{{"Year",Date.Year,Int64.Type}})

變更為 

更有意義的名稱、OrderDate 和 Year

重新命名欄 1

Table.RenameColumns

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

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

GroupedRows

= Table.Group (重新命名Columns1,{"Year","Order_Details.ProductID"},{{"Total Sales",每個 List.Sum ([行合計]) ,輸入 number}})

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

在這項工作中,您可以使用合併查詢和展開作業來合併產品與總銷售額查詢,然後將每個產品的總銷售額查詢載入至 Excel模型。

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

  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的合併資料行,以在產品查詢中建立兩個新資料行:YearTotal Sales

  1. 資料預覽中,選取NewColumn 旁 ( 展開 ) 展開圖示。

  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 (來源,"總銷售額",{"Year","Total Sales"},{"Total Sales.Year","Total Sales.Total Sales"})

重新命名兩欄

重新命名的欄

= Table.RenameColumns (#"展開的總銷售額",{{"Total Sales.Year","Year"},{"Total Sales.Total Sales","Total Sales"}})

以遞增順序排序總銷售額

排序的列

= Table.sort ( #"重新命名的欄",{{"Total Sales", Order.Ascending}})

另請參閱

Power Query for Excel説明

需要更多協助?

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

這項資訊有幫助嗎?

×