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

Trong quá trình chuẩn bị dữ liệu bằng Excel hoặc Power BI, lọc và giữ lại những hàng dữ liệu cần thiết là bước căn bản nhưng cực kỳ quan trọng. Bên cạnh việc xóa dòng, nhóm tính năng Keep Rows trong Power Query là công cụ đắc lực giúp bạn nhanh chóng giữ lại những tập hợp dòng mong muốn theo các quy luật có sẵn. Bài viết này sẽ giúp bạn hiểu rõ định nghĩa, cách sử dụng chi tiết từng tính năng của Keep Rows, bản chất dòng lệnh M-Code phía sau, cùng các ví dụ trực quan dựa trên giao diện Power Query thực tế.

Chức năng Keep Rows trong Power Query là gì?

Keep Rows là một nhóm lệnh nằm trong tab Home, nhóm Reduce Rows trên thanh Ribbon của Power Query Editor. Chức năng chính của nó là giúp người dùng giữ lại một số lượng dòng cụ thể từ bảng dữ liệu gốc và loại bỏ tất cả các dòng còn lại dựa trên các tiêu chí xác định.Thay vì phải lọc thủ công bằng bộ lọc Filter (dễ bị sót hoặc lỗi khi dữ liệu nguồn thay đổi), Keep Rows cho phép thiết lập các bước cố định mang tính tự động hóa cao.

Chi tiết các chức năng trong bộ công cụ Keep Rows:

• Keep Top Rows (Giữ các dòng đầu): Chỉ giữ lại một số lượng dòng nhất định tính từ dòng đầu tiên của bảng xuống dưới.
• Keep Bottom Rows (Giữ các dòng cuối): Giữ lại một số lượng dòng nhất định tính từ dòng cuối cùng của bảng ngược lên trên.
• Keep Range of Rows (Giữ một khoảng dòng): Cho phép bạn chỉ định dòng bắt đầu (First Row) và số lượng dòng cần lấy kể từ vị trí đó.
• Keep Duplicates (Giữ các dòng trùng lặp): Lọc và chỉ giữ lại những hàng có giá trị bị trùng lặp dựa trên một hoặc nhiều cột được chọn. Đây là tính năng cực kỳ hữu ích để kiểm tra tính toàn vẹn của dữ liệu hoặc phát hiện lỗi nhập liệu.
• Keep Errors (Giữ các dòng bị lỗi): Chỉ giữ lại các dòng chứa giá trị lỗi (Error) trong (các) cột được chọn. Tính năng này giúp các nhà phân tích nhanh chóng cô lập lỗi để tìm phương án khắc phục.

Minh họa cụ thể các chức năng Keep Row trong Power Query

Keep Top Rows

Để chỉ lấy một số lượng dòng đầu tiên của bảng:

• Bước 1: Trên thanh menu chính, bạn nhấn vào biểu tượng Keep Rows và chọn Keep Top Rows.

• Bước 2: Một hộp thoại hiện lên yêu cầu nhập số lượng dòng cần giữ. Ở đây, chúng ta nhập số 3.

• Bước 3: Power Query ngay lập tức lọc bảng và chỉ giữ lại chính xác 3 dòng đầu tiên.

Keep Bottom Rows

Trong trường hợp muốn lấy các giao dịch mới nhất (nếu bảng đã được sắp xếp tăng dần theo thời gian) hoặc các dòng cuối bảng:

• Bước 1: Nhấp chọn Keep Rows > Chọn Keep Bottom Rows.

• Bước 2: Nhập số lượng dòng cần giữ từ dưới lên, ví dụ nhập 5 dòng.

• Bước 3: Bảng dữ liệu của bạn sẽ chỉ giữ lại đúng 5 dòng cuối cùng.

Keep Range of Rows

Nếu bạn cần lấy dữ liệu nằm ở giữa bảng (ví dụ: lấy từ dòng thứ 5 đến dòng thứ 14):

• Bước 1: Nhấp chọn Keep Rows > Chọn Keep Range of Rows.

• Bước 2: Trong hộp thoại hiển thị, nhập hai thông số quan trọng:
  • First row (Dòng bắt đầu): Nhập 5 (Hàng thứ 5 sẽ là hàng đầu tiên được chọn).
  • Number of rows (Số lượng dòng): Nhập 10 (Hệ thống sẽ lấy tiếp tục 10 hàng kể từ hàng thứ 5).

• Bước 3: Kết quả trả về gồm chính xác 10 hàng bắt đầu từ chỉ mục hàng thứ 5 của bảng gốc.

Keep Duplicates

Đây là tính năng nâng cao chuyên dùng để kiểm soát chất lượng dữ liệu:

• Bước 1: Chọn cột bạn muốn kiểm tra trùng lặp (trong ví dụ này là cột client_id). Sau đó, nhấn Keep Rows > Chọn Keep Duplicates.

• Bước 2 (Kết quả thực tế): Power Query sẽ tự động nhóm, đếm và lọc ra toàn bộ các hàng có giá trị client_id xuất hiện từ 2 lần trở lên trong bảng dữ liệu.

Mã M đằng sau chức năng Keep Rows

Khi thao tác click chuột trên giao diện của Power Query, hệ thống sẽ tự động biên dịch các hành động này thành ngôn ngữ công thức Power Query (thường gọi là mã M hay M-code). Việc hiểu rõ các hàm M-code này giúp bạn dễ dàng làm chủ Advanced Editor và tùy biến linh hoạt hơn.

Dưới đây là các hàm mã M tương ứng với từng chức năng Keep Rows:

Hàm Table.FirstN (Keep Top Rows)

• Cú pháp: Table.FirstN(table as table, countOrCondition as any)
• Giải thích: Giữ lại N dòng đầu tiên của bảng. Ngoài việc truyền vào một con số cụ thể, bạn còn có thể truyền vào một điều kiện.
• Ví dụ thực tế: = Table.FirstN(#”Changed Type”, 3)

Hàm Table.LastN (Keep Bottom Rows)

• Cú pháp: Table.LastN(table as table, countOrCondition as any)
• Giải thích: Hoạt động tương tự Table.FirstN nhưng quét và giữ dữ liệu bắt đầu từ dòng cuối cùng của bảng ngược lên trên.
• Ví dụ thực tế: = Table.LastN(#”Changed Type”, 5)

Hàm Table.Range (Keep Range of Rows)

• Cú pháp: Table.Range(table as table, offset as number, optional count as nullable number)
• Giải thích: Giữ một khoảng dòng.
  • offset: Vị trí dòng bắt đầu lấy (Lưu ý: M-code sử dụng Zero-based index, nghĩa là dòng thứ nhất có index là 0. Do đó, nếu bạn chọn bắt đầu từ dòng 5 ngoài UI, hệ thống sẽ tự động trừ đi 1 và điền offset trong mã M là 4).
  • count: Số lượng dòng cần lấy kể từ vị trí offset.
• Ví dụ thực tế: = Table.Range(#”Changed Type”, 4, 10)

Hàm Table.SelectRowsWithErrors (Keep Errors)

• Cú pháp: Table.SelectRowsWithErrors(table as table, optional columns as nullable list)
• Giải thích: Trả về một bảng chỉ chứa các hàng có lỗi trong (các) cột được chỉ định. Nếu không có cột nào được truyền vào, hệ thống sẽ kiểm tra lỗi trên toàn bộ các cột của bảng.
• Ví dụ thực tế: = Table.SelectRowsWithErrors(#”Changed Type”, {“errors”})

Keep Duplicates

Không giống các chức năng đơn giản trên, Keep Duplicates là một tổ hợp các bước xử lý dữ liệu phức tạp hơn được viết gộp lại để tối ưu hiệu suất:Hệ thống sẽ nhóm bảng dữ liệu theo cột được chọn (Table.Group), đếm tần suất xuất hiện của mỗi giá trị (Table.RowCount), sau đó lọc những giá trị có số lần xuất hiện lớn hơn hoặc bằng 2 (Table.SelectRows), và cuối cùng ghép (merge) ngược lại để giữ toàn bộ thông tin gốc của những dòng bị lặp.

Khi nào nên sử dụng Keep Rows thay vì Remove Rows?

Nhiều người dùng thường phân vân không biết nên dùng Keep Rows (Giữ dòng) hay Remove Rows (Xóa dòng) vì hai tính năng này có vẻ đối nghịch nhưng lại mang đến kết quả tương tự nếu đảo ngược logic. Dưới đây là bảng so sánh giúp ra quyết định tối ưu theo kinh nghiệm thực tế của chuyên gia:

Một số lưu ý khi sử dụng chức năng Keep Rows

Kết hợp Sort trước khi Keep Top/Bottom

Mặc định, Power Query sẽ lấy các dòng theo thứ tự xuất hiện của chúng trong nguồn dữ liệu. Nếu nguồn dữ liệu của bạn không được sắp xếp cố định, việc áp dụng Keep Top Rows có thể trả về các kết quả khác nhau sau mỗi lần Refresh.Giải pháp cho tình huống này là chèn thêm bước Sort Ascending/Descending (Sắp xếp tăng/giảm) ngay trước bước Keep Rows để đảm bảo tính nhất quán của báo cáo.

Index trong Keep Range of Rows

Trong Power Query, chỉ mục dòng khi bạn nhập vào hộp thoại Keep Range of Rows bắt đầu bằng 1 (Dòng 1 là dòng đầu tiên). Tuy nhiên, trong ngôn ngữ lập trình M (M-Code), index của mảng lại bắt đầu từ 0. Đừng lo lắng vì Power Query đã tự động tối ưu hóa giao diện người dùng để bạn nhập số thứ tự dòng thực tế một cách tự nhiên nhất.

Tổng kết

Bộ công cụ Keep Rows trong Power Query là trợ thủ đắc lực giúp bạn kiểm soát chính xác cấu trúc dữ liệu đầu vào trước khi tiến hành xây dựng báo cáo trên Excel hay Power BI. Việc nắm vững cách phối hợp giữa giao diện nút bấm trực quan và bản chất các hàm mã M như Table.FirstN, Table.LastN, hay Table.Range không chỉ giúp bạn tối ưu hóa thời gian xử lý dữ liệu mà còn hạn chế tối đa các lỗi hệ thống khi nguồn dữ liệu thay đổi hoặc mở rộng trong tương lai.

Tìm hiểu thêm khóa học Power BI tại Starttrain để xây dựng hệ thống báo cáo tự động và phân tích dữ liệu.