[Power Query Tutorial] Cách sử dụng Remove Rows

Trong kỷ nguyên phân tích dữ liệu, việc sở hữu một bộ dữ liệu sạch là yếu tố quyết định 80% sự thành công của một báo cáo Power BI hay Excel. Một trong những nhóm tính năng quan trọng và được sử dụng thường xuyên nhất để tinh lọc dữ liệu thô chính là Remove Rows trong Power Query.

Bài viết này, Starttrain sẽ cung cấp cho bạn cái nhìn toàn diện, chuyên sâu về cách vận hành, các trường hợp ứng dụng thực tế của nhóm chức năng Remove Rows, kèm theo phân tích ảnh chụp thực tế và hướng dẫn viết mã code M tối ưu nhất.

Tại sao việc làm sạch dữ liệu bằng Remove Rows lại quan trọng?

Trong quy trình phân tích dữ liệu chuyên nghiệp, bước ETL (Extract – Transform – Load) luôn chiếm phần lớn thời gian của một kỹ sư dữ liệu. Khi kết nối Power Query với các nguồn dữ liệu thô chưa qua xử lý như file CSV, trang tính Excel từ phòng kế toán, hệ thống ERP (như SAP, Oracle) hay cơ sở dữ liệu SQL Server, dữ liệu nhận về hiếm khi ở trạng thái lý tưởng. Bạn sẽ liên tục đối mặt với rác dữ liệu bao gồm:

• Dòng tiêu đề phụ và thông tin nhiễu (Sub-headers & Metadata): Các file báo cáo xuất ra từ phần mềm kế toán thường đi kèm 3 đến 5 dòng đầu ghi tên công ty, tiêu đề báo cáo, ngày xuất tệp, hoặc các dòng trống ngăn cách để tạo khoảng thở thị giác. Ngoài ra, cuối bảng thường xuất hiện các dòng tổng cộng (Grand Total) hoặc ghi chú chân trang (Footer). Nếu không loại bỏ các dòng này, Power Query sẽ nhận diện chúng là các bản ghi dữ liệu thực tế, làm sai lệch nghiêm trọng các phép tính toán trung bình hoặc phân bổ dữ liệu sau này.
• Dữ liệu trống (Blank Rows) vô nghĩa: Các dòng trống xuất hiện do người dùng định dạng dư thừa trong Excel (như nhấp chuột kéo dòng hoặc định dạng bảng xuống vô hạn) hoặc do hệ thống xuất file bị lỗi dòng trống xen kẽ. Mặc dù không chứa giá trị, các dòng trống này vẫn ngốn tài nguyên hệ thống vì Power Query vẫn phải nạp và xử lý các bản ghi chứa toàn giá trị null, làm tăng thời gian tải (Refresh time) một cách không cần thiết.
• Bản ghi trùng lặp (Duplicate Rows): Đây là một trong những nguyên nhân phổ biến nhất dẫn đến sai số trong báo cáo tài chính và kinh doanh. Trùng lặp có thể xảy ra do lỗi đồng bộ hệ thống, nhân viên nhập liệu ấn lưu hai lần, hoặc gộp chung nhiều nguồn dữ liệu mà không khử trùng. Việc giữ lại các dòng trùng lặp sẽ phá vỡ mối quan hệ 1 – Nhiều trong Data Model, gây ra lỗi tham chiếu chéo hoặc lỗi nghiêm trọng khi liên kết bảng Dim và bảng Fact, đồng thời khiến các chỉ số doanh thu, chi phí bị nhân đôi, nhân ba so với thực tế.
• Dữ liệu lỗi phát sinh từ nguồn (Error Rows): Các ô chứa lỗi tính toán kế thừa từ công thức Excel như #N/A, #DIV/0!, #VALUE! hoặc lỗi định dạng (ví dụ: cột Số lượng lại chứa ký tự chữ “Không có”). Khi nạp vào Power Query và cố gắng chuyển đổi kiểu dữ liệu (Change Type) sang số hoặc ngày tháng, hệ thống sẽ báo lỗi (Error). Nếu không loại bỏ kịp thời, các dòng lỗi này sẽ ngăn chặn quá trình tải dữ liệu lên Power BI Service, làm sập lịch cập nhật tự động (Scheduled Refresh) và làm hỏng toàn bộ báo cáo của doanh nghiệp.

Chức năng Remove Rows trong Power Query

Chức năng Remove Rows nằm trong tab Home, thuộc nhóm công cụ Reduce Rows trên thanh Ribbon của Power Query Editor. Chức năng này cho phép bạn loại bỏ các dòng không mong muốn theo các quy tắc thiết lập sẵn.

Dưới đây là giải thích chi tiết, cách hoạt động và trường hợp ứng dụng cụ thể của cả 6 tùy chọn có trong menu Remove Rows:

Remove Top Rows

• Cách hoạt động: Cho phép người dùng nhập vào một số nguyên dương “N”. Power Query sẽ đếm từ dòng số 1 (dòng đầu tiên) xuống đến dòng thứ N và xóa bỏ hoàn toàn N dòng này, giữ lại toàn bộ các dòng từ N + 1 trở đi.
• Ứng dụng: Thường dùng khi các file dữ liệu thô xuất ra từ phần mềm có chứa các dòng chú thích, thông tin tác giả, ngày xuất báo cáo hoặc các khoảng trống ở 2-3 dòng đầu tiên trước khi đến bảng dữ liệu chính thức.

Remove Bottom Rows

• Cách hoạt động: Tương tự như dòng trên cùng nhưng đếm ngược từ dưới lên. Bạn nhập vào số lượng dòng “N”, Power Query sẽ định vị dòng cuối cùng của bảng và xóa ngược lên trên đủ N dòng.
• Ứng dụng: Loại bỏ phần tổng kết cuối trang, chữ ký người lập biểu, ngày phê duyệt hoặc các ghi chú hệ thống nằm ở cuối file dữ liệu.

Remove Alternate Rows

• Cách hoạt động: Đây là tính năng xóa theo quy luật lặp lại cấu trúc. Chức năng yêu cầu bạn nhập 3 thông số đầu vào:
  • First row to remove (Dòng đầu tiên muốn xóa): Vị trí dòng bắt đầu áp dụng quy luật.
  • Number of rows to remove (Số dòng cần xóa): Số lượng dòng sẽ bị xóa đi trong mỗi chu kỳ.
  • Number of rows to keep (Số dòng cần giữ lại): Số lượng dòng sẽ được giữ lại trước khi bắt đầu chu kỳ xóa tiếp theo.
• Ứng dụng: Thường áp dụng cho các báo cáo được xuất dưới dạng định dạng lưới phức tạp, nơi cứ 1 dòng chứa dữ liệu thật thì lại có 1 dòng chứa dữ liệu trống hoặc dòng chú giải thừa.

Remove Duplicates

• Cách hoạt động:
  • Nếu bạn đang chọn một hoặc nhiều cột cụ thể: Power Query sẽ quét các cột đó, tìm các giá trị trùng nhau và chỉ giữ lại dòng xuất hiện đầu tiên (First-in-keep), loại bỏ các dòng xuất hiện sau.
  • Nếu bạn chọn toàn bộ bảng: Chỉ những dòng nào giống hệt nhau 100% trên tất cả các cột mới bị coi là trùng lặp và bị xóa.
• Ứng dụng: Tạo danh mục duy nhất (Dimension Table) như Danh sách khách hàng không trùng lặp từ bảng dữ liệu giao dịch (Fact Table).

Remove Blank Rows

• Cách hoạt động: Power Query sẽ quét qua tất cả các dòng của bảng. Nếu một dòng nào đó hoàn toàn trống (tất cả các ô trên dòng đó đều chứa giá trị null), dòng đó sẽ bị xóa bỏ ngay lập tức.
• Ứng dụng: Làm sạch các file Excel định dạng thô có chứa các dòng trống xen kẽ vô nghĩa, giúp tối ưu hóa dung lượng lưu trữ của tệp.

Remove Errors

• Cách hoạt động: Khi Power Query đọc dữ liệu và phát hiện các giá trị bị lỗi hệ thống (hiển thị chữ Error màu đỏ trong ô dữ liệu), tính năng này sẽ xóa toàn bộ dòng chứa ô lỗi đó. Bạn có thể chọn áp dụng trên một cột cụ thể hoặc trên toàn bộ bảng dữ liệu.
• Ứng dụng: Loại bỏ các lỗi phát sinh do quá trình đổi kiểu dữ liệu thất bại.

Minh họa các chức năng có trong Remove Rows

Để giúp bạn hiểu rõ hơn về cách các chức năng này hoạt động trên giao diện thực tế của Power Query Editor, chúng ta sẽ cùng phân tích chi tiết chuỗi 10 hình ảnh thao tác thực tế trên bảng dữ liệu giao dịch có tên là FactTransactions.

Giao diện tổng quan và Menu tùy chọn

Giao diện cho thấy nút lệnh Remove Rows nằm tại tab Home, khu vực Reduce Rows. Khi người dùng nhấp chuột vào biểu tượng này, một menu thả xuống xuất hiện bao gồm đầy đủ 6 tùy chọn chính xác như đã mô tả: Remove Top Rows, Remove Bottom Rows, Remove Alternate Rows, Remove Duplicates, Remove Blank Rows và Remove Errors.

Cấu trúc bảng dữ liệu FactTransactions: Bảng có 18 cột (hiển thị ở thanh trạng thái dưới cùng bên trái: “18 COLUMNS, 999+ ROWS”). Các cột chính hiển thị bao gồm: card_id, client_id, amount (kiểu số Decimal), date (kiểu ngày giờ datetime), errors (chứa các giá trị null), mcc, và merchant_city (kiểu văn bản text). Ở bảng này, các bước biến đổi dữ liệu (Applied Steps) hiện tại dừng ở bước “Changed Type”.

Chức năng Remove Top Rows

Khi nhấp chọn Remove Top Rows, một hộp thoại nhỏ có tiêu đề “Remove Top Rows” xuất hiện giữa màn hình. Giao diện yêu cầu nhập thông số tại mục “Number of rows”.

Trong hình, người dùng đã nhập số 2 vào ô này. Bảng dữ liệu phía sau cho thấy dòng số 1 đang có giá trị cột amount là -62 và dòng số 2 có giá trị amount là 1.75.

Kết quả: Sau khi nhấn OK, một bước mới có tên Removed Top Rows được ghi nhận vào danh sách “Applied Steps” ở bảng điều khiển bên phải. Hãy chú ý vào thanh công thức (Formula Bar), mã M-code tự động sinh ra là:

= Table.Skip(#”Changed Type”,2)

Lúc này, hai dòng đầu tiên cũ (chứa giá trị -62 và 1.75) đã biến mất hoàn toàn. Dòng số 1 mới bây giờ bắt đầu bằng giá trị amount là 2.41 (trước đó là dòng số 3).

Chức năng Remove Bottom Rows

Người dùng mở menu thả xuống và chọn Remove Bottom Rows. Hộp thoại “Remove Bottom Rows” yêu cầu nhập số lượng dòng muốn xóa từ dưới đáy lên. Ở đây, con số được nhập vào là 100.

Kết quả: Sau khi xác nhận, bước Removed Bottom Rows được thêm vào “Applied Steps”. Thanh công thức hiển thị mã code M như sau:

= Table.RemoveLastN(#”Changed Type”,100) Hệ thống đã tự động cắt bỏ đúng 100 dòng dữ liệu cuối cùng của bảng FactTransactions.

Chức năng Remove Alternate Rows

Khi chọn tùy chọn này, hộp thoại “Remove Alternate Rows” hiện lên với 3 trường thông tin trống. Người dùng đã thiết lập cấu hình:

• “First row to remove”: 1 (bắt đầu xóa từ dòng đầu tiên).
• “Number of rows to remove”: 1 (xóa đúng 1 dòng).
• “Number of rows to keep”: 1 (giữ lại 1 dòng tiếp theo). Quy luật này có nghĩa là: Xóa dòng 1, giữ dòng 2, xóa dòng 3, giữ dòng 4, xóa dòng 5, giữ dòng 6… lặp lại liên tục cho đến hết bảng.

Chức năng Remove Duplicates

Đây là một thao tác cực kỳ quan trọng đòi hỏi người dùng phải chọn cột làm mốc trước khi nhấn lệnh. Cột client_id đã được nhấp chọn. Sau đó, người dùng chọn lệnh Remove Duplicates từ menu.

Kết quả: Bước mới có tên Removed Duplicates xuất hiện.

Công thức M-code thực thi là: = Table.Distinct(#”Changed Type”, {“client_id”})

Quan sát kỹ bảng kết quả, toàn bộ các dòng trùng lặp ID khách hàng ở cột client_id đã được loại bỏ hoàn toàn, chỉ giữ lại những mã ID duy nhất (Distinct). Ví dụ, các dòng tiếp theo sau ID 115 đều đã bị xóa nếu chúng có cùng mã client_id này, khiến số lượng dòng của bảng giảm đi đáng kể.

Chức năng Remove Rows trong code M

Nếu bạn là một nhà phát triển dữ liệu chuyên nghiệp, việc hiểu và làm chủ mã nguồn M-code của Power Query sẽ giúp bạn tùy biến các bước biến đổi dữ liệu linh hoạt hơn mà không cần phụ thuộc hoàn toàn vào giao diện kéo thả.

Dưới đây là bảng tổng hợp các hàm M tương ứng với từng chức năng trong menu Remove Rows:

Tổng kết

Việc nắm vững bộ công cụ Remove Rows trong Power Query là mảnh ghép không thể thiếu giúp bạn xây dựng những mô hình dữ liệu chuẩn mực và tối ưu hiệu suất tốt nhất. Bằng cách chủ động loại bỏ các dòng trống nhiễu, khử trùng các bản ghi trùng lặp nguy hiểm hoặc xử lý triệt để các dòng lỗi hệ thống ngay từ bước đầu, bạn đang trực tiếp bảo vệ tính chính xác cho các phép toán DAX và tăng tốc độ tải của báo cáo lên đáng kể.

Nếu muốn thực hành chuyên sâu hơn với Power Query, Data Model và DAX, bạn có thể tham khảo khóa học Power BI để xây dựng quy trình xử lý dữ liệu hoàn chỉnh.