Cách dùng Power Query để làm sạch dữ liệu trong Excel (không cần viết công thức)

Power Query là gì và vì sao nên dùng?

Khi nhận dữ liệu thô từ hệ thống khác (file xuất từ phần mềm bán hàng, file đồng nghiệp gửi, dữ liệu chấm công...), dữ liệu thường lộn xộn: tên cột không thống nhất, có dòng trống, số bị lưu dưới dạng văn bản, ngày tháng sai định dạng, dữ liệu nằm rải rác ở nhiều file. Cách làm truyền thống là dùng công thức hoặc sửa tay từng ô — vừa mất thời gian, vừa phải làm lại từ đầu mỗi khi có dữ liệu mới.

Power Query là công cụ có sẵn trong Excel (từ Excel 2016 trở lên, nằm ở tab Data) giúp bạn:

Kết nối tới nhiều nguồn dữ liệu (file Excel khác, CSV, folder chứa nhiều file, database...).
Thực hiện các bước làm sạch (xóa dòng trống, tách cột, đổi kiểu dữ liệu, gộp bảng...) thông qua thao tác click chuột, không cần viết công thức.
Ghi nhớ lại toàn bộ các bước đã làm — lần sau chỉ cần bấm Refresh là dữ liệu mới tự chạy qua đúng quy trình làm sạch cũ, không phải lặp lại thao tác.

Ví dụ dữ liệu dùng xuyên suốt bài — một bảng nhân viên thô, xuất ra từ hệ thống khác, đang có nhiều lỗi định dạng:

	A	B	C	D
1	ho_ten	phong_ban	luong	ngay_vao_lam
2	Nguyễn Văn A	Kế Toán	8500000	15/01/2024
3	trần thị b	kinh doanh	"9200000"	2024-02-20
4
5	Lê Văn C	Kỹ Thuật	8800000	10/3/2024

Cột tên chưa viết hoa chuẩn, cột lương có dòng bị lưu dạng văn bản (dấu ngoặc kép), ngày tháng hai định dạng khác nhau, và có một dòng trống xen giữa.

Bước 1: Đưa dữ liệu vào Power Query

Bôi đen vùng dữ liệu (hoặc đặt con trỏ vào trong bảng, tốt nhất nên chuyển dữ liệu thành Table bằng Ctrl+T trước).
Vào tab Data → nhóm Get & Transform Data → chọn From Table/Range (nếu dữ liệu đang nằm sẵn trong Excel) hoặc From File (nếu đang lấy từ file CSV/Excel khác).
Excel mở ra cửa sổ Power Query Editor — đây là nơi thực hiện toàn bộ các bước làm sạch, tách biệt hoàn toàn với dữ liệu gốc trên sheet.

Bước 2: Xóa dòng trống và dòng lỗi

Trong Power Query Editor:

Vào tab Home → Remove Rows → Remove Blank Rows để loại bỏ ngay dòng trống (dòng 4 trong ví dụ).
Nếu có dòng lỗi phát sinh trong quá trình xử lý (Power Query hiển thị "Error" màu đỏ trong ô), vào Home → Remove Rows → Remove Errors để loại các dòng đó, hoặc bấm vào từng ô lỗi để xem nguyên nhân cụ thể trước khi quyết định xóa.

Bước 3: Chuẩn hóa định dạng chữ

Cột ho_ten và phong_ban đang viết hoa/thường không thống nhất:

Chọn cột ho_ten.
Vào tab Transform → Format → chọn Capitalize Each Word để tự động viết hoa chữ cái đầu mỗi từ.
Làm tương tự với cột phong_ban.

Power Query còn có các lựa chọn khác trong menu Format: UPPERCASE, lowercase, Trim (xóa khoảng trắng thừa đầu/cuối), Clean (xóa ký tự không in được) — nên áp dụng Trim cho mọi cột dạng văn bản để tránh lỗi so khớp dữ liệu về sau do khoảng trắng thừa mà mắt thường không thấy được.

Bước 4: Sửa kiểu dữ liệu cột số và cột ngày

Cột luong có giá trị đang bị lưu như văn bản (do dấu ngoặc kép từ hệ thống nguồn):

Chọn cột luong.
Vào tab Transform → Data Type → chọn Decimal Number hoặc Whole Number.
Nếu Power Query báo lỗi chuyển đổi ở một số dòng, thường do còn ký tự lạ (dấu phẩy phân cách nghìn, khoảng trắng) — dùng thêm Transform → Replace Values để xóa ký tự đó trước khi đổi kiểu dữ liệu.

Cột ngay_vao_lam có hai định dạng ngày khác nhau lẫn lộn (15/01/2024 và 2024-02-20) — đây là lỗi phổ biến khi gộp dữ liệu từ nhiều nguồn. Cách xử lý an toàn nhất là tách riêng theo từng định dạng gốc rồi mới gộp lại, hoặc yêu cầu chuẩn hóa định dạng ngày ngay từ nguồn xuất dữ liệu trước khi import, vì Power Query chuyển đổi kiểu Date dựa trên một định dạng cố định — áp cùng lúc cho cột có 2 định dạng lẫn nhau sẽ làm sai lệch một phần dữ liệu (ví dụ nhầm ngày với tháng).

Bước 5: Tách cột và gộp cột

Hai thao tác dùng thường xuyên khi làm sạch dữ liệu:

Tách cột (ví dụ cột "Họ và tên" muốn tách thành "Họ" và "Tên"):

Chọn cột cần tách.
Vào tab Home → Split Column → By Delimiter (theo ký tự phân cách, ví dụ khoảng trắng) hoặc By Number of Characters (theo số ký tự cố định).

Gộp cột (ví dụ gộp "Họ" và "Tên" lại thành một cột "Họ tên"):

Chọn nhiều cột cần gộp (giữ Ctrl để chọn nhiều cột).
Vào tab Transform → Merge Columns → chọn ký tự ngăn cách (ví dụ dấu cách) → đặt tên cột mới.

Bước 6: Gộp nhiều bảng/file thành một

Đây là tính năng mạnh nhất của Power Query so với copy-paste thủ công — gộp dữ liệu từ nhiều file cùng cấu trúc (ví dụ báo cáo bán hàng của 12 tháng, mỗi tháng một file):

Vào tab Data → Get Data → From File → From Folder.
Chọn thư mục chứa tất cả các file cần gộp.
Power Query liệt kê danh sách file, bấm Combine & Transform — Power Query tự đọc cấu trúc của file đầu tiên và áp dụng cho toàn bộ các file còn lại trong thư mục.
Kết quả là một bảng duy nhất chứa dữ liệu của tất cả các file, có thêm cột tên file gốc để biết dòng nào đến từ file nào.

Khi có thêm file mới trong thư mục (ví dụ tháng tiếp theo), chỉ cần bấm Refresh — không cần lặp lại thao tác gộp.

Bước 7: Hoàn tất và đưa dữ liệu về Excel

Kiểm tra lại danh sách các bước đã thực hiện ở khung Applied Steps bên phải cửa sổ — có thể xóa hoặc sửa lại từng bước nếu cần, giống như một quy trình được ghi lại tự động.
Vào tab Home → Close & Load → Close & Load To... để chọn đưa dữ liệu ra sheet mới dạng Table, hoặc chỉ tạo Connection (nếu dữ liệu này dùng làm nguồn cho PivotTable mà không cần hiển thị riêng).
Từ lần sau, mỗi khi dữ liệu nguồn thay đổi, chỉ cần chuột phải vào bảng kết quả → Refresh, toàn bộ các bước làm sạch ở trên sẽ tự chạy lại trên dữ liệu mới.

Bảng các thao tác làm sạch thường dùng

Vấn đề dữ liệu	Thao tác trong Power Query
Dòng trống, dòng lỗi	Remove Rows → Remove Blank Rows / Remove Errors
Chữ hoa/thường không thống nhất	Transform → Format → Capitalize Each Word / UPPERCASE / lowercase
Khoảng trắng thừa	Transform → Format → Trim
Số bị lưu dạng văn bản	Transform → Data Type → Whole Number / Decimal Number
Một cột cần tách thành nhiều cột	Home → Split Column
Nhiều cột cần gộp thành một	Transform → Merge Columns
Nhiều file cùng cấu trúc cần gộp	Get Data → From Folder → Combine & Transform
Dữ liệu trùng lặp	Home → Remove Rows → Remove Duplicates

Lỗi thường gặp

Refresh xong dữ liệu bị lệch hoặc thiếu dòng Thường do cấu trúc file/dữ liệu nguồn mới đã thay đổi so với lần đầu tiên thiết lập các bước (ví dụ đổi tên cột, thêm cột mới). Mở lại Power Query Editor, kiểm tra khung Applied Steps xem bước nào báo lỗi để chỉnh lại cho khớp cấu trúc mới.

Cột ngày tháng bị đổi sai sau khi chuyển kiểu dữ liệu Date Do dữ liệu nguồn có định dạng ngày không đồng nhất (một số dòng kiểu dd/mm/yyyy, một số dòng kiểu yyyy-mm-dd) như ví dụ ở Bước 4. Nên kiểm tra và thống nhất định dạng ngày ngay từ nguồn trước khi import, thay vì để Power Query tự đoán.

Power Query chạy rất chậm với file dữ liệu lớn Nên hạn chế các bước không cần thiết trong Applied Steps (mỗi bước thừa đều làm chậm quá trình xử lý), và cân nhắc lọc bớt dữ liệu không cần thiết (ví dụ chỉ giữ vài cột cần dùng) ngay từ những bước đầu tiên thay vì để đến cuối mới lọc.

Không tìm thấy nút Refresh hoặc dữ liệu không tự cập nhật Refresh áp dụng cho bảng đã "Close & Load" ra sheet — nếu bạn copy-paste dữ liệu ra ngoài dạng giá trị tĩnh (paste special → values) thì liên kết với Power Query sẽ mất, không thể Refresh được nữa.

Khi nào nên dùng Power Query?

Nhận dữ liệu định kỳ từ hệ thống khác cần làm sạch trước khi báo cáo (bán hàng, chấm công, đơn hàng)
Gộp báo cáo từ nhiều file/nhiều tháng có cùng cấu trúc
Chuẩn hóa dữ liệu trước khi dựng PivotTable hoặc Dashboard, tránh sai số do dữ liệu bẩn
Thay thế các thao tác lặp đi lặp lại bằng tay mỗi kỳ báo cáo (tách cột, đổi định dạng, lọc dòng)

Học Excel thực chiến tại TP Vinh

Power Query giúp tiết kiệm rất nhiều thời gian xử lý dữ liệu thô trước khi làm báo cáo, đặc biệt hữu ích khi dữ liệu đến từ nhiều nguồn khác nhau. Nếu bạn muốn học Excel bài bản từ hàm cơ bản đến các công cụ xử lý dữ liệu nâng cao như Power Query, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh