Trong phần hướng dẫn này, bạn có thể sử dụng Trình soạn thảo Truy vấn của Power Query để nhập dữ liệu từ tệp Excel cục bộ chứa thông tin sản phẩm và từ nguồn cấp OData chứa thông tin đơn hàng sản phẩm. Bạn 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 theo Sản phẩm và Năm".
Để thực hiện theo hướng dẫn này, bạn cần sổ làm việc Products. Trong hộp thoại Lưu Dưới dạng, hãy đặt tên tệp là Products and Orders.xlsx.
Bạn đang xem: Cách tổng hợp dữ liệu từ nhiều file excel
Trong tác vụ này, bạn nhập sản phẩm từ tệp Products and 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
Tạo sổ làm việc Excel.
Chọn Hộp dữ > nhận dữ > từ tệp và > 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 làm điều thuận tiện cho bạn. Kiểm tra từng bước bên dưới Các bước Áp dụng trong ngăn Cài đặt 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 ra 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 Cài đặt. Bước này được tạo ra 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 Sửa Cài đặt. Bước này được tạo ra bởi Power Query mà đã suy ra 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 toàn bộ công thức.
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,CategoryIDvà QuantityPerUnit (sử dụng Ctrl+Bấm hoặc Shift+Bấm).
Chọn Loại bỏ cột > xóa 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 Products vào trang tính Excel tính.
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: 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, các bước truy vấn sẽ được tạo và liệt kê trong ngăn Cài đặ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ông thức Power Query, hãy xem Tạo công thức Power Query Excel.
Nhập sổ làm Excel việc | 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{ |
Power Query tự động phát hiện các 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"}) |
Trong tác vụ này, bạn nhập dữ liệu vào sổ làm việc Excel của mình 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 tổng dòng, chuyển đổi hàng OrderDate bằng 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: chuyển Kết nối nguồn cấp OData
Chọn Tùy chọn > nhận dữ > từ các nguồn khác > Từ 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 ( Đơnhàng).
Trong Power Query, cột chứa bảng liên quan có giá trị Bản ghi hoặc Bảng trong ô. Đâ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à 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 biểu thị 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 biểu thị 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 chỉ rõ 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 dữ 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.
Trong Xem trước Dữliệu , cuộn theo chiều ngang Order_Details cột.
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ác cột.
Chọn ProductID, UnitPricevà Quantity.
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 .
Trong Xem trước Dữliệu, hãy chọn các cột sau đây:
Chọn cột đầu tiên, OrderID.
Shift+Click 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ộ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 .
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.Xem thêm: Dấu Hiệu Ung Thư Phổi Giai Đoạn Cuối Như Thế Nào? Tìm Hiểu Ngay!
Bấm 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
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 > Year.
Đổi tên cột OrderDate thành Year:
Bấm đúp chuột vào cột OrderDate và nhập Năm hoặc
Right-Click trên 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
Trong Bản xem trước Dữliệu , chọn YearOrder_Details.ProductID.
Right-Click 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.
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 hộp Excel, hãy đổi tên truy vấn:
Trong ngăn Số Cài đặt, 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, các bước truy vấn sẽ được tạo và liệt kê trong ngăn Cài đặ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ông thức Power Query, hãy xem Tìm hiểu về công thức Power Query.
Kết nối với nguồn cấp OData | Nguồn | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, |
Chọn một bảng | Dẫn hướng | = Source{ |
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 = Table.AddColumn(#"Expanded Order_Details", "Line Total", each |
Đổi thành tên có ý nghĩa hơn, Tổng Lne | Đã đổi tên Cột | = 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, Ngày Đặt hàng và Năm | 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( |
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à 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 Hợp nhất.
Bước 1: Phối ProductID cùng với truy vấn Tổng Doanh thu
Trong sổ làm Excel, dẫn hướng đến 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ọn Truy vấn>phối.
Trong hộp thoại Phối, chọn Sản phẩm làm bảng đầu tiên, 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.
Để 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.
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ảnphẩm ), và một cột có cấu trúc Bảng đơn đến bảng liên quan ( Tổng Doanhthu). 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 được phối
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 , hãy chọn Bung rộng biểu tượ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á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 xem sản phẩm nào và năm nào mà sản phẩm đạt doanh số cao nhất, hãy chọn Sắp xếp Giảm dần theoTổng Doanh thu.
Đổ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ột Mô hình Dữ liệu Excel, để xây dựng một 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ô Excel Liệu, bạncó thể sử dụng Power Pivot để phân tích thêm dữ liệu của mình.
Chọn Trang > Đóng & Tải.
Trong hộp thoại Nhập Dữ liệu, hãy đảm bảo rằng 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 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: Các bước 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 Cài đặ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ông thức Power Query, hãy xem Tìm hiểu về công thức Power Query.
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 Được bung rộng | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
Đổi tên hai cột | Đã đổi tên Cột | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |