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 sản phẩm từ tệp Sản phẩm và Orders.xlsx (đã tải xuống và đổi tên ở trên) vào sổ làm việc Excel, tăng cấp hàng thành tiêu đề cột, loại bỏ một số cột và tải truy vấn vào một trang tính.

Bước 1: Kết nối với một sổ làm việc Excel

1. Tạo sổ làm việc Excel.

2. Chọn Dữ> lấy dữ liệu >từ tệp >từ sổ làm việc.

3. Trong hộp thoại Nhập Dữ liệu, duyệt và định vị tệp Products.xlsx bạn đã tải xuống, rồi chọn Mở.

4. Trong ngăn Bộ dẫn hướng , bấm đúp vào bảng Sản phẩm. Nút Nguồn sẽ Trình soạn thảo truy vấn hiện.

Bước 2: Kiểm tra các bước truy vấn

Theo mặc định, Power Query động thêm một vài bước để thuận tiện cho bạn. Kiểm tra từng bước bên dưới Các bước Được áp dụng trong ngăn Thiết đặt Truy vấn để tìm hiểu thêm.

1. Bấm chuột phải vào bước Nguồn, rồi chọn Chỉnh sửa Cài đặt. Bước này được tạo ra khi bạn nhập sổ làm việc.

2. Bấm chuột phải vào bước Dẫn hướng, rồi chọn Chỉnh sửa Thiết đặt. Bước này được tạo khi bạn chọn bảng từ hộp thoại Dẫn hướng.

3. Bấm chuột phải vào bước Loại đã Thay đổi, rồi chọn Chỉnh sửa Thiết đặt. Bước này được tạo ra Power Query suy ra kiểu dữ liệu của từng cột. Chọn mũi tên xuống ở bên phải thanh công thức để xem công thức hoàn chỉnh.

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 sẽ loại bỏ tất cả các cột, ngoại trừ ProductID, ProductName, CategoryID và QuantityPerUnit.

1. Trong Bản xem trước Dữ liệu, chọn các cột ProductID, ProductName, CategoryID và QuantityPerUnit (sử dụng Ctrl+Bấm hoặc Shift+Bấm).

2. Chọn Loại bỏ Cột > bỏ cột khác.

   

Bước 4: Tải truy vấn sản phẩm

Trong bước này, bạn tải truy vấn Products vào trang tính Excel.

• Chọn Trang > Đóng & Tải. Truy vấn sẽ xuất hiện trong trang tính Excel mới.

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

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 mới, hãy xem Power Query công thức trong Excel.

Tác vụ	Bước truy vấn	Công thức
Nhập sổ làm việc Excel	Nguồn	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Chọn bảng Sản phẩm	Dẫn hướng	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query tự động phát hiện kiểu dữ liệu cột	Loại đã Thay đổi	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", nhập text}, {"Id_Nhà_cung_cấp", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", nhập text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Xóa các cột khác để chỉ hiển thị các cột bạn muốn	Đã loại bỏ cột khác	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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}})

Power Query cho phép bạn kết hợp nhiều truy vấn, bằng cách phối hoặc chắp thêm truy vấn. Thao tác Phối được thực hiện trên bất cứ truy vấn nào của Power Query với hình dạng bảng, độc lập với nguồn dữ liệu mà dữ liệu xuất phát từ đó. Để biết thêm thông tin về việc kết hợp các nguồn dữ liệu, hãy xem Kết hợp nhiều truy vấn.

Trong tác vụ này, bạn kết hợp các truy vấn Sản phẩm và Tổng Doanh thu bằng cách sử dụng truy vấn Phối và thao tác Bung rộng, rồi tải truy vấn Tổng Doanh thu theo Sản phẩm vào Mô hình Dữ liệu Excel.

Bước 1: Phối ProductID cùng với truy vấn Tổng Doanh thu

1. Trong sổ làm việc Excel, dẫn hướng đến truy vấn Sản phẩm trên tab trang tính Sản phẩm.

2. Chọn một ô trong truy vấn, rồi chọn Truy vấn> Phối.

3. Trong hộp thoại Phối , chọn Sản phẩm làm bảng chính, rồi chọn Tổng Doanh thu làm bảng phụ hoặc truy vấn liên quan để phối. Tổng Doanh thu sẽ trở thành cột có cấu trúc mới với biểu tượng bung rộng.

4. Để khớp Tổng Doanh thu với Sản phẩm theo ProductID, chọn cột ProductID từ bảng Sản phẩm và cột Order_Details.ProductID từ bảng Tổng Doanh thu .

5. Trong hộp thoại Mức độ Riêng tư:

   1. Chọn Thuộc tổ chức cho mức độ độc lập riêng tư của bạn đối với cả hai nguồn dữ liệu.

   2. Chọn Lưu.

6. Chọn OK.

   Ghi chú Bảo mật:  Mức độ Riêng tư ngăn chặn người dùng vô tình kết hợp dữ liệu từ nhiều nguồn dữ liệu, có thể là riêng tư hoặc thuộc tổ chức. Tùy vào truy vấn, người dùng có thể vô tình gửi dữ liệu từ nguồn dữ liệu riêng tư đến một nguồn dữ liệu khác có thể gây hại. Power Query phân tích mỗi nguồn dữ liệu và phân loại thành cấp độ bảo mật xác định: Công cộng, Tổ chức và Cá nhân. Để biết thêm thông tin về Cấp độ Bảo mật, hãy xem Đặt Cấp độ Bảo mật.

   

Kết quả

Phép toán Phối sẽ tạo ra một truy vấn. Kết quả truy vấn chứa tất cả các cột từ bảng chính (Sản phẩm) và một cột có cấu trúc bảng duy nhất đến bảng liên quan (Tổng Doanh thu). Chọn biểu tượng Bung rộng để thêm cột mới vào bảng chính từ bảng phụ hoặc bảng liên quan.

Bước 2: Bung rộng cột đã phối

Trong bước này, bạn bung rộng cột đã phối có tên NewColumn để tạo hai cột mới trong truy vấn Sản phẩm: Năm và Tổng Doanh thu.

1. Trong Xem trước Dữ liệu, chọn Bung rộng biểu tượng () bên cạnh NewColumn.

2. Trong danh sách 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 Năm vàTổng Doanh thu.

   3. Chọn OK.

3. Đổi tên hai cột này thành Năm và Tổng Doanh thu.

4. Để tìm hiểu những sản phẩm nào và trong những năm nào các sản phẩm có doanh số bán hàng cao nhất, hãy chọn Sắp xếp Giảm dần theoTổng Doanh thu.

5. Đổi tên truy vấn thành Tổng Doanh thu theo Sản phẩm.

Kết quả

Bước 3: Tải truy vấn Tổng Doanh thu theo Sản phẩm vào Mô hình Dữ liệu Excel

Trong bước này, bạn tải truy vấn vào Mô hình Dữ liệu Excel để xây dựng báo cáo được kết nối với kết quả truy vấn. Sau khi bạn tải dữ liệu vào Mô hình Dữ liệu Excel, bạn có thể dùng Power Pivot để phân tích thêm dữ liệu của mình.

1. Chọn Trang > Đóng & Tải.

2. Trong hộp thoại Nhập Dữ liệu, hãy đảm bảo bạn chọn Thêm dữ liệu này vào Mô hình Dữ liệu. Để biết thêm thông tin về cách sử dụng hộp thoại này, hãy chọn dấu chấm hỏi (?).

Kết quả

Bạn có một truy vấn Tổng Doanh thu theo Sản phẩm kết hợp dữ liệu từ tệp Products.xlsx và nguồn cấp Northwind OData. Truy vấn này được áp dụng cho mô hình Power Pivot. Ngoài ra, các thay đổi đối với truy vấn sửa đổi và làm mới bảng kết quả trong Mô hình Dữ liệu.

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

Khi bạn thực hiện các hoạt động truy vấn Phối trong 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
Phối ProductID vào truy vấn Tổng Doanh thu	Nguồn (nguồn dữ liệu cho phép toán Phối)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Bung rộng cột phối	Tổng Doanh thu Mở rộng	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Đổi tên hai cột	Cột được đổi tên	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Sắp xếp tổng Doanh thu theo thứ tự tăng dần	Hàng đã Sắp xếp	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})