Nếu mỗi thứ Hai bạn đều mở cùng một file xuất từ phần mềm, xoá ba dòng đầu, xoá cột thừa, đổi định dạng ngày, lọc bỏ hàng huỷ, rồi dán vào bảng theo dõi, thì bạn đang làm đúng thứ Power Query sinh ra để thay. Công cụ này nằm sẵn trong thẻ Data của Excel trên Windows, nhưng rất nhiều người dùng Excel nhiều năm chưa từng bấm vào, vì cái tên nghe có vẻ dành cho dân lập trình.
Thực ra Power Query giống một cuốn sổ ghi lại các bước bạn làm với bảng. Lần đầu bạn làm bằng chuột, nó ghi lại từng bước. Lần sau dữ liệu mới về, nó tự làm lại toàn bộ các bước đó trong vài giây. Bài này giải thích cách nghĩ về Power Query, các bước hay dùng nhất với dữ liệu văn phòng Việt Nam, và cách dùng AI để đi nhanh hơn mà không phó mặc.
Cách nghĩ đúng: công thức tính trên ô, Power Query xử lý cả bảng
Người quen Excel thường giải quyết mọi việc bằng công thức: thêm một cột phụ, viết hàm, kéo xuống. Cách này tốt khi bạn cần tính toán trên từng ô và muốn thấy kết quả thay đổi ngay khi sửa số. Nhưng khi việc cần làm là sửa hình dạng của cả bảng, như bỏ dòng tiêu đề thừa, tách một cột thành hai, xoay bảng từ dạng ngang sang dạng dọc, ghép với bảng danh mục, thì công thức trở nên rối và dễ vỡ.
Power Query làm việc ở tầng khác. Nó lấy dữ liệu từ một nguồn, như file Excel, file CSV, thư mục, trang tính trong chính file, rồi áp lần lượt các bước biến đổi, cuối cùng đổ kết quả ra một bảng mới. Dữ liệu gốc không bị đụng tới. Bảng kết quả không chứa công thức, chỉ chứa giá trị, nên nhẹ và khó bị ai vô tình sửa hỏng.
Hệ quả quan trọng nhất là tính lặp lại. Vì mọi bước được ghi lại theo thứ tự, khi nguồn có dữ liệu mới, bạn bấm Refresh và toàn bộ chuỗi chạy lại. Đây là lý do Power Query thuộc nhóm công cụ tự động hoá chứ không chỉ là công cụ làm sạch. Việc chuẩn hoá giá trị cụ thể như họ tên, số điện thoại có bài riêng ở danh mục làm sạch dữ liệu; ở đây tập trung vào chuyện biến việc tay hằng tuần thành chuỗi bước chạy lại được.
Bốn vùng trên màn hình Power Query Editor
Thanh công cụ phía trên. Chứa các thao tác: bỏ cột, giữ dòng, tách cột, đổi kiểu, nhóm, xoay bảng, ghép truy vấn. Phần lớn việc của bạn nằm ở đây, chủ yếu trong các thẻ Home, Transform và Add Column.
Danh sách truy vấn bên trái. Mỗi nguồn dữ liệu hoặc mỗi bảng trung gian là một truy vấn. Một file có thể có nhiều truy vấn: một cho bảng bán hàng, một cho bảng danh mục sản phẩm, một cho kết quả ghép hai bảng.
Vùng xem trước ở giữa. Hiển thị dữ liệu sau bước đang chọn. Lưu ý đây thường chỉ là phần đầu của dữ liệu để xem cho nhanh, không phải toàn bộ, nên đừng kết luận "không có lỗi" chỉ vì vùng xem trước sạch.
Khung Applied Steps bên phải. Đây là trái tim của Power Query: danh sách các bước theo thứ tự. Bấm vào một bước để xem dữ liệu lúc đó, bấm dấu nhân để xoá bước, kéo để đổi thứ tự. Khi kết quả sai, bạn đi ngược từng bước trong khung này để tìm chỗ bắt đầu sai, giống như lật lại sổ ghi chép.
Thanh công thức và Advanced Editor. Mỗi bước thực chất là một dòng mã ngôn ngữ M. Bạn không bắt buộc phải đọc, nhưng bật thanh công thức (trong thẻ View) giúp bạn dần quen, và Advanced Editor cho xem toàn bộ mã của truy vấn khi cần nhờ AI giải thích.
Những bước hay dùng nhất với dữ liệu văn phòng
Bảng dưới liệt kê các bước mà người làm kế toán, bán hàng, kho dùng nhiều nhất, kèm tình huống điển hình. Tên thao tác theo giao diện tiếng Anh.
| Thao tác | Tình huống điển hình | Lưu ý |
|---|---|---|
| Remove Top Rows, Use First Row as Headers | File xuất từ phần mềm có vài dòng tên công ty, ngày in phía trên | Nếu số dòng thừa thay đổi theo kỳ, lọc theo nội dung thay vì xoá cố định số dòng |
| Change Type, Using Locale | Ngày ghi dạng ngày trước tháng sau, số dùng dấu chấm ngăn nghìn | Chọn vùng phù hợp với cách ghi trong file nguồn, không phải vùng của máy bạn |
| Split Column by Delimiter | Cột "Ma hang - Ten hang" cần tách làm hai | Kiểm dòng có nhiều hơn một dấu phân cách |
| Unpivot Columns | Bảng có mỗi tháng một cột, cần đổi thành dạng mỗi dòng một tháng | Bảng dạng dọc dễ lọc, dễ cộng hơn nhiều |
| Merge Queries | Ghép bảng bán hàng với bảng danh mục để lấy nhóm hàng | Giống dò tìm, nhưng làm một lần cho cả bảng; kiểm dòng không khớp |
| Group By | Cộng số lượng theo mã hàng trước khi đưa đi nơi khác | Chỉ nhóm khi thật sự cần; giữ bản chi tiết ở một truy vấn riêng |
Không cần học hết. Với phần lớn việc văn phòng, chừng mười thao tác là đủ dùng cho nhiều năm. Học theo việc thật: lần tới gặp một bảng cần sửa hình dạng, thử làm trong Power Query thay vì bằng tay.
Nhờ AI gợi bước: quy trình bốn bước
AI hữu ích nhất khi bạn biết mình muốn bảng cuối cùng trông thế nào nhưng không biết thao tác nào dẫn tới đó. Quy trình dưới đây giúp kết quả AI đưa ra sát với file thật của bạn.
- Mô tả bảng đầu vào bằng chữ: có những cột nào, kiểu gì, dòng tiêu đề nằm đâu, có dòng tổng cộng không. Nếu dán dữ liệu mẫu, chỉ dán năm đến mười dòng đã thay tên người, số điện thoại, số tài khoản bằng giá trị giả.
- Mô tả bảng đầu ra mong muốn: cột nào giữ, cột nào thêm, mỗi dòng là gì. Ví dụ: "Mỗi dòng là một mã hàng trong một tháng, có cột Thang, Ma hang, So luong."
- Yêu cầu AI trả lời dưới dạng danh sách thao tác trên giao diện, đánh số, kèm tên thẻ và tên nút; sau đó mới hỏi mã M tương ứng nếu muốn.
- Làm theo từng thao tác trong Editor, sau mỗi bước nhìn vùng xem trước. Bước nào ra kết quả khác mô tả, dừng lại, chụp mô tả kết quả và hỏi tiếp, thay vì làm hết rồi mới xem.
Một câu lệnh mẫu: "Trong Power Query, bảng đang có các cột Ma hang, T1, T2, … T12 là số lượng bán mỗi tháng. Cần đổi thành ba cột Ma hang, Thang, So luong. Liệt kê từng thao tác trên giao diện Excel tiếng Anh, sau đó cho mã M." Câu trả lời đúng sẽ dùng thao tác Unpivot Other Columns sau khi chọn cột Ma hang.
Khi AI viết mã M: những điểm cần soi
Đôi khi AI đưa thẳng một đoạn mã M dài để bạn dán vào Advanced Editor. Cách này nhanh nhưng dễ sai ở những chỗ nhỏ, và lỗi M thường báo bằng câu khó hiểu. Trước khi dán, soi các điểm sau:
- Tên cột trong mã phải khớp chính xác với tên cột trong file của bạn, kể cả chữ hoa, dấu tiếng Việt và khoảng trắng. AI hay tự "sửa đẹp" tên cột.
- Bước đầu tiên của mã phải trỏ đúng nguồn của bạn. Cách an toàn là giữ nguyên dòng nguồn mà Excel đã tạo, chỉ thay các bước phía sau.
- Các bước đổi kiểu dữ liệu nên nằm sau bước xoá dòng thừa; đổi kiểu trước sẽ báo lỗi ở những dòng tiêu đề thừa.
- AI có thể dùng hàm không tồn tại hoặc tên tham số sai. Nếu Editor báo lỗi tên, hỏi lại AI kèm câu báo lỗi nguyên văn.
- Mã quá dài cho một việc đơn giản là dấu hiệu nên làm lại bằng giao diện. Mã do giao diện tạo ra dễ bảo trì hơn cho người sau.
Nguyên tắc chung: dán mã AI vào một truy vấn bản sao (chuột phải, Duplicate) thay vì sửa trực tiếp truy vấn đang chạy. Khi bản sao cho kết quả đúng, mới thay thế bản cũ.
Ví dụ giả định: file xuất kho mỗi thứ Hai, từ đầu đến cuối
Ví dụ giả định: một công ty phân phối nhỏ ở Hải Phòng mỗi thứ Hai xuất từ phần mềm kho một file tên PhieuXuat kèm ngày. File có bốn dòng đầu ghi tên công ty và khoảng ngày, dòng thứ năm là tiêu đề cột, cuối file có một dòng Tổng cộng. Cột ngày ghi theo dạng ngày trước tháng sau, cột mã hàng lẫn chữ hoa chữ thường, và có cột Ghi chú mà không ai dùng.
Nhân viên kho lần đầu làm trong Power Query như sau: lấy dữ liệu từ file, bỏ bốn dòng đầu, dùng dòng thứ năm làm tiêu đề, lọc bỏ dòng có chữ Tổng cộng ở cột đầu, xoá cột Ghi chú, đổi cột ngày sang kiểu ngày với vùng Việt Nam, chuyển cột mã hàng về chữ hoa, cuối cùng ghép với bảng danh mục để lấy nhóm hàng. Tám bước, mỗi bước hiện thành một dòng trong khung Applied Steps.
Thứ Hai tuần sau, nhân viên chỉ cần lưu file mới đè lên file cũ cùng tên ở cùng chỗ, mở file tổng và bấm Refresh All. Việc trước đây mất nửa tiếng giờ còn vài giây, cộng thêm hai phút đếm dòng và so tổng số lượng với dòng Tổng cộng của file gốc để chắc không sót. Nếu phần mềm kho đổi mẫu xuất, ví dụ thêm một dòng ở đầu, bước bỏ bốn dòng sẽ lệch; đó là lúc mở khung Applied Steps ra sửa đúng một bước.
Những việc không nên giao cho Power Query
Power Query mạnh nhưng không phải chỗ cho mọi việc. Nếu bạn cần nhập liệu tay vào bảng kết quả, đừng gõ vào bảng do Power Query tạo ra: lần làm mới sau, mọi thứ bạn gõ sẽ bị xoá hoặc lệch dòng. Phần nhập tay nên nằm ở một bảng riêng, rồi dùng Merge để ghép vào nếu cần.
Nếu việc cần làm là tính toán phụ thuộc lẫn nhau giữa các ô, như kế hoạch có nhiều kịch bản người dùng tự thay số để xem kết quả, công thức vẫn hợp hơn. Power Query chỉ cập nhật khi bấm làm mới, không phản hồi tức thì.
Nếu việc cần làm là thao tác bên ngoài bảng như in, gửi thư, đổi tên file, tạo thư mục, Power Query không làm được; đó là phần của macro hoặc Apps Script, có bài riêng trong danh mục này. Và nếu bạn cần nối nhiều ứng dụng khác nhau, như đưa đơn từ biểu mẫu web sang phần mềm kế toán, đó là chuyện tự động hoá liên ứng dụng mà trang AI Kết Nối của cùng hệ bàn riêng.
Cuối cùng, Power Query không thay được việc kiểm tra. Sau mỗi lần làm mới, vẫn cần đếm số dòng và so một con số tổng với nguồn, đặc biệt trong vài tuần đầu.
Câu hỏi thường gặp
Power Query có trong Excel bản nào?
Trên Windows, Power Query được tích hợp sẵn từ Excel 2016 trong nhóm Get & Transform của thẻ Data; Excel 2010 và 2013 cần cài phần bổ trợ. Excel trên Mac và trên trình duyệt có hỗ trợ nhưng một số chức năng có thể khác hoặc thiếu, nên kiểm phiên bản bạn đang dùng.
Mã M có khó học không?
Bạn không cần học mã M để dùng Power Query ở mức văn phòng, vì giao diện tự sinh mã. Đọc mã giúp hiểu và sửa nhanh hơn, và AI giải thích mã M khá tốt nếu bạn dán đoạn mã đã bỏ đường dẫn nhạy cảm.
Làm mới dữ liệu có làm mất định dạng bảng không?
Định dạng cơ bản của bảng kết quả thường được giữ, nhưng cột bạn thêm tay bên cạnh có thể lệch nếu số dòng thay đổi. Nên để mọi cột tính toán trong Power Query hoặc trong một bảng riêng tham chiếu tới bảng kết quả.
Power Query và PivotTable khác nhau thế nào?
Power Query chuẩn bị và định hình dữ liệu, PivotTable tóm tắt dữ liệu đã chuẩn bị. Thường dùng nối tiếp: Power Query làm bảng sạch, PivotTable đọc bảng đó. Phần tóm tắt và đọc ý nghĩa số liệu thuộc khâu phân tích.
Gửi file có Power Query cho người khác thì họ có làm mới được không?
Họ làm mới được nếu máy họ truy cập được cùng nguồn dữ liệu, ví dụ cùng thư mục trên ổ chung. Nếu nguồn là đường dẫn trên máy bạn, họ sẽ gặp lỗi không tìm thấy nguồn. Khi đó chỉ cần sửa đường dẫn trong bước nguồn.
Có nên dán cả bảng dữ liệu thật vào chatbot để AI viết Power Query không?
Không nên. AI chỉ cần cấu trúc và vài dòng mẫu đã che thông tin để gợi bước đúng. Dán cả bảng thật vừa không cần thiết vừa có nguy cơ lộ dữ liệu khách hàng hoặc bí mật kinh doanh.