瞭解如何合併多個數據源 (Power Query)

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

步驟 1：連線至 Excel 活頁簿

1. 建立 Excel 活頁簿。

2. 選取 [數據 > 從活頁簿取得數據 > >]

3. 在 [ 匯入數據] 對話框中，流覽並找出您下載的 Products.xlsx 檔案，然後選取 [ 開啟]。

4. 在 [ 導覽] 窗格中，按兩下 [產品] 資料表。 Power 查詢編輯器 隨即出現。

步驟 2：檢查查詢步驟

根據預設，Power Query 會為您新增數個方便的步驟。 請在 [查詢設定] 窗格的 [套用步驟] 底下檢查每個步驟，以深入瞭解。

1. 以滑鼠右鍵按兩下 [來源 ] 步驟，然後選取 [編輯設定]。 此步驟是在您匯入活頁簿時建立的。

2. 以滑鼠右鍵按兩下 [導覽] 步驟，然後選 取 [編輯設定]。 此步驟是在您從 [ 導 覽] 對話框中選取數據表時建立。

3. 以滑鼠右鍵按兩下 [變更的類型] 步驟，然後選 取 [編輯設定]。 這個步驟是由 Power Query 所建立，Power Query 推斷每個數據行的數據類型。 選取數據編輯列右側的向下箭號以查看完整的公式。

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

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

1. 在 [數據預覽] 中，選取 [ProductID]、[ ProductName]、[ CategoryID] 和 [ QuantityPerUnit ] 欄， (使用 Ctrl+Click 或 Shift+單擊) 。

2. 選 取 [移除欄 > 移除其他欄]。

   

步驟 4：載入產品查詢

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

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

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

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

工作	查詢步驟	公式
匯入 Excel 活頁簿	來源	= Excel.Workbook (File.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 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”})

在此工作中，您可以從 http://services.odata.org/Northwind/Northwind.svc 的 Northwind OData 摘要範例將數據匯入至 Excel 活頁簿 、展開Order_Details數據表、移除欄、計算行總和、轉換 [訂單日期]、依 [產品標識符] 和 [年份] 將數據列組成群組、重新命名查詢，以及停用下載至 Excel 活頁簿的查詢。

步驟 1：連線至 OData 摘要

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 中，包含相關數據表的數據行的儲存格中含有 [記錄] 或 [表格] 值。 這些稱為結構化欄。 記錄表示單一相關記錄，並代表與目前數據或主數據表的一對一關係。 數據表表示關聯的數據表，並代表與目前或主表的一對多關聯。 結構化數據行代表具有關係模型之數據源中的關聯。 例如，結構化數據行會指出 OData 摘要中有外鍵關聯的實體，或 SQL Server 資料庫中的外鍵關聯。

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

1. 在 [數據預覽] 中，水平捲動到 Order_Details 欄。

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

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

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

   2. 選取 [ProductID]、 [單價] 和 [ 數量]。

   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. 選取 [確定]。

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

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

1. 在 [數據預覽] 中，以滑鼠右鍵按兩下 [訂單日期] 欄，然後選取 [> 年轉換]。

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

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

   2. Right-Click 在 [ 訂單日期] 欄上，選取 [ 重新命名]，然後輸入 [年份]。

步驟 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] (總銷售額) 的查詢。

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

當您在 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 (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 (#“Extended Order_Details”， “Line Total”， each [Order_Details.UnitPrice] * [Order_Details.Quantity])
變更為更有意義的名稱，Lne Total	重新命名的欄	= Table.RenameColumns (InsertedCustom，{{“Custom”， “Line Total”}})
轉換 [OrderDate] (訂單日期) 欄以轉換年份	解壓縮年份	= Table.TransformColumns (#“Grouped Rows”，{{“Year”， Date.Year， Int64.Type}})
變更為  更有意義的名稱、訂單日期和年份	重新命名的欄 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}})

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

   

結果

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

步驟 2：展開合併欄

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

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 模型。 此外，對查詢所做的變更會修改並重新整理數據模型中產生的數據表。

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

當您在 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}})