Hàm QUERY nhiều sheet

Nội dung bài viết

Trường hợp lưu trữ dữ liệu thành từng Tab trong một file Excel vô cùng phổ biến. Đó có thể là dữ liệu theo từng tháng trong năm với cấu trúc giống nhau mà bạn cần phải kết hợp dữ liệu từ những Tab này trong cùng một file Excel khi bạn đang sử dụng Power BI hoặc Power query/Get & Transform trong Excel. Và trong bài blog này, chúng ta sẽ cùng bàn về cách tích hợp dữ liệu từ các Tab trong 1 File Excel và thậm chí là áp dụng cùng một quy trình thay đổi dữ liệu cho từng tab vì cấu trúc của chúng giống nhau. Hi vọng đây sẽ là một ứng dụng tuyệt vời của Power BI giúp bạn xử lý gọn nhẹ việc liên kết các bảng dữ liệu tách rời này.
Trước hết, để thực hành, các bạn hãy chuẩn bị nguồn đơn giản trong 1 file Excel như sau. Giả sử bạn có một bảng tính Excel với bốn Tab: Q1, Q2, Q3 và Q4. Trong mỗi Tab là một số dữ liệu bán hàng trong ba tháng trong mỗi quý. Ví dụ như bảng tính Q1 trông như thế này:

Trang tính Q2 trông như thế này:

… và như vậy. Đầu ra cần thiết cho Power BI nên là một bảng trông giống như thế này:

Trong trường hợp mỗi Tab có một bảng với tên cột giống nhau trên đó, chúng ta có thể dễ dàng sử dụng Append để nối chúng lại thành 1 bảng chính. Khi kết nối với file Excel, chúng ta sẽ thấy các bảng tính được bao gồm trong đó như dưới đây.

Nhiều bạn nghĩ chúng ta có sử dụng icon ở cột Data để mở các dữ liệu ở từng Tab và Append chúng như dưới đây. Tuy nhiên trong trường hợp đặc biệt này, vì cấu trúc của từng bảng thuộc dạng Semi-Tabular thay vì Tabular, nó không giải quyết được vấn đề, bởi vì chúng ta nhận được điều này:

AHA, bạn có thể nói, chúng ta phải chuyển đổi dữ liệu trước khi chúng ta có thể kết hợp nó và vì vậy chúng ta cần phải tạo ra một chức năng (Function) và thực hiện nó cho mỗi bảng tính để chuyển hóa về dạng Tabular trước khi có thể Append. Và điều đó là có thể, trên lý thuyết thì nghe có vẻ là dễ nhưng điều khó khăn là đi vào chi tiết. Hãy cùng mình khám phá cách thức hoạt động của nó nhé! Và sau đây là giải pháp theo từng bước một:

Bước 1: Chọn một bảng mẫu trong các Tab được liệt kê

Trong Power BI, hãy kết nối với tệp Excel của bạn như bình thường. Sau đó, bạn không cần chọn bất cứ bảng nào vội mà chỉ cần ấn chuột phải vào Folder Excel ‘Sample.xlsx’, sau đó Transform Data:

Kết quả sẽ là một bảng trông giống như thế này: (Chúng ta chỉ dừng ở bước kết nối với file Excel)

Nếu bạn cần, lọc ra bất kỳ hàng nào không chứa “Sheet” trong cột Kind và cũng lọc ra bất kỳ bảng tính nào mà bạn không muốn kết hợp dữ liệu.

Bước 2: Tạo truy vấn mẫu của bạn

Nhân đôi truy vấn (Duplicate) ở trên và gọi là mẫu truy vấn mới (Sample Query).

Từ đây, trong bảng truy vấn mẫu Sample Query, chúng ta sẽ thực hiện các bước cần thiết để chuyển hóa dữ liệu. Trước tiên, chọn một trong các bảng tính để sử dụng để xây dựng các bước truy vấn sẽ được áp dụng cho tất cả các bảng tính khác và lọc bảng trên để nó chỉ chứa hàng cho bảng tính đó. Trong trường hợp này tôi đang sử dụng bảng tính được gọi là Q1:

Sau đó, và điều này là quan trọng: loại bỏ tất cả các cột khác trong bảng ngoại trừ cột Data:

Làm điều này giúp sử dụng ngôn ngữ M được tạo ra cho bước tiếp theo bạn sẽ làm. Việc loại bỏ tất cả các cột này thay đổi cách mà M thực hiện và đảm bảo rằng tên của bảng tính sẽ không được mã hóa cứng ở bất cứ đâu.
Sau đó nhấp vào liên kết “Table” bên trong ô và bạn sẽ thấy nội dung của bảng tính:

Khi làm như vậy, Power BI sẽ tự động bổ sung thêm 2 bước đó chính là Promoted Header (chuyển dòng đầu thanh tên trường) và Change Types (thay đổi định dạng). Chúng ta cần để ý bước Change Type sẽ gọi ra tên trường và điều này là không nên có khi áp dụng cho các bảng ở Tab khác vì tên trường sẽ khác hoàn toàn.

Vì vậy, chúng ta cần xóa đi bước “ChangeType”:

Bây giờ bạn có thể thực hiện bất kỳ biến đổi nào khác mà bạn cần trên truy vấn này, nhưng bạn sẽ cần phải tránh bất kỳ biến đổi nào tạo ra mã M đề cập đến bất kỳ tên cột nào trên bảng tính. Vì chúng ta sẽ áp dụng các bước thay đổi này với các bảng tính khác bao gồm tên các trường khác nhau. Hãy luôn nhớ rằng, những biến đổi này sẽ cần phải được áp dụng cho các bảng tính khác và chúng sẽ thất bại nếu chúng áp dụng cho các cột không phải là cột hiện tại. Đây là lý do tại sao bạn phải xoá bước thay đổi loại trước đó, bởi vì nó đặt các loại vào tháng một, tháng hai và tháng ba, và bạn có thể mở trình chỉnh sửa nâng cao (Advance Editor) và kiểm tra mã M cho toàn bộ truy vấn chỉ để đảm bảo không có bất cứ tên trường nào được gọi ra.
Trong trường hợp này tất cả những gì chúng ta cần làm là unpivot các cột tháng bằng cách chọn cột “sản phẩm” và sử dụng “Unpivot Other Column” trên Transform tab, và sau đó đổi tên các cột thích hợp:

Việc có tên trường Sản Phẩm ở đây sẽ không phải vấn đề vì ở các Tab đều có tên trường này.

Bước 3: Tạo một chức năng

Tiếp theo, bạn cần tạo một tham số mới bằng cách nhấp vào nút Manange Parameters/New Parameters, gọi tham số Worksheet, đặt kiểu dữ liệu thành văn bản và trả về tên của bảng tính bạn đã chọn trong bước trước:

Bây giờ, quay lại truy vấn mẫu, tìm các bước được gọi là lọc hàng về phía đầu mà bạn đã lọc xuống một bảng tính và nhấp vào biểu tượng bánh răng bên cạnh bước để chỉnh sửa nó:

Sau đó, chỉnh sửa bước để nó sử dụng giá trị trả lại bằng tham số để lọc theo thay vì giá trị cứng mã hoá mà bạn đã nhập trước đó. Để thực hiện việc này, hãy nhấp vào nút Hiển thị bên dưới, chọn tham số và sau đó chọn tham số Worksheet trong hộp thả xuống tiếp theo:

Cuối cùng, đi tới ngăn truy vấn ở bên trái màn hình và nhấp chuột phải vào truy vấn mẫu và chọn “Create Function”:

Bạn sẽ được nhắc để cung cấp cho các chức năng mới một tên; gọi nó là GetData:

Và chúng ta đã tạo được một chức năng Function là Get Data bao gồm Mẫu Sample Query và Parameter

Bước 4: Gọi hàm và kết hợp dữ liệu

Cuối cùng, quay lại bản sao trùng lặp của truy vấn ban đầu được tạo ở đầu bước 2. Sau đó đi đến “Add column tab” trên thanh công cụ và nhấp vào nút “Invoke Custom Function” và gọi hàm GetData, đi qua trong nội dung của cột tên cho tham số chỉ của hàm:

Cuối cùng, nhấp vào Expand/Aggregate trên cột mới và mở rộng các bảng lồng nhau:

Sau khi loại bỏ bất kỳ cột không cần thiết nào, bạn sẽ thấy dữ liệu từ tất cả các bảng tính kết hợp thành một bảng duy nhất như mong muốn:

Đừng quên đặt các loại dữ liệu trên mỗi cột!
Hi vọng bài viết trên sẽ giúp các bạn có thể giải quyết được một trong những bài toán điển hình với dữ liệu ở Excel được trả ra các Tab khác nhau có cùng cấu trúc. Và giờ thì happy analyzing!