Power Query Trong Excel: Làm Sạch Và Gộp Nhiều File
Power Query là công cụ lấy và làm sạch dữ liệu có sẵn trong Excel từ bản 2016. Điểm mạnh của nó nằm ở chỗ mọi thao tác bạn làm đều được ghi lại thành các bước, nên lần sau dữ liệu thay đổi chỉ cần bấm Refresh là chạy lại toàn bộ.
Đây là khác biệt căn bản so với việc dọn dữ liệu bằng tay: làm một lần, dùng mãi.
Mở Power Query
Power Query nằm ở tab Data, trong nhóm Get & Transform Data. Không phải cài thêm gì.
Ba lệnh hay dùng nhất:
- From Table/Range - lấy dữ liệu từ chính bảng trong file hiện tại.
- From File → From Workbook - lấy từ file Excel khác.
- From File → From Folder - lấy toàn bộ file trong một thư mục, gộp lại thành một bảng.
Các bước làm sạch thường dùng
Sau khi lấy dữ liệu vào, cửa sổ Power Query Editor mở ra. Mỗi thao tác bạn làm sẽ thành một bước ở khung Applied Steps bên phải, xóa được và sắp xếp lại được.
Dùng dòng đầu làm tiêu đề. Use First Row as Headers, xử lý trường hợp file xuất ra có tiêu đề nằm ở dòng dữ liệu.
Đổi kiểu dữ liệu. Bấm vào biểu tượng cạnh tên cột để chọn kiểu. Đây là bước quan trọng nhất, vì cột ngày tháng hay số bị nhận nhầm thành văn bản là nguyên nhân của phần lớn lỗi tính toán về sau.
Xóa dòng và cột thừa. Remove Rows để bỏ dòng trống, dòng trùng, dòng đầu hoặc cuối.
Tách cột. Split Column theo dấu phân cách hoặc theo số ký tự. Đây là cách thay thế tốt cho các hàm tách chuỗi mà bản vĩnh viễn không có, xem tách chuỗi trong Excel.
Unpivot Columns. Biến bảng chéo có các tháng nằm ngang thành bảng dọc chuẩn. Đây là thao tác giá trị nhất của Power Query và gần như không có cách nào làm nhanh tương đương bằng công thức.
Gộp nhiều file trong một thư mục
Đây là việc Power Query làm tốt nhất. Giả sử bạn có 12 file báo cáo tháng cùng cấu trúc:
- Vào Data → Get Data → From File → From Folder, chọn thư mục chứa 12 file.
- Bấm Combine & Transform Data.
- Chọn sheet cần lấy, Power Query tự gộp cả 12 file.
- Làm sạch nếu cần, rồi bấm Close & Load.
Tháng sau chỉ cần thả file mới vào thư mục rồi bấm Refresh, bảng tổng tự dài thêm. Cách này thay thế hoàn toàn cho việc sao chép dán thủ công, và cũng là cách thay cho hàm ghép mảng mà bản vĩnh viễn không có, xem nhóm hàm VSTACK và cách thay thế.
Gộp hai bảng theo khóa chung
Dùng Merge Queries khi bạn cần nối bảng đơn hàng với bảng khách hàng theo mã khách:
- Trong Power Query Editor, chọn Merge Queries.
- Chọn bảng thứ hai và cột khóa của mỗi bên.
- Chọn kiểu nối, thường là Left Outer để giữ toàn bộ dòng của bảng chính.
- Mở rộng cột kết quả và chọn những trường cần lấy.
Cách này thay cho hàng nghìn công thức VLOOKUP, và nhanh hơn nhiều trên dữ liệu lớn, xem thêm INDEX MATCH và VLOOKUP.
Nạp kết quả vào đâu?
Ở bước Close & Load, chọn Close & Load To để có thêm lựa chọn:
- Table - nạp ra sheet dưới dạng bảng. Dùng khi dữ liệu không quá lớn.
- Only Create Connection - chỉ tạo kết nối, không đổ dữ liệu ra sheet. File nhẹ hơn nhiều.
- Add to Data Model - nạp vào Data Model để phân tích bằng Power Pivot, xem Power Pivot và Data Model.
Với dữ liệu lớn, nạp vào Data Model thay vì ra sheet là cách giữ file nhẹ, xem thêm tối ưu file Excel nặng.
Lỗi thường gặp
Refresh báo không tìm thấy file. Đường dẫn nguồn đã đổi. Vào Data Source Settings để sửa lại đường dẫn.
Cột biến mất sau khi refresh. Do tên cột ở file nguồn thay đổi. Power Query nhớ tên cột chứ không nhớ vị trí.
Ngày tháng bị nhận sai. Do khác biệt định dạng ngày giữa hệ thống và file. Đổi kiểu dữ liệu bằng Using Locale và chọn đúng vùng.
Power Query có từ key Office 2016 trở lên, giao diện các bản mới gọn hơn nhưng nguyên tắc như nhau.
Câu hỏi thường gặp
Power Query có làm hỏng dữ liệu gốc không?
Không. Nó chỉ đọc từ nguồn và tạo bản kết quả riêng.
Có cần biết lập trình không?
Không. Mọi thao tác thông thường đều làm bằng chuột qua menu.
Power Query khác Power Pivot thế nào?
Power Query lấy và làm sạch dữ liệu. Power Pivot mô hình hóa và tính toán. Thường dùng nối tiếp nhau.
