Trong tài liệu hướng dẫn này, hãy sử dụng Trình soạn thảo truy vấn của Power Query để nhập dữ liệu từ một tệp Excel cục bộ chứa thông tin sản phẩm và từ một nguồn cấp OData chứa thông tin đơn hàng sản phẩm. Thực hiện các bước chuyển đổi và tổng hợp, rồi kết hợp dữ liệu từ cả hai nguồn để tạo báo cáo Tổng Doanh thu trên mỗi Sản phẩm và Năm .
Để hoàn thành hướng dẫn này, bạn cần có sổ làm việc Sản phẩm . Trong hộp thoại Lưu Dưới dạng, hãy đặt tên tệp là Products and Orders.xlsx.
Tác vụ 1: Nhập sản phẩm vào sổ làm việc Excel
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 trong phần trước) vào sổ làm việc Excel. Sau đó, bạn tăng cấp hàng làm tiêu đề cột, loại bỏ một số cột và tải truy vấn vào trang tính.
Bước 1: Kết nối với một sổ làm việc Excel
- Tạo sổ làm việc Excel.
- Chọn Dữ liệu>: Lấy dữ liệu>từ tệp>từ sổ làm việc.
- 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ở.
- Trong ngăn Bộ dẫn hướng , bấm đúp vào bảng Sản phẩm . Trình soạn thảo Power Query xuất hiện.
Bước 2: Kiểm tra các Bước Truy vấn
Theo mặc định, Power Query tự độ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 Bước đã áp dụng trong ngăn Cài đặt Truy vấn để tìm hiểu thêm.
- 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 khi bạn nhập sổ làm việc.
- Bấm chuột phải vào bước Dẫn hướng , rồi chọn Chỉnh sửa Cài đặt. Bước này được tạo khi bạn chọn bảng từ hộp thoại Dẫn hướng .
- Bấm chuột phải vào bước Loại Đã thay đổi , rồi chọn Chỉnh sửa Cài đặt. Bước này được tạo ra bởi Power Query, theo đó phỏng đoán các kiểu dữ liệu của mỗi 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.
- Trong Xem trước Dữ liệu, chọn các cột ProductID,ProductName, CategoryID và QuantityPerUnit (sử dụng Ctrl+Click hoặc Shift+Click).
- Chọn Loại bỏ cột,>Loại bỏ các 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 Sản phẩm vào trang tính Excel.
- Chọn Trang chủ>, Đóng & tải. Truy vấn sẽ xuất hiện trong một trang tính Excel mới.
Tóm tắt: Các bước Power Query được tạo trong Tác vụ 1
Khi bạn thực hiện các hoạt động truy vấn trong Power Query, nó sẽ tạo các bước truy vấn và liệt kê chúng 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 về các công thức Power Query, hãy xem tài liệu về Power Query.
| 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", 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}}) |
| Xóa các cột khác để chỉ hiển thị các cột bạn muốn | Đã loại bỏ các cột khác | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Tác vụ 2: Nhập dữ liệu đơn hàng từ nguồn cấp OData
Trong tác vụ này, bạn nhập dữ liệu vào sổ làm việc Excel của bạn từ nguồn cấp OData Northwind 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 tổng số dòng, chuyển đổi OrderDate, nhóm các hàng theo ProductID và Year, đổi tên truy vấn và tắt tính năng 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
- Chọn Dữ liệu>: Lấy dữ liệu>từ các nguồn> kháctừ nguồn cấp OData.
- Trong hộp thoại Nguồn cấp OData Feed, nhập URL cho nguồn cấp Northwind OData.
- Chọn OK.
- 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, cột chứa bảng liên quan có giá trị Bản ghi hoặ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 đơn lẻ và đại diện cho mối quan hệ một-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à đại diện cho mối quan hệ một-nhiều với bảng hiện tại hoặc bảng chính. Cột có cấu trúc đại diện cho 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 biểu thị một thực thể với một 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 một cơ sở dữ liệu SQL Server.
Sau khi bạn bung rộng bảng Order_Details , ba cột mới và các hàng bổ sung sẽ đượ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.
Trong Xem trước Dữ liệu, cuộn theo chiều ngang đến cột Order_Details .
Trong cột Order_Details , chọn biểu tượng bung rộng (
).Trong menu thả xuống Bung rộng:
Chọn (Chọn Tất cả Cột) để xóa tất cả cột.
Chọn ProductID,UnitPrice và Quantity.
Chọn OK.
Lưu ý
Trong Power Query, bạn có thể bung rộng các bảng được nối kết từ một cột và tổng hợp các cột của bảng nối kết trước khi bung rộng dữ liệu trong bảng chủ đề. Để biết thêm thông tin 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 (Power Query).
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ừ cột Ngày_đặt_hàng, ID_sản_phẩm, Đơn_Giá và Số lượng .
Trong Xem trước Dữ liệu, chọn các cột sau đây:
- Chọn cột đầu tiên, OrderID.
- Shift+Bấm vào cột cuối cùng, Shipper.
- Ctrl+Click vào các cột OrderDate, Order_Details.ProductID, Order_Details.UnitPrice và Order_Details.Quantity.
Bấm chuột phải vào tiêu đề cột đã chọn, rồi chọn Loại bỏ các cột khác.
Bước 4: Tính tổng dò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 .
- 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. - Chọn Thêm cột tùy chỉnh.
- 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.Đơn_Giá] * [Order_Details.Số lượng].
- Trong hộp Tên cột mới , nhập Tổng dòng.
- 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.
Trong Xem trước Dữ liệu, bấm chuột phải vào cột OrderDate , rồi chọn Chuyển đổi>Năm.
Đổi tên cột OrderDate thành Year:
- Bấm đúp vào cột OrderDate và nhập Year hoặc
- Bấm chuột phải vào cột OrderDate , chọn Đổi tên, rồi nhập Year.
Bước 6: Nhóm các hàng bằng ProductID và Year
Trong Xem trước Dữ liệu, chọn Year và Order_Details.ProductID.
Bấm chuột phải vào một trong các tiêu đề, rồi chọn Nhóm Theo.
Trong hộp thoại Nhóm Theo:
- Trong hộp văn bản Tên cột mới, nhập Tổng Doanh thu.
- Trong danh sách thả xuống Thao tác, chọn Tính tổng.
- Trong danh sách thả xuống Cột, chọn Tổng Dòng.
Chọn OK.
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: Các bước 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 trong Power Query, nó sẽ tạo các bước truy vấn và liệt kê chúng 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 về các công thức Power Query, hãy xem tài liệu về Power Query.
| 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]) |
| Đổi sang tên có ý nghĩa hơn, Lne Total | Cột được đổi tên | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Chuyển đổi cột OrderDate thành năm | Năm được trích xuất | = Table.TransformColumns(#"Hàng đã nhóm",{{"Year", Date.Year, Int64.Type}}) |
| Thay đổi thành tên có ý nghĩa hơn, OrderDate và Year |
Đổi tên Cột 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}}) |
Tác vụ 3: Kết hợp các truy vấn Sản phẩm và Tổng Doanh thu
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. Bạn có thể thực hiện thao tác Phối trên bất kỳ truy vấn Power Query nào với hình dạng bảng, bất kể nguồn dữ liệu. Để 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 (Power Query).
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 , sau đó 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
Trong sổ làm việc Excel, đi tới truy vấn Sản phẩm trên tab trang tính Sản phẩm .
Chọn một ô trong truy vấn, rồi chọnPhối Truyvấn>.
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 một cột có cấu trúc mới với biểu tượng bung rộng.
Để 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 .
Trong hộp thoại Mức độ Riêng tư:
- 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.
- Chọn Lưu.
Chọn OK.
Lưu ý
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 Thiết đặt Cấp độ Bảo mật (Power Query).
Kết quả
Thao tác 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 đầu tiên (Sản phẩm) và một cột có cấu trúc Bảng đơn lẻ đế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.
Trong bước này, bạn bung rộng cột đã phối với 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.
Trong Xem trước Dữ liệu, chọn biểu tượng Bung rộng (
) bên cạnh NewColumn.Trong danh sách thả xuống Bung rộng :
- Chọn (Chọn Tất cả Cột) để xóa tất cả cột.
- Chọn Năm vàTổng Doanh thu.
- Chọn OK.
Đổi tên hai cột này thành Năm và Tổng Doanh thu.
Để tìm hiểu những sản phẩm nào và năm nào mà các sản phẩm đạt mức doanh thu cao nhất, hãy chọn Sắp xếp Giảm dần theo Tổng Doanh số.
Đổ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 một truy vấn vào Mô hình Dữ liệu Excel, để bạn có thể lập một báo cáo nối kết đến 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 bạn.
- Chọn Trang chủ>, Đóng & tải.
- 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 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 vào 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: Các bước Power Query được tạo trong Tác vụ 3
Khi bạn thực hiện các hành độ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 về các công thức Power Query, hãy xem tài liệu về Power Query.
| 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 số theo thứ tự tăng dần | Hàng đã Sắp xếp | = Table.Sort(#"Cột đã đổi tên",{{"Tổng Doanh số", Order.Ascending}}) |