[Power Query Tutorial] Cách sử dụng Column From Examples

Column from Examples là tính năng tạo cột dữ liệu tự động dựa trên giá trị mẫu trong Power Query (áp dụng cho cả Excel và Power BI). Thay vì phải ghi nhớ hàng trăm cú pháp hàm M phức tạp hay mày mò tìm kiếm nút chức năng trên thanh Ribbon, bạn chỉ cần gõ vào kết quả mong muốn, hệ thống sẽ tự động phân tích quy luật, sinh mã lệnh và điền toàn bộ dữ liệu cho các dòng còn lại.

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

Khi sử dụng tính năng Column from Examples, bạn có thể tạo nhanh các cột dữ liệu mới đáp ứng chính xác nhu cầu chuyển đổi của mình. Về bản chất, tính năng này được thiết kế đặc biệt hữu ích trong các trường hợp sau:

• Bạn biết rõ kết quả dữ liệu mong muốn, nhưng không biết dùng phép biến đổi nào: Bạn hình dung được cột mới cần hiển thị gì (ví dụ: lấy số phút, ghép họ tên, tách mã định danh, …) nhưng chưa rõ công thức hay chuyển đổi như nào trong Power Query có thể tạo ra kết quả đó.
• Bạn biết phép biến đổi cần dùng, nhưng không biết chọn tính năng nào trên giao diện: Bạn hiểu logic xử lý nhưng mất thời gian tìm kiếm nút lệnh tương ứng trong hệ thống menu dày đặc của thẻ Add Column hoặc Transform.
• Cần dùng công thức ngôn ngữ M nâng cao nhưng UI không hỗ trợ sẵn nút bấm: Bạn nắm được logic biểu thức tùy chỉnh trong ngôn ngữ M, tuy nhiên giao diện người dùng tiêu chuẩn lại không có sẵn nút bấm cho thao tác đó. Khi đó, việc nhập ví dụ mẫu sẽ kích hoạt Power Query tự động dựng sẵn cấu trúc hàm M cho bạn một cách nhanh chóng.

Minh họa chức năng Column from Examples

Trên thanh công cụ của Power Query Editor, người dùng truy cập thẻ Add Column, bấm vào biểu tượng Column From Examples. Menu xổ xuống cho phép chọn giữa hai tùy chọn: From All Columns (suy luận từ tất cả các cột) và From Selection (chỉ suy luận từ các cột đang được chọn).

From Selection

Bảng dữ liệu có cột End_DateTime mang giá trị dòng đầu tiên là 2024-01-08 11:45:00. Tại cột mới Column1, khi người dùng nhập số 45, Power Query tự động mở một danh sách gợi ý thông minh phù hợp với ngữ cảnh:

• Trích xuất nguyên chuỗi ngày giờ
• Trích xuất ngày kèm múi giờ (+07:00)
• Trích xuất phút: 45 (Minutes from End_DateTime)
• Trích xuất định dạng thời gian: 11:45:00 (Time from End_DateTime)

Khi người dùng chọn gợi ý phút, Power Query tự động nhận diện quy luật và hiển thị công thức xem trước ngay phía trên bảng: Transform: Time.Minute([End_DateTime])

Cột mới được tự động đổi tên thành Minute và các giá trị của toàn bộ dòng bên dưới được điền tự động (dòng 2 là 30, dòng 3 là 0, dòng 4 là 0, dòng 5 là 30…).

Sau khi nhấn nút OK, một bước xử lý mới có tên Inserted Minute được ghi nhận vĩnh viễn vào danh sách Applied Steps. Thanh công thức hiển thị mã lệnh M hoàn chỉnh:
= Table.AddColumn(#”Removed Columns”, “Minute”, each Time.Minute([End_DateTime]), Int64.Type)

From All Columns

Khi bài toán cần trích xuất dữ liệu dựa trên sự kết hợp của nhiều cột, hoặc khi bạn chưa rõ cột nào nắm vai trò chính, tùy chọn From All Columns sẽ tự động đưa toàn bộ các cột trong bảng vào phạm vi phân tích.

Khi người dùng nhập giá trị 0 ở dòng đầu tiên tại cột mới, Power Query cố gắng tìm kiếm quy luật dựa trên ký tự xuất hiện trong giá trị ngày giờ 2024-01-08 11:45:00. Do chỉ có 1 ví dụ đơn lẻ và phạm vi quét là toàn bộ bảng, Power Query không nhận diện đây là phép trừ ngày tháng.

Để điều hướng thuật toán, người dùng không cần xóa đi làm lại mà chỉ cần cung cấp thêm các ví dụ ở các dòng tiếp theo:

• Giữ nguyên số 0 ở dòng 1 (ngày 08 trừ ngày 08 = 0 ngày).
• Nhấp vào dòng 3 và nhập số 2 (ngày 12 trừ ngày 10 = 2 ngày chênh lệch).
• Nhấp vào dòng 6 và nhập số 1 (ngày 16 trừ ngày 15 = 1 ngày chênh lệch).

Ngay khi tiếp nhận đủ dữ liệu, Power Query lập tức cập nhật lại công thức chính xác: Transform: Duration.Days([End_DateTime] – [Start_DateTime]) Cột mới tự động đổi tên thành Subtraction, toàn bộ các dòng còn lại được tính toán số ngày chênh lệch hoàn hảo.

Hướng dẫn các bước sử dụng Column from Examples

Để khai thác tối đa sức mạnh của tính năng và hạn chế sai lệch công thức, bạn nên áp dụng quy trình 4 bước chuẩn sau:

• Lựa chọn phạm vi phân tích: Nếu chỉ xử lý trên 1 cột (tách chuỗi, lấy giờ phút, viết hoa/viết thường), hãy chọn cột đó và dùng From Selection. Nếu cần kết hợp từ 2 cột trở lên (ghép họ tên, trừ thời gian), chọn From All Columns.
• Nhập giá trị mẫu đầu tiên: Nhấp đúp chuột vào ô trống ở dòng đầu tiên tại cột mới, gõ kết quả mong muốn và nhấn Enter.
• Cung cấp mẫu (Dạy quy luật cho máy): Quan sát các dòng tự động điền bên dưới (chữ màu xám). Nếu phát hiện dòng nào cho kết quả sai, hãy nhấp thẳng vào ô đó và nhập kết quả đúng. Hệ thống sẽ tự tinh chỉnh lại công thức M phù hợp.
• Kiểm tra công thức và xác nhận: Quan sát dòng Transform: … ở trên bảng để đảm bảo Power Query dùng đúng bản chất hàm mong muốn, sau đó nhấn OK để lưu bước vào Applied Steps.

Ưu điểm của chức năng Column from Examples

Không chỉ đơn giản hóa các phép biến đổi dữ liệu phức tạp, tính năng Column from Examples còn mang lại nhiều lợi thế vượt trội giúp tối ưu hiệu suất làm việc hàng ngày của bạn:

Không cần ghi nhớ cú pháp ngôn ngữ M phức tạp

Rào cản lớn nhất đối với người mới tiếp cận Power Query chính là kho tàng hàm M đồ sộ. Tính năng Column from Examples xóa bỏ hoàn toàn rào cản này, giúp bạn biến đổi dữ liệu thành công mà không đòi hỏi nền tảng lập trình hay kỹ năng viết code nâng cao.

Tối ưu tốc độ làm sạch và chuẩn hóa dữ liệu

Thay vì phải mất nhiều phút tìm kiếm từng công cụ trong hệ thống menu dày đặc của thẻ Add Column hay Transform, bạn chỉ cần gõ vài ký tự ví dụ. Những tác vụ phổ biến như tách chuỗi, trích xuất thời gian hay định dạng văn bản đều được hoàn tất chỉ trong vài giây.

Công cụ học ngôn ngữ M trực quan và thực tế

Column from Examples đóng vai trò như một người trợ lý hướng dẫn lập trình trực tiếp. Khi theo dõi các dòng mã tự động sinh ra trên thanh Formula Bar như Time.Minute, Text.BetweenDelimiters hay Duration.Days, … người dùng sẽ hiểu nhanh bản chất và dần nâng cao trình độ viết code M.

Tự động hóa hoàn toàn theo quy trình ETL

Tất cả các cột tạo từ ví dụ đều được lưu trữ thành một bước cố định trong danh sách Applied Steps. Nhờ đó, mỗi khi nguồn dữ liệu được Refresh, toàn bộ logic biến đổi sẽ tự động áp dụng chính xác cho các dòng dữ liệu mới mà không cần thao tác lại thủ công.

Một số lưu ý khi dùng Column from Examples

Ưu tiên chọn chế độ “From Selection” thay vì “From All Columns”

Nếu bảng dữ liệu chứa nhiều trường thông tin, tùy chọn From All Columns có thể làm thuật toán suy luận chậm và dễ ghép nhầm các cột không liên quan. Bạn nên chủ động bôi đen đúng các cột cần xử lý trước khi kích hoạt tính năng để tối ưu tốc độ và độ chuẩn xác.

Cung cấp từ 2 đến 3 ví dụ để chốt quy luật

Chỉ nhập duy nhất một giá trị mẫu rất dễ khiến hệ thống suy đoán sai bản chất (chẳng hạn như nhầm lẫn phép trừ ngày thành hàm cắt chuỗi ký tự). Bằng cách điền thêm ví dụ ở các dòng có dữ liệu khác biệt, bạn sẽ giúp Power Query chốt chặn và hiểu chính xác logic nghiệp vụ mong muốn.

Luôn kiểm tra dòng công thức Transform trước khi bấm OK

Trước khi xác nhận hoàn tất, hãy quan sát kỹ công thức hiển thị ở thanh xem trước phía trên bảng dữ liệu. Thao tác này giúp bạn đảm bảo kết quả trả về đúng kiểu dữ liệu (ngày tháng, số học hay văn bản), tránh nguy cơ phát sinh lỗi định dạng khi nguồn dữ liệu có sự thay đổi.

Nhận biết giới hạn với các bài toán điều kiện phức tạp

Column from Examples hoạt động hiệu quả nhất với các thao tác trích xuất, ghép nối và định dạng. Khi gặp những yêu cầu phân nhánh logic nhiều tầng (IF – THEN – ELSE lồng nhau), bạn nên chuyển sang dùng tính năng Conditional Column hoặc viết biểu thức trực tiếp trong Custom Column để đảm bảo tính ổn định.

Phân biệt Column from Examples và Custom Column

[FAQ] Một số thắc mắc liên quan đến Column from Examples

Column from Examples có làm file Power BI hay Excel bị nặng và chạy chậm không?

Không. Sau khi nhấn OK, Power Query sẽ chuyển đổi ví dụ của bạn thành một câu lệnh hàm M. Hiệu năng xử lý và tốc độ làm mới dữ liệu hoàn toàn tương đương với việc bạn tự tay viết công thức M.

Có thể đổi tên cột trực tiếp trong lúc đang nhập ví dụ không?

Có. Bạn chỉ cần nhấp đúp vào phần tiêu đề của cột mới (nơi hiển thị mặc định là Column1 hoặc tên do máy tự đề xuất như Minute, Subtraction) và nhập tên bạn mong muốn trước khi nhấn nút OK.

Làm cách nào để chỉnh sửa lại quy luật nếu dữ liệu đầu ra bị sai sau khi đã bấm OK?

Bạn có thể nhấp đúp vào biểu tượng bánh răng bên cạnh bước vừa tạo trong danh sách Applied Steps để mở lại giao diện nhập mẫu và bổ sung thêm ví dụ. Ngoài ra, bạn cũng có thể tinh chỉnh trực tiếp biểu thức trên thanh công thức.

Tổng kết

Column from Examples là một trợ thủ đắc lực giúp thu hẹp khoảng cách giữa người dùng phổ thông và sức mạnh xử lý dữ liệu nâng cao của Power Query. Thay vì phải nhớ từng cú pháp hàm M hay mò mẫm qua vô số menu tính năng, bạn hoàn toàn có thể để hệ thống tự động hóa công việc chỉ với vài thao tác nhập ví dụ trực quan.

Hãy bắt đầu áp dụng ngay Column from Examples vào các dự án Excel và báo cáo Power BI của bạn để tăng tốc độ làm sạch dữ liệu và tối ưu hóa quy trình phân tích hằng ngày. Bạn cũng có thể tự học các công cụ qua trang Thư viện tự học miễn phí của Starttrain.