Tìm Hiểu Cách Kết Hợp Nhiều Nguồn Dữ Liệu (Power Query)

Trong tác vụ này, bạn nhập dữ liệu vào sổ làm việc Excel từ nguồn cấp Northwind OData mẫu tại http://services.odata.org/Northwind/Northwind.svc,bung rộng bảng Order_Details, loại bỏ cột, tính toán tổng dòng, chuyển đổi Ngày_Đặt_hàng, nhóm các hàng theo ProductID và Year, đổi tên truy vấn và tắt tải xuống truy vấn vào sổ làm việc Excel.

Bước 1: Kết nối với Nguồn cấp OData

1. Chọn Dữ >Lấy dữ liệu> nguồn khác từ nguồn > từ Nguồn cấp OData.

2. Trong hộp thoại Nguồn cấp OData Feed, nhập URL cho nguồn cấp Northwind OData.

3. Chọn OK.

4. Trong ngăn Bộ dẫn hướng , bấm đúp vào bảng Đơn hàng.

Bước 2: Bung rộng bảng Order_Details

Trong bước này, bạn bung rộng bảng Order_Details liên quan đến bảng Đơn hàng, để kết hợp các cột ProductID, UnitPrice và Quantity từ Order_Details thành bảng Đơn hàng . Thao tác Bung rộng kết hợp các cột từ bảng liên quan thành một bảng chủ đề. Khi truy vấn chạy, các hàng từ bảng liên quan (Order_Details) được kết hợp thành các hàng với bảng chính (Đơn hàng).

Trong Power Query, một cột chứa một bảng liên quan có giá trị Là bản ghihoặc Bảng trong ô. Những cột này được gọi là cột có cấu trúc. Bản ghi cho biết một bản ghi có liên quan duy nhất và thểhiện mối quan hệ một đối một với dữ liệu hiện tại hoặc bảng chính. Bảng cho biết một bảng có liên quan và thể hiện mối quan hệ một đối nhiều với bảng hiện tại hoặc bảng chính. Cột có cấu trúc thể hiện mối quan hệ trong nguồn dữ liệu có mô hình quan hệ. Ví dụ: cột có cấu trúc cho biết một thực thể có liên kết khóa ngoại trong nguồn cấp OData hoặc mối quan hệ khóa ngoại trong cơ sở dữ SQL Server liệu.

Sau khi bạn bung rộng bảng Order_Details, ba cột mới và các hàng bổ sung được thêm vào bảng Đơn hàng, ứng với mỗi hàng trong bảng lồng hoặc bảng liên quan.

1. Trong Xem trước Dữ liệu, cuộn theo chiều ngang Order_Details cột.

2. Trong cột Order_Details , chọn biểu tượng bung rộng ().

3. Trong menu thả xuống Bung rộng:

   1. Chọn (Chọn Tất cả Cột) để xóa tất cả cột.

   2. Chọn ProductID, UnitPrice và Quantity.

   3. Chọn OK.

      

      Lưu ý: Trong Power Query, bạn có thể bung rộng các bảng được liên kết từ một cột và tổng hợp các cột của bảng được liên kết trước khi bung rộng dữ liệu trong bảng chủ đề. Để biết thêm chi tiết về cách thực hiện các thao tác tổng hợp, hãy xem Tổng hợp dữ liệu từ cột.

Bước 3: Xóa các cột khác để chỉ hiển thị các cột bạn muốn

Trong bước này bạn xóa tất cả các cột ngoại trừ cột OrderDate, ProductID, UnitPrice và Quantity . 

1. Trong Xem trước Dữ liệu, hãy chọn các cột sau đây:

   1. Chọn cột đầu tiên, ORDERID.

   2. Shift+Bấm vào cột cuối cùng, Công cụ giao hàng.

   3. Ctrl+Click vào các cột OrderDate, Order_Details.ProductID, Order_Details.UnitPrice và Order_Details.Quantity.

2. Bấm chuột phải vào tiêu đề cột đã chọn, rồi chọn Loại bỏ Cột Khác.

Bước 4: Tính dòng tổng cộng cho mỗi hàng Order_Details

Trong bước này, bạn tạo một Cột Tùy chỉnh để đếm tổng số dòng cho mỗi hàng Order_Details .

1. Trong Xem trước Dữ liệu, chọn biểu tượng bảng () ở góc trên cùng bên trái của bản xem trước.

2. Bấm Thêm Cột Tùy chỉnh.

3. Trong hộp thoại Cột Tùy chỉnh, trong hộp Công thức cột tùy chỉnh, nhập [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. Trong hộp Tên cột mới , nhập Tổng Dòng.

5. Chọn OK.

Bước 5: Chuyển đổi cột năm OrderDate

Trong bước này, bạn chuyển đổi cột OrderDate để kết xuất năm ngày tháng của đơn hàng.

1. Trong Bản xem trước Dữ liệu, bấm chuột phải vào cột Ngày _Đặt_hàng, rồi chọn Chuyển đổi > Năm.

2. Đổi tên cột OrderDate thành Year:

   1. Bấm đúp chuột vào cột OrderDate và nhập Năm hoặc

   2. Right-Click cột OrderDate , chọn Đổi tên, rồi nhập Năm.

Bước 6: Nhóm các hàng bằng ProductID và Year

1. Trong Bản xem trước Dữ liệu, chọn YearOrder_Details.ProductID.

2. Right-Click một trong các tiêu đề, rồi chọn Nhóm Theo.

3. Trong hộp thoại Nhóm Theo:

   1. Trong hộp văn bản Tên cột mới, nhập Tổng Doanh thu.

   2. Trong danh sách thả xuống Thao tác, chọn Tính tổng.

   3. Trong danh sách thả xuống Cột, chọn Tổng Dòng.

4. Chọn OK.

   

Bước 7: Đổi tên truy vấn

Trước khi bạn nhập dữ liệu bán hàng vào Excel, hãy đổi tên truy vấn:

• Trong ngăn Thiết đặt Truy vấn, trong hộp Tên, nhập Tổng Doanh thu.

Kết quả: Truy vấn cuối cùng cho Tác vụ 2

Sau khi bạn thực hiện từng bước, bạn sẽ có một truy vấn Tổng Doanh thu trên nguồn cấp Northwind OData.

Tóm tắt: Power Query được tạo trong Tác vụ 2 

Khi bạn thực hiện các hoạt động truy vấn Power Query, các bước truy vấn sẽ được tạo và liệt kê trong ngăn Thiết đặt Truy vấn, trong danh sách Các bước Áp dụng. Mỗi bước truy vấn có một công thức Power Query, cũng được gọi là ngôn ngữ "M". Để biết thêm thông tin Power Query công thức, hãy xem tìm hiểu Power Query thức mới.

Tác vụ	Bước truy vấn	Công thức
Kết nối với nguồn cấp OData	Nguồn	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Chọn một bảng	Dẫn hướng	= Source{[Name="Orders"]}[Data]
Bung rộng bảng Order_Details	Bung rộng Order_Details	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Xóa các cột khác để chỉ hiển thị các cột bạn muốn	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "ShipPer"})
Tính dòng tổng cộng cho mỗi hàng Order_Details	Đã thêm Tùy chỉnh	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Thay đổi thành tên có ý nghĩa hơn, Tổng Lne	Cột được đổi tên	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Chuyển đổi cột OrderDate thành năm	Năm Trích xuất	= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
Thay đổi thành  tên có ý nghĩa hơn, OrderDate và Year	Cột được đổi tên 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Nhóm các hàng theo ProductID và Year	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})