Tuy Power Query cung cấp hơn 600 hàm tiêu chuẩn, đôi khi chúng không đáp ứng được nhu cầu phân tích dữ liệu phức tạp. Khi đó, hàm tùy chỉnh chính là giải pháp tối ưu, cho phép bạn xử lý dữ liệu theo cách hiệu quả và linh hoạt hơn. Bài viết này sẽ hướng dẫn bạn cách tạo và sử dụng hàm tùy chỉnh trong Power Query, từ cơ bản đến nâng cao.
Hàm trong Excel thực hiện các tác vụ cụ thể, ví dụ hàm SUM cộng các giá trị và hàm VLOOKUP tìm kiếm giá trị tương ứng. Trong Power Query, hàm cũng hoạt động tương tự, thực hiện các tác vụ lặp lại, từ lấy dữ liệu từ website đến đọc dữ liệu từ file. Bài viết này sẽ tập trung vào việc tạo hàm tùy chỉnh, bao gồm cả việc chỉnh sửa mã M. Chúng ta sẽ tìm hiểu qua hai ví dụ: hàm tùy chỉnh đơn giản và hàm tùy chỉnh dựa trên phép biến đổi.
Ví dụ về hàm tùy chỉnh trong Power Query
Hàm Tùy Chỉnh là gì?
Hàm là tập hợp các quy trình hoặc bước thực hiện một công việc cụ thể một cách nhất quán. Ví dụ, hàm SUM trong Excel luôn cộng các giá trị, còn hàm VLOOKUP luôn trả về giá trị khớp từ danh sách. Trong Power Query, hàm tùy chỉnh có thể thực hiện nhiều tác vụ khác nhau, từ lấy dữ liệu từ website đến đọc dữ liệu từ tệp.
Hàm Tùy Chỉnh Đơn Giản
Ví dụ đầu tiên sẽ minh họa quá trình tạo một hàm tùy chỉnh đơn giản, nhân một giá trị với 10. Đầu tiên, tạo một truy vấn trống (Blank Query) và đặt tên là myFunction1 (khuyến khích đặt tên mô tả hơn). Mở Advanced Editor và nhập đoạn mã sau:
(Value) => Value * 10Đoạn mã trên khai báo tham số Value và nhân nó với 10. Sau khi đóng Advanced Editor, bạn có thể kiểm tra hàm bằng cách nhập giá trị vào ô Value và nhấn Invoke. Kết quả sẽ được hiển thị.
Để sử dụng hàm này trong một truy vấn khác (ví dụ, truy vấn Data), tạo một Custom Column và sử dụng công thức =myFunction1([Value]), trong đó [Value] là tên cột cần xử lý.
Minh họa GIF về VBA trong Excel
Giải Mã Mã M
Đoạn mã M trên rất đơn giản, nhưng để hiểu rõ hơn về cấu trúc của hàm tùy chỉnh, chúng ta sẽ phân tích một ví dụ phức tạp hơn.
Hàm Tùy Chỉnh Chứa Phép Biến Đổi
Ví dụ này sẽ tính toán tỷ lệ phần trăm của mỗi giao dịch so với tổng giá trị mua hàng của một khách hàng. Ví dụ, nếu khách hàng ABC đã mua hàng tổng cộng 122 đô la, mỗi giao dịch chiếm bao nhiêu phần trăm trong tổng số đó?
Đầu tiên, nhân đôi truy vấn Data và đặt tên là fnCustomerTotal. Xóa tất cả các bước trừ bước Source. Sau đó, sử dụng Group By để nhóm theo cột Customer và tính tổng giá trị trong cột Value. Tiếp theo, lọc theo một khách hàng cụ thể.
Mở Advanced Editor và chỉnh sửa mã M để biến truy vấn này thành một hàm. Khai báo hai tham số: sourceTable (bảng dữ liệu) và customerName (tên khách hàng). Thay thế các tham chiếu đến bước Source bằng sourceTable và thay thế tên khách hàng cụ thể bằng customerName. Cuối cùng, chỉnh sửa mã để hàm chỉ trả về giá trị tổng thay vì cả bảng.
Minh họa GIF về Excel
Sau khi hoàn thành, bạn có thể kiểm tra hàm bằng cách chọn truy vấn Data làm sourceTable và nhập tên khách hàng. Để sử dụng hàm trong truy vấn Data, tạo một Custom Column và sử dụng công thức =fnCustomerTotal(Source, [Customer]).
Kết Luận
Bài viết này đã hướng dẫn bạn cách tạo và sử dụng hàm tùy chỉnh trong Power Query, từ đơn giản đến phức tạp. Việc sử dụng hàm tùy chỉnh giúp tăng tính linh hoạt và hiệu quả trong việc phân tích dữ liệu, cho phép bạn thực hiện các phép tính và biến đổi phức tạp mà các hàm tiêu chuẩn không đáp ứng được. Một số ví dụ ứng dụng của hàm tùy chỉnh bao gồm tính tổng lũy tiến, tỷ lệ phần trăm tổng thể và lấy giá trị từ hàng trước hoặc sau.
Discussion about this post