[Power Query Tutorial] Cách sử dụng Replace trong Power BI

Chức năng replace trong Power BI là công cụ làm sạch dữ liệu giúp bạn tự động tìm kiếm và thay thế các chuỗi văn bản, ký tự dư thừa, giá trị null hay các ô bị lỗi. Khác với tính năng Find & Replace thông thường trong Excel, mọi thao tác replace trong Power BI đều được ghi nhận thành bước xử lý (Applied Step) và tự động lặp lại mỗi khi dữ liệu nguồn được làm mới (refresh).

Bài viết này, Starttrain sẽ hướng dẫn chi tiết cách thực thi replace values, replace errors qua bài tập thực tế và giải mã toàn bộ cấu trúc mã M đằng sau công cụ này.

Chức năng replace trong Power BI

Trong giao diện Power Query Editor, bạn có thể dễ dàng kích hoạt công cụ này tại thanh Ribbon ở cả hai thẻ Home và Transform, nằm trong nhóm lệnh Any Column. Chức năng replace được chia thành hai tác vụ chính là Replace Values và Replace Errors, đáp ứng các mục đích xử lý dữ liệu khác nhau.

Replace Values

Chức năng Replace Values cho phép bạn tìm kiếm và thay thế một chuỗi văn bản, con số, ngày tháng hoặc giá trị null cụ thể trên các cột được lựa chọn. Tính năng này thường được ứng dụng để chuẩn hóa dữ liệu không đồng nhất, xóa bỏ ký tự thừa do nhập liệu sai, hoặc điền số 0 và văn bản mặc định vào các ô trống.

Ngoài ra, người dùng có thể mở rộng tùy chọn Advanced options và tích chọn Match entire cell contents để bắt buộc Power Query chỉ thay thế khi toàn bộ nội dung trong ô trùng khớp 100% với từ khóa tìm kiếm, tránh việc thay thế nhầm các chuỗi con chứa ký tự tương tự.

Replace Errors

Khác với Replace Values, Replace Errors là công cụ chuyên biệt dùng để xử lý các ô đang hiển thị thông báo lỗi. Nguyên nhân phổ biến nhất gây ra lỗi này là do ép sai kiểu dữ liệu khi chuyển một cột chứa chữ sang dạng số hoặc do các phép toán không hợp lệ như chia cho 0.

Trong mô hình báo cáo, nếu một cột chứa ô Error thì mọi phép tính tổng hợp như SUM, AVERAGE trên Power Query hay ngôn ngữ DAX trong Power BI đều sẽ trả về kết quả lỗi trên toàn bộ hệ thống. Việc áp dụng Replace Errors để chuyển các ô bị lỗi về số 0, về null hoặc câu cảnh báo tùy chỉnh sẽ đảm bảo quy trình tự động hóa dữ liệu không bị ngắt quãng và báo cáo vận hành ổn định.

Ví dụ cụ thể chức năng replace trong Power BI

Nhận diện dữ liệu lỗi cần làm sạch

Giả sử bạn có bảng dữ liệu tên Data – 2 đang gặp phải hai vấn đề phổ biến:

• Cột NGÀY LÊN HÀNG XONG bị lẫn lộn giữa chuỗi ngày tháng đúng chuẩn và ký tự rác (chữ “V” đứng một mình).
• Cột NGÀY XUỐNG HÀNG XONG khi được chuyển sang kiểu số (Int64.Type) đã xuất hiện dòng chữ Error màu đỏ ở những ô trước đó chứa văn bản.

Để khắc phục, bạn sẽ kết hợp cả hai lệnh thay thế có sẵn trong nhóm Any Column (nằm ở thẻ Home hoặc Transform trên thanh Ribbon).

Dùng Replace Values loại bỏ ký tự thừa

Đầu tiên, bạn xử lý cột NGÀY LÊN HÀNG XONG bằng cách làm theo các bước sau:

• Nhấp chọn cột NGÀY LÊN HÀNG XONG.
• Nhấn vào nút Replace Values trên thanh công cụ.

• Trong bảng hiện ra, nhập ký tự “V” vào ô Value To Find (giá trị cần tìm).
• Nhập từ “null” vào ô Replace With (giá trị thay thế) để chuyển ký tự dư thừa này thành ô rỗng.
• Nhấn OK để áp dụng.

Mở tính năng Replace Errors xử lý ô lỗi

Ngay sau bước 2, các chữ “V” đã biến mất và trở thành ô rỗng (null). Lúc này, bạn tiếp tục chuyển sang làm sạch cột NGÀY XUỐNG HÀNG XONG:

• Nhấp chọn cột NGÀY XUỐNG HÀNG XONG.
• Tìm đến nút Replace Values trên thanh công cụ, bấm vào mũi tên nhỏ bên cạnh để mở danh sách tùy chọn.
• Chọn dòng Replace Errors.

• Hộp thoại Replace Errors xuất hiện và yêu cầu bạn nhập giá trị sẽ thay thế cho các ô chứa lỗi
• Tại ô Value, nhập số 0 (hoặc giá trị phù hợp với báo cáo của bạn).
• Nhấn nút OK để xác nhận.

Thao tác này giúp chuyển toàn bộ các dòng đang bị thông báo Error về số 0 an toàn.

Kết quả trả về

Sau khi hoàn thành, bạn sẽ thấy kết quả thay đổi rõ rệt trên bảng dữ liệu:

• Cột NGÀY XUỐNG HÀNG XONG không còn ô Error nào, thanh chất lượng dữ liệu hiển thị xanh lá cây (100% Valid).
• Ở khung Applied Steps bên phải màn hình, hai bước xử lý Replaced Value và Replaced Errors đã được lưu lại tự động.
• Mọi phép tính tổng hợp trên cột này từ giờ sẽ chạy mượt mà và không còn lo bị ngắt quãng.

Mã M đằng sau chức năng Replace trong Power BI

Hàm thay thế giá trị: Table.ReplaceValue

Cú pháp chuẩn trong mã M:Table.ReplaceValue( table as table, oldValue as any, newValue as any, replacer as function, columnsToReplace as list ) as table

Giải thích các tham số:

• table: Bảng dữ liệu đầu vào (ví dụ: #”Changed Type1″).
• oldValue: Giá trị cần tìm để thay thế (ví dụ: “V”).
• newValue: Giá trị mới sẽ thay thế vào (ví dụ: null).
• replacer: Hàm thực thi việc thay thế. Thường dùng Replacer.ReplaceValue (thay thế toàn bộ ô) hoặc Replacer.ReplaceText (thay thế một phần chuỗi văn bản).
• columnsToReplace: Danh sách các cột áp dụng thay thế (đặt trong dấu ngoặc nhọn, ví dụ: {“NGÀY LÊN HÀNG XONG”}).

Ví dụ thực tế từ bài học: = Table.ReplaceValue(#”Changed Type1″, “V”, null, Replacer.ReplaceValue, {“NGÀY LÊN HÀNG XONG”})

Hàm thay thế lỗi: Table.ReplaceErrorValues

Cú pháp chuẩn trong mã M:Table.ReplaceErrorValues( table as table, errorValuePairs as list ) as table

Giải thích các tham số:

• table: Bảng dữ liệu đầu vào chứa các ô lỗi.
• errorValuePairs: Danh sách các cặp dạng {“Tên_Cột”, Giá_Trị_Thay_Thế}.

Ví dụ thực tế từ bài học: = Table.ReplaceErrorValues(#”Replaced Value”, {{“NGÀY XUỐNG HÀNG XONG”, 0}})

Những lưu ý khi sử dụng chức năng Replace trong Power BI

Phân biệt chữ hoa và chữ thường

Ngôn ngữ M và công cụ Replace trong Power BI tuyệt đối phân biệt chữ hoa và chữ thường. Điều này có nghĩa là “v” thường và “V” hoa được hệ thống hiểu là hai giá trị hoàn toàn khác nhau. Nếu dữ liệu của bạn chứa cả “hcm”, “Hcm” và “HCM”, việc chỉ nhập tìm kiếm “hcm” sẽ bỏ sót hai dạng còn lại.Giải pháp: Trước khi thực hiện Replace Values, bạn nên chuẩn hóa toàn bộ cột về cùng một dạng chữ hoa (Uppercase) hoặc chữ thường (Lowercase) bằng tính năng Transform -> Format -> Lowercase/Uppercase.

Phân biệt thay thế toàn bộ ô và thay thế một phần chuỗi

Trong cửa sổ Replace Values, khi mở mục Advanced options, bạn sẽ thấy tùy chọn Match entire cell contents (Khớp toàn bộ nội dung ô).

• Mặc định (Không tích chọn): Power Query sẽ tìm và thay thế cụm từ đó ở bất kỳ vị trí nào trong ô. Ví dụ: Nếu bạn thay thế từ “Nam” thành “Nữ”, ô chứa chữ “Việt Nam” sẽ bị đổi thành “Việt Nữ”.
• Khi tích chọn Match entire cell contents: Power Query chỉ thay thế khi toàn bộ nội dung ô trùng khớp 100% với từ khóa tìm kiếm. Trong ví dụ trên, ô “Việt Nam” sẽ được giữ nguyên, chỉ những ô chỉ chứa duy nhất chữ “Nam” mới bị thay đổi.

Loại bỏ khoảng trắng ẩn trước khi Replace

Khoảng trắng dư thừa ở đầu hoặc cuối chuỗi văn bản là nguyên nhân phổ biến nhất khiến lệnh Replace Values không hoạt động như mong đợi.

• Rủi ro: Ô dữ liệu hiển thị là “HCM ” (có khoảng trắng ở cuối) sẽ không được tìm thấy nếu bạn chỉ nhập từ khóa “HCM” trong ô Value To Find.
• Giải pháp: Luôn thực hiện thao tác Trim (Transform -> Format -> Trim) trên cột văn bản trước khi thực hiện Replace để đảm bảo mọi khoảng trắng thừa đã được xóa sạch.

Tổng kết

Chức năng Replace trong Power BI không chỉ đơn thuần là công cụ tìm kiếm và thay thế chuỗi văn bản hay giá trị số thông thường, mà còn là mắt xích quan trọng giúp tự động hóa toàn bộ quy trình làm sạch dữ liệu. Việc nắm vững cách phân biệt giữa Replace Values và Replace Errors, hiểu rõ cơ chế phân biệt chữ hoa/chữ thường, cũng như thứ tự ưu tiên khi ép kiểu dữ liệu sẽ giúp bạn xây dựng các báo cáo Power BI hay Excel chuẩn xác, tối ưu hiệu năng và vận hành ổn định lâu dài.

Hãy áp dụng ngay các kỹ thuật này vào công việc xử lý dữ liệu hàng ngày để tiết kiệm thời gian và nâng cao chất lượng phân tích của bạn.