Power Query đến nay đã sở hữu hơn 700 function, và team phát triển Power Query vẫn tiếp tục bổ sung ngày càng nhiều hơn. Bạn không cần ghi nhớ tất cả chúng, nhưng bạn cần phải hiểu khái niệm về các tham số và đối số để hiểu các hàm làm gì. Trong bài viết này, KPIM sẽ giới thiệu các hàm tùy chỉnh là gì và cách bạn có thể tạo Functions (hàm).
M Functions
Bạn có thể lấy danh sách tất cả các biến môi trường (environment variables) trong Power Query / Power BI của mình bằng cách sử dụng code có tên =shared trong thanh công thức của bạn như sau:
Tuy nhiên, ngay cả khi công cụ có thể có hơn 700 function, ta có thể vẫn cần tạo chức năng tùy chỉnh của riêng mình để giải quyết các tác vụ lặp đi lặp lại.
Thay vì phải viết đi viết lại một đoạn code, việc gắn đoạn code đó một cái tên function nhất định để sẽ giúp gia tăng tính linh hoạt và tiết kiệm thời gian một cách đáng kinh ngạc. Một tác vụ lặp đi lặp lại sẽ không chỉ áp dụng cho một truy vấn duy nhất, mà cho nhiều truy vấn hoặc các đối số khác nhau.
Nguyên tắc hoạt động của function tùy chỉnh cũng tương tự như việc tạo ra các Measure trong DAX, để từ đó không phải define SUM (Sales [Sales]) mỗi khi sử dụng CALCULATE; ta chỉ cần gọi tên measure đó - ở đây gọi là [Sale Amount].
Nguyên tắc nêu trên cũng tương tự như với Excel khi ta tạo UDF với VBA - một tác vụ lặp đi lặp lại có thể được đơn giản hóa khi ta define một hàm function đơn giản.
Sự khác biệt giữa chúng: kết quả thu được khi sử dụng function (hàm) trong Power Query sẽ không chỉ giới hạn ở một đơn vị giá trị duy nhất. Ta có thể thu được một bảng, một danh sách, một bản ghi record, nhiều giá trị, hoặc kết hợp mọi thứ nêu trên,...
Tình huống: Các tệp file có cùng định dạng, nhưng giá trị khác nhau
Hãy tưởng tượng rằng mỗi tháng chúng ta cần xuất một tệp có dữ liệu của tháng trước ra khỏi hệ thống. Tất cả các tệp sẽ có cùng cấu trúc và định dạng, nhưng giá trị bên trong các tệp đó sẽ khác nhau, do bản chất giá trị mỗi tháng đêu khác nhau.
Ở đây, tôi đã tải xuống các tệp cho tháng 1, tháng 2 và tháng 3:
Những gì tôi muốn làm ở đây là tạo một function (hàm) mà sau này tôi có thể áp dụng cho bất kỳ tệp nào mà tôi muốn tại bất kỳ thời điểm nào. Thậm chí tại một thời điểm nhất định, tôi có thể xuất hệ thống của mình theo dạng cả năm thay vì chỉ dữ liệu theo tháng.
Ở đây, ta cần tạo một hàm function với chỉ 1 parameter (tham số). Parameter đó phải là tệp thực tế mà ta muốn chuyển đổi.
Custom Function (hàm tùy chỉnh) trong Power Query / Power BI
Có 2 cách để tạo một hàm M (M function):
- Cách thủ công - bạn cần phải vào Advanced Editor theo cách thủ công để chuyển đổi một truy vấn thành một hàm.
- Cách được hỗ trợ - trong đó Power Query cung cấp cho bạn một framework để tạo các hàm function thay vì phải xử lý bằng M.
Trong bài này, ta sẽ xem xét cách thức tạo hàm thủ công, thông qua Advanced Editor của Power Query / Power BI.
Trước khi đi sâu vào nghiên cứu, hãy xác định phạm vi hàm của chúng ta sẽ làm gì và giá trị nó đem lại cho chúng ta.
Một trong những tệp bạn đã tải xuống là tệp Excel và trong tệp đó, bạn sẽ có một truy vấn kết nối với tệp 01-January.csv.
Lưu ý: bạn có thể nhập truy vấn đó vào Power BI Desktop thông qua menu Import from the File.
Điều đầu tiên cần làm là chỉnh sửa truy vấn đó và thay đổi đường dẫn tệp thành đường dẫn trong máy tính của bạn. Chỉ cần nhấp vào biểu tượng bánh răng bên cạnh bước nguồn và trỏ đến tệp của bạn.
Sau khi hoàn tất, truy vấn của bạn sẽ tải một cách hoàn chỉnh:
Tóm lại, đây là các bước đã được thực hiện cho truy vấn đó:
- Source - cách chúng ta kết nối với tệp csv
- Promoted Headers – đặt hàng đầu tiên thành tiêu đề cột
- Replaced Value - chúng ta thay thế các giá trị trống bằng các giá trị null trong cột tháng
- Filled Down - chúng tôi đã sử dụng thao tác điền vào cột tháng
- Unpivoted Other Columns - unpivot tất cả các cột ngoại trừ tháng và tên sản phẩm
- Renamed Columns - cột thuộc tính đã được đổi tên thành thị trường và giá trị được đổi tên thành Số lượng
- Changed Type - đặt các kiểu dữ liệu cho mỗi cột
Sau khi đã chuẩn bị xong truy vấn, ta cần chuyển truy vấn đó thành một hàm function, và gán một tham số (parameter) cho hàm đó.
Tạo một function (hàm) theo cách thủ công
Để giữ mọi thứ có tổ chức, hãy sao chép truy vấn đó và đặt tên là "ManualFunction". Chỉ cần nhấp chuột phải vào truy vấn và bạn sẽ nhận được tùy chọn để sao chép truy vấn:
Sau khi hoàn tất, chúng ta cần khởi chạy trình chỉnh sửa nâng cao cho truy vấn mới đó:
Vào Menu View (1), nhấp vào nút Advanced Editor (2), và sau đó chúng ta sẽ thấy Cửa sổ Advanced Editor (3). Tôi đã đánh dấu phần mã mà chúng ta cần chuyển đổi thành một tham số, về cơ bản là mã nhị phân của tệp:
Và sau đó chúng ta phải thay đổi code theo cách thủ công để nó trông như thế này:
Và khi chúng ta nhấn Xong, nó sẽ được hiển thị với biểu tượng hàm trên ngăn truy vấn vì nó bây giờ là một hàm tùy chỉnh với 1 tham số (nhị phân) xuất ra một bảng (dưới dạng bảng).
Chú ý sự khác biệt giữa 2 hình ảnh. Trước let, tôi đã thêm các thành phần chức năng sau cho function:
(FileBinary as binary) as table =>
Và mọi thứ khác vẫn giữ nguyên cho phần đó mà tôi đã đánh dấu trong hình ảnh đầu tiên, bây giờ là một tham số hàm (function parameter).
Kiểm tra function (hàm) đã tạo
Với function đã tạo ra, bây giờ ta sẽ cần kiểm thử nó. Phương pháp tiến hành kiểm thử ở đây là kết nối với thư mục mà chúng ta đã tải xuống - thư mục chứa tất cả các tệp mẫu. Bạn có thể làm điều đó trong Power Query Editor:
Sau đó, nhấp vào Edit (Chỉnh sửa) trên cửa sổ sẽ bật lên để chuyển nó thành một truy vấn mới bên trong Power Query Editor.
Gọi hàm Functions của bạn khởi các tệp bên trong thư mục đó.
Đi tới menu Add Column (Thêm Cột) (1), nhấp vào nút Invoke Custom Function (Gọi Chức năng Tùy chỉnh) (2), và sau đó điền vào các trường theo hình dưới đây (3).
Bạn sẽ nhận thấy rằng một số ô đang phát sinh lỗi - do một số tệp trong đây không phải theo định dạng .csv. Ở đây, ta có hai lựa chọn:
- Lọc bỏ các ô có dữ liệu không phải định dạng .csv ra (sử dụng bộ lọc filter, áp dụng chỉ lấy extension đuôi .csv) trước khi gọi Custom Function
- Xóa các ô đó một cách thủ công
Tôi khuyên bạn nên chọn tùy chọn đầu tiên là lọc chúng trước khi gọi hàm. Sau khi tôi loại bỏ một số cột không cần sử dụng, đây là những gì tôi có:
Nhấp vào biểu tượng mũi tên kép đối diện (còn được gọi là biểu tượng Mở rộng) để gộp tất cả dữ liệu vào chỉ một bản - kết nối chúng lại:
Và như hình dưới đây, mọi dữ liệu đều đã được điền vào chung một bảng:
Tổng kết
Trong Power BI, Parameters và Functions đóng vai trò quan trọng trong việc tạo và quản lý các báo cáo và trực quan hóa dữ liệu. Parameters (tham số) cho phép người dùng đưa ra các giá trị đầu vào có thể thay đổi để điều chỉnh các tính toán và lọc dữ liệu trong báo cáo. Functions (hàm) cung cấp cách để tổ chức và thực hiện các phép tính phức tạp và tích hợp các thao tác xử lý dữ liệu trong Power BI.
Functions trong Power BI cho phép người dùng tạo các biểu thức tính toán phức tạp và thực hiện các xử lý dữ liệu. Power BI cung cấp một loạt các hàm tích hợp sẵn để thực hiện các phép tính như SUM, AVERAGE, MAX, MIN, và nhiều hơn nữa.
Bạn cũng có thể tạo các Custom Functions (hàm tùy chỉnh) của riêng mình bằng cách sử dụng ngôn ngữ DAX (Data Analysis Expressions). Hàm tùy chỉnh cho phép bạn thực hiện tính toán phức tạp, lọc dữ liệu, kết hợp các bảng và thực hiện các phép tính logic.
Tóm lại, Parameters và Functions là hai khái niệm quan trọng trong Power BI, cho phép người dùng điều chỉnh giá trị và thực hiện các tính toán phức tạp trong báo cáo. Bằng cách sử dụng chúng, người dùng có thể tạo ra các báo cáo linh hoạt và dễ dàng cập nhật để hiển thị thông tin dựa trên các tham sốvà hàm tính toán. Hy vọng những thông tin chia sẻ ở trên của KPIM đã giúp các bạn hiểu rõ hơn về Parameters và Functions.
