[Power Query Tutorial] Cách dùng chức năng Index Column

Index Column là tính năng tự động tạo số thứ tự dòng và chỉ mục định danh trong Power Query, giúp bạn tạo khóa thay thế, cố định trật tự bản ghi và phục vụ các phép tính toán nâng cao theo dòng mà không cần viết công thức phức tạp. Dưới đây Starttrain sẽ hướng dẫn toàn diện từ cách sử dụng qua giao diện, phân tích cú pháp hàm M Code đến các mẹo tối ưu hiệu năng chuẩn chuyên gia.

Chức năng Index Column là gì trong Power Query?

Index Column là công cụ tích hợp sẵn trên thanh công cụ Power Query Editor, cho phép người dùng tự động sinh một cột số nguyên tăng dần liên tục theo từng dòng của bảng dữ liệu.

Mỗi giá trị trong cột Index đại diện cho số thứ tự vị trí của dòng. Khi bấm vào mũi tên mở rộng của nút lệnh Index Column trên thanh công cụ, Power Query sẽ cung cấp 3 lựa chọn:

• From 0: Tự động đánh số thứ tự dòng bắt đầu từ số 0 với bước tăng mặc định là 1 (0, 1, 2, 3, …). Tùy chọn này rất phù hợp cho các nhà phát triển dữ liệu quen thuộc với hệ thống index đếm từ 0 trong lập trình.
• From 1: Tự động đánh số thứ tự dòng bắt đầu từ số 1 với bước tăng mặc định là 1 (1, 2, 3, 4, …). Đây là kiểu đánh số thứ tự truyền thống, trực quan nhất cho người dùng Excel và báo cáo nghiệp vụ thông thường.
• Custom…: Mở ra hộp thoại tùy chỉnh, cho phép bạn tự do quy định giá trị bắt đầu và bước nhảy giữa các dòng kế tiếp.

Ví dụ minh họa chức năng Index Column

Để nắm vững cách sử dụng Index Column, bạn có thể thực hành theo các bước dưới đây trực tiếp trên Power Query:

Mở Power Query với bảng dữ liệu mẫu ban đầu. Bạn chuyển sang tab Add Column trên thanh Ribbon, sau đó tìm đến nhóm lệnh General và rê con trỏ chuột vào vị trí nút lệnh Index Column nằm ngay bên cạnh lệnh Conditional Column.

Nhấp trực tiếp vào biểu tượng Index Column trên thanh công cụ để áp dụng nhanh tùy chọn mặc định. Hệ thống sẽ tự động thêm một cột mới mang tên Index vào vị trí cuối bảng với kiểu dữ liệu số nguyên, hiển thị chuỗi giá trị tăng dần từ 0, 1, 2, 3 đến 4 tương ứng với 5 dòng dữ liệu. Đồng thời, tại khung Applied Steps bên phải xuất hiện thêm bước thao tác Added Index.

Trong trường hợp bạn muốn tạo số thứ tự với giá trị khởi đầu khác 0 hoặc bước tăng khác 1, hãy nhấp vào dấu mũi tên trỏ xuống bên cạnh nút Index Column. Menu thả xuống sẽ xuất hiện với ba lựa chọn: From 0, From 1 và Custom…. Tại đây, bạn nhấp chọn vào mục Custom….

Khi hộp thoại Add Index Column xuất hiện trên màn hình, tiến hành nhập các tham số: điền giá trị 10 vào ô Starting Index để quy định điểm bắt đầu và điền giá trị 5 vào ô Increment để làm bước nhảy.

Sau khi bấm OK, Power Query tạo ra một cột mới có tên Index.1 mang chuỗi số tăng đều: dòng 1 là 10, dòng 2 là 15, dòng 3 là 20, dòng 4 là 25 và dòng 5 là 30.

M Code từ chức năng Index Column

Mỗi khi bạn thao tác qua giao diện đồ họa, Power Query sẽ tự động biên dịch hành động thành mã lệnh M tương ứng trên thanh công thức. Hàm chịu trách nhiệm sinh cột index là Table.AddIndexColumn.

Cú pháp hàm AddIndexColumn:

Table.AddIndexColumn(table as table, newColumnName as text, optional initialValue as number, optional step as number, optional columnType as type) as table

Trong đó

• table: Tên bảng dữ liệu đầu vào (hoặc tên bước xử lý liền trước trong quy trình).
• newColumnName: Tên cột mới được tạo (dạng chuỗi văn bản nằm trong dấu ngoặc kép, ví dụ: “Index”).
• initialValue: Giá trị khởi điểm của dòng đầu tiên (mặc định là 0 nếu chọn From 0, hoặc 1 nếu chọn From 1).
• step: Bước nhảy gia số sau mỗi dòng (mặc định là 1).
• columnType: Định dạng kiểu dữ liệu cho cột mới (thường là kiểu số nguyên).

Các đoạn code thực tế từ giao diện minh họa:

Tạo Index mặc định từ 0:
= Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1, Int64.Type)

Tạo Index tùy chỉnh (bắt đầu từ 10, bước nhảy 5):
= Table.AddIndexColumn(#”Added Index”, “Index.1”, 10, 5, Int64.Type)

Dùng Index Column khi nào?

Trong quá trình chuyển đổi và mô hình hóa dữ liệu, việc thêm cột số thứ tự không chỉ đơn thuần để đếm dòng mà còn đóng vai trò quan trọng trong nhiều kỹ thuật xử lý nâng cao. Một vài trường hợp sử dụng Index Column:

• Tạo khóa: Khi bảng dữ liệu gốc không có khóa chính, bạn có thể tạo Index Column làm trường khóa để liên kết dữ liệu giữa các bảng trong Data Model.
• Bảo toàn thứ tự dòng gốc: Trước khi thực hiện các phép biến đổi dữ liệu phức tạp như sắp xếp nhiều cấp, lọc bỏ dòng hoặc Unpivot, việc đánh Index Column giúp bạn giữ lại vị trí dòng ban đầu để có thể khôi phục đúng trật tự bất cứ khi nào cần.
• Tính toán giữa các dòng kế tiếp nhau: Power Query vốn không xử lý dữ liệu theo ô như Excel mà xử lý theo bảng. Để tính toán chênh lệch giữa dòng sau và dòng trước (ví dụ: tính mức tăng trưởng doanh thu theo ngày, tính khoảng cách thời gian giữa 2 sự kiện), kỹ thuật phổ biến là tạo 2 cột Index (một cột bắt đầu từ 0 và một cột bắt đầu từ 1) rồi tự merge với chính nó.

So sánh Index Column, Conditional Column và Custom Column

Mặc dù cùng thuộc tab Add Column, ba công cụ này giải quyết các bài toán dữ liệu hoàn toàn khác biệt:

[FAQ] Một số câu hỏi khi sử dụng Index Column

Dưới đây là tổng hợp các vướng mắc phổ biến nhất khi ứng dụng Index Column vào thực tế xử lý dữ liệu và mô hình hóa:

Số thứ tự trong Index Column có bị đảo lộn khi Refresh dữ liệu không?

Có thể bị thay đổi nếu dữ liệu nguồn không có trật tự cố định. Power Query đánh số Index dựa trên thứ tự dòng mà hệ thống đọc vào tại thời điểm thực thi. Nếu nguồn dữ liệu chèn thêm dòng mới vào giữa hoặc không có thứ tự trả về ổn định, index các dòng cũ sẽ bị thay đổi sau mỗi lần Refresh. Do đó, hãy luôn thực hiện một bước sắp xếp trên các cột dữ liệu xác định (ví dụ: ngày tháng hoặc mã giao dịch) ngay trước khi thêm bước Index Column.

Thêm Index Column có làm chậm truy vấn không?

Có. Trong phần lớn trường hợp khi kết nối với các nguồn cơ sở dữ liệu quan hệ (SQL Server, Oracle, PostgreSQL, …), bước Table.AddIndexColumn sẽ làm ngắt tính năng Query Folding. Điều này khiến Power Query không thể đẩy logic xử lý về máy chủ CSDL mà buộc phải tải toàn bộ bảng dữ liệu thô về bộ nhớ máy tính cục bộ để đánh số thứ tự. Nếu làm việc với tập dữ liệu hàng triệu dòng, bạn nên cân nhắc tạo số thứ tự ngay từ câu lệnh truy vấn SQL nguồn thay vì tạo Index Column trong Power Query.

Nên chọn “From 0” hay “From 1” trong các dự án thực tế?

Tùy thuộc vào mục đích sử dụng sau đó:

• Chọn From 1: Khi bạn cần cột số thứ tự trực quan để hiển thị trực tiếp lên bảng báo cáo, in ấn danh sách hoặc làm khóa định danh thân thiện với người dùng nghiệp vụ.
• Chọn From 0: Khi bạn cần dùng cột này để tính toán lập trình, tính phép chia lấy phần dư để gom nhóm chu kỳ dòng hoặc khi kết hợp 2 cột Index để tính chênh lệch giữa dòng hiện tại với dòng liền trước.

Có thể tạo Index Column đánh số lặp lại theo từng nhóm riêng biệt không?

Nút bấm Index Column trên thanh Ribbon chỉ hỗ trợ đánh số tăng dần liên tục cho toàn bảng. Tuy nhiên, bạn hoàn toàn có thể đánh số thứ tự lặp lại theo từng nhóm bằng cách kết hợp tính năng Group By với hàm M Table.AddIndexColumn trong cửa sổ Advanced Editor.

Tổng kết

Index Column là một trong những công cụ trực quan cần thiết trong Power Query. Dù thao tác tạo cột chỉ mất chưa đầy vài giây trên thanh Ribbon, giá trị mà cột chỉ mục này mang lại cho quy trình chuẩn hóa dữ liệu là vô cùng to lớn: từ việc đánh số thứ tự hiển thị, cố định vị trí bản ghi, cho đến đóng vai trò làm khóa và mở đường cho các thuật toán so sánh dòng nâng cao.