[Power Query Tutorial] Cách tạo Custom Column trong Power BI

Custom Column là một trong những công cụ biến đổi dữ liệu quan trọng và linh hoạt nhất trong Power Query. Tính năng này cho phép bạn chủ động tạo thêm các trường dữ liệu mới dựa trên các biểu thức tính toán tự định nghĩa bằng ngôn ngữ M, vượt qua mọi giới hạn của các tính năng tạo cột mặc định.

Dù bạn chỉ cần gán một hằng số phân loại, nhân bản cột, thực hiện các phép tính số học cơ bản hay xây dựng các điều kiện rẽ nhánh phức tạp theo từng dòng dữ liệu, Custom Column đều đáp ứng hoàn hảo ngay tại bước tiền xử lý.

Bài viết này, Starttrain sẽ giúp bạn nắm vững bản chất của Custom Column, theo dõi từng bước thao tác thực tế qua hệ thống hình ảnh minh họa chi tiết, cùng những lưu ý chuyên sâu giúp tối ưu tài nguyên và tránh các lỗi thường gặp trong dự án.

Chức năng Custom Column là gì?

Custom Column là tính năng nằm trên tab Add Column của giao diện Power Query Editor. Chức năng này cho phép người dùng tính toán và chèn thêm một cột dữ liệu mới vào bảng hiện hành dựa trên biểu thức logic hoặc công thức toán học do chính người dùng thiết lập. Trong khi các công cụ tạo cột định sẵn như Conditional Column chỉ đáp ứng các cấu trúc rẽ nhánh cơ bản hay Index Column chỉ dùng để đánh số thứ tự dòng, Custom Column có thể tận dụng toàn bộ sức mạnh của ngôn ngữ truy vấn M (M-code).

Cơ chế tính toán theo ngữ cảnh từng dòng

Về bản chất vận hành, Custom Column hoạt động dựa trên ngữ cảnh từng dòng (row-by-row context). Khi bạn khai báo một công thức, Power Query sẽ duyệt qua từng bản ghi riêng lẻ và thực thi phép tính tương ứng cho dòng đó. Cơ chế tính toán độc lập này mang lại độ chính xác tuyệt đối khi cần kết hợp các giá trị từ nhiều trường dữ liệu trên cùng một hàng để tạo ra chỉ số mới.

Khả năng tùy biến và xử lý dữ liệu đa dạng

Khả năng tùy biến của Custom Column gần như không có giới hạn nhờ sự hỗ trợ toàn diện từ thư viện hàm M phong phú. Bạn có thể thực hiện từ các phép tính số học giản đơn như cộng, trừ, nhân, chia cho đến những thao tác xử lý chuỗi văn bản, chuyển đổi dữ liệu thời gian hay thiết lập các cấu trúc điều kiện, … Thậm chí đối với các mô hình phức tạp, Custom Column còn cho phép trích xuất và xử lý trực tiếp các cấu trúc dữ liệu lồng nhau như List, Record, Table, …

Minh họa chức năng Custom Column

Dưới đây là các thao tác thực tế và các tình huống ứng dụng phổ biến của Custom Column được chụp lại chi tiết từ giao diện Power Query Editor.

Mở giao diện Custom Column

Trong giao diện Power Query Editor đang mở bảng dữ liệu có tên Table (gồm 2 cột ban đầu là Column1 và Column2 thuộc kiểu số nguyên Int64). Người dùng chuyển sang tab Add Column trên thanh công cụ Ribbon, sau đó nhấp chọn biểu tượng Custom Column nằm trong nhóm chức năng General.

Hộp thoại thiết lập Custom Column xuất hiện gồm 3 khu vực chính:

• New column name: Tên cột mới muốn đặt (mặc định hiển thị chữ Custom).
• Custom column formula: Khung soạn thảo công thức (bắt đầu bằng dấu =).
• Available columns: Danh sách các cột hiện có (Column1, Column2) cùng nút << Insert để chèn nhanh tên cột vào công thức. Dưới góc trái có dòng kiểm tra cú pháp No syntax errors have been detected.

Tạo cột với giá trị hằng số cố định

Trong ô Custom column formula, người dùng nhập công thức hằng số: = 1.

Sau khi bấm OK, cột mới mang tên Custom được thêm vào phía sau cùng của bảng.

• Tất cả 5 dòng dữ liệu đều nhận giá trị là 1.
• Trên thanh Formula bar hiển thị mã M: = Table.AddColumn(#”Changed Type”, “Custom”, each 1).
• Khu vực Applied Steps bên phải tự động ghi nhận bước mới có tên Added Custom.

Sao chép giá trị từ một cột có sẵn

Trong khung công thức, người dùng chọn Column2 từ danh sách Available columns và bấm Insert, tạo thành cú pháp: = [Column2].

Kết quả trả về cột Custom có các giá trị lần lượt là 0, 7, 9, 6, 2, trả về chính xác giá trị của Column2 tương ứng trên từng dòng. Đoạn mã M được sinh ra là = Table.AddColumn(#”Changed Type”, “Custom”, each [Column2]).

Thực hiện các phép toán số học giữa các cột

Người dùng đổi tên cột tại mục New column name thành SUm. Tại ô công thức, nhập biểu thức tính tổng: = [Column1] + [Column2].

Cột SUm hiển thị kết quả cộng dồn theo từng dòng: dòng 1 (1 + 0 = 1), dòng 2 (5 + 7 = 12), dòng 3 (2 + 9 = 11), dòng 4 (7 + 6 = 13), dòng 5 (9 + 2 = 11). Thanh công thức hiển thị cú pháp: = Table.AddColumn(#”Changed Type”, “SUm”, each [Column1] + [Column2]).

Trường hợp khác, trong hộp thoại, giữ tên cột là Custom, viết công thức nhân: = [Column1] * [Column2].

Kết quả cột mới hiển thị tích số của từng cặp giá trị tương ứng: dòng 1 (1 * 0 = 0), dòng 2 (5 * 7 = 35), dòng 3 (2 * 9 = 18), dòng 4 (7 * 6 = 42), dòng 5 (9 * 2 = 18). Công thức M tương ứng: = Table.AddColumn(#”Changed Type”, “Custom”, each [Column1] * [Column2]).

Cần lưu ý gì khi sử dụng chức năng Custom Column?

Dù chức năng Custom Column rất tiện lợi, trong quá trình làm dự án thực tế bạn cần lưu ý các nguyên tắc sau để tránh lỗi phát sinh và duy trì hiệu năng xử lý cao:

Luôn khai báo lại kiểu dữ liệu

Nếu giữ nguyên kiểu Any, Power BI khi tải dữ liệu vào Data Model sẽ không thể tối ưu dung lượng nén cột, gây tiêu hao bộ nhớ RAM và dễ phát sinh lỗi khi viết công thức DAX sau này.

Do đó, ngay sau khi tạo Custom Column, hãy bấm trực tiếp vào biểu tượng góc trái tiêu đề cột hoặc chọn Data Type trên tab Transform để chuyển về các kiểu dữ liệu chuẩn.

Ngôn ngữ M có phân biệt chữ hoa, chữ thường

Khác với công thức Excel hoặc ngôn ngữ SQL, M-code phân biệt chính xác chữ hoa và chữ thường:

• Cú pháp lệnh điều kiện phải là chữ thường hoàn toàn: if … then … else (viết IF, If hay THEN đều sẽ báo lỗi cú pháp).
• Tên hàm hệ thống: Text.Upper hoàn toàn khác với text.upper hoặc Text.upper.
• Tên cột tham chiếu: [Column1] khác hoàn toàn với [column1].

Quy tắc lây lan giá trị rỗng (Null Propagation)

Trong ngôn ngữ M, nếu một trong các toán hạng tham gia phép tính chứa giá trị null, kết quả mặc định của toàn bộ biểu thức sẽ trả về null:

• Ví dụ: 10 + null = null (Power Query không tự động chuyển null thành 0 như Excel).
• Cách khắc phục: Trước khi tính toán, bạn nên dùng tính năng Replace Values để chuyển giá trị null về 0, hoặc viết biểu thức điều kiện bảo vệ: if [Column1] = null then 0 else [Column1]

Quy tắc đặt tên cột và tham chiếu

Tên cột trong biểu thức M luôn phải nằm trong cặp ngoặc vuông [Tên Cột]. Nếu tên cột chứa dấu cách hoặc ký tự đặc biệt, mã M sẽ tự sinh tiền tố thăng và dấu ngoặc kép: #”Tên Cột Có Khoảng Trắng”. Để hạn chế lỗi chính tả, hãy ưu tiên nhấp đúp vào danh sách Available columns hoặc dùng nút << Insert.

So sánh Custom Column (Power Query) và Calculated Column (DAX)

Một câu hỏi kinh điển trong cộng đồng người dùng Power BI là: Nên tạo cột bằng Custom Column trong Power Query hay tạo Calculated Column bằng DAX trong Data Model?

[FAQ] Một số câu hỏi thường gặp khi dùng chức năng Custom Column

Dưới đây là tổng hợp các thắc mắc phổ biến nhất của người dùng khi làm việc với Custom Column và giải pháp tương ứng:

Làm thế nào để chỉnh sửa lại công thức của Custom Column sau khi đã bấm OK?

Bạn không cần phải xóa cột để làm lại từ đầu. Tại vùng Applied Steps (nằm ở góc phải màn hình), bạn tìm đến bước Added Custom vừa tạo và nhấp đúp chuột vào bước này (hoặc bấm vào biểu tượng bánh răng cài đặt bên cạnh). Hộp thoại Custom Column sẽ hiển thị lại để bạn chỉnh sửa công thức. Ngoài ra, bạn cũng có thể chọn bước đó và sửa trực tiếp mã M trên thanh công thức.

Custom Column khác gì so với Conditional Column và Column From Examples?

Cả 3 công cụ đều nằm trên tab Add Column nhưng phục vụ các mục đích khác nhau:

• Conditional Column: Cung cấp giao diện hộp thoại đồ họa để tạo điều kiện logic dạng nếu – thì (if/else) mà không cần viết code, nhưng chỉ hỗ trợ các phép so sánh cơ bản.
• Column From Examples: Cho phép bạn gõ kết quả mẫu mong muốn và hệ thống tự động suy luận ra công thức M tương ứng. Phù hợp cho việc tách họ tên, nối chuỗi định dạng nhanh.
• Custom Column: Khung soạn thảo tự do cho phép bạn viết trực tiếp bất kỳ câu lệnh hoặc hàm nào của ngôn ngữ M, phù hợp cho mọi biểu thức tính toán từ đơn giản đến phức tạp nhất.

Có thể viết câu lệnh điều kiện if-else nhiều tầng trong Custom Column được không?

Hoàn toàn được. Cú pháp chuẩn của ngôn ngữ M là: if [Dieu_Kien_1] then [Gia_Tri_1] else if [Dieu_Kien_2] then [Gia_Tri_2] else [Gia_Tri_Mac_Dinh] Lưu ý quan trọng: Toàn bộ các từ khóa if, then, else bắt buộc phải viết chữ thường.

Lỗi “The column ‘[Tên Cột]’ of the table wasn’t found” phát sinh do đâu?

Lỗi này thường do 2 nguyên nhân:

• Viết sai chính tả hoặc sai quy tắc viết hoa/thường của tên cột (ví dụ trong bảng là [Column1] nhưng công thức lại gõ [column1]).
• Cột tham chiếu đã bị đổi tên, xóa hoặc gộp ở một bước biến đổi trước đó trong Applied Steps, khiến Power Query không tìm thấy trường dữ liệu tương ứng ở thời điểm thực thi.

Tổng kết

Custom Column là công cụ chuyển đổi dữ liệu không thể thiếu đối với bất kỳ ai làm việc với Power Query trong Power BI hoặc Excel. Việc làm chủ Custom Column cho phép bạn vượt qua các ranh giới của những công cụ tự động mặc định, tự do kiến tạo các chỉ số tính toán và chuẩn hóa bảng dữ liệu ngay từ tầng ETL.