2026-08-16 · Power Query & Excel
Cuối tháng, bạn có 12 file Excel từ 12 chi nhánh gửi về. Cùng một mẫu báo cáo bán hàng, cùng gửi qua một đường. Bạn thả cả 12 file vào một thư mục, mở Power Query, bấm Combine — và nhận về một trong ba kết cục quen thuộc: bảng ra thiếu mất mấy chi nhánh, có cột trắng toàn null, hoặc lỗi đỏ chói "The column 'Doanh thu' of the table wasn't found".
Lỗi không nằm ở bạn thao tác sai. Nó nằm ở chỗ nút Combine được thiết kế cho một tình huống mà file của bạn không thỏa mãn. Microsoft nói rất rõ trong tài liệu: bạn gộp được mọi file trong một thư mục miễn là chúng cùng loại file và cùng cấu trúc, bao gồm cùng các cột. Mà 12 chi nhánh thì chẳng bao giờ cùng cấu trúc.
Bài này chỉ cách xử lý khi các file khác cấu trúc: khác tên cột, thừa thiếu cột, khác thứ tự, tiêu đề không nằm ở dòng đầu. Cách làm không cần code phức tạp, và quan trọng hơn: nó không im lặng nuốt mất dữ liệu.
Vì sao nút Combine gãy khi file khác cấu trúc
Muốn sửa thì phải hiểu nó làm gì. Khi bạn bấm Combine, Power Query chạy một chuỗi việc tự động:
- Nó phân tích file mẫu — mặc định là file đầu tiên trong danh sách — để đoán xem nên dùng trình kết nối nào (Excel, CSV, JSON…).
- Nó tạo một truy vấn tên Transform Sample File, chứa toàn bộ các bước bóc dữ liệu ra khỏi đúng một file mẫu đó.
- Nó gói các bước ấy thành một hàm, rồi đem hàm đó áp cho tất cả các file còn lại.
Vấn đề nằm ở bước 3. Hàm được viết ra từ một file duy nhất, nhưng lại đem áp cho mọi file. File nào giống file mẫu thì qua. File nào lệch một chút là gãy.
Và "file đầu tiên" thường là file nào? Là file có tên xếp trước theo bảng chữ cái — hoàn toàn ngẫu nhiên so với chuyện file nào chuẩn nhất. Bạn đang để một file bất kỳ định nghĩa khuôn cho cả bộ dữ liệu.
Bốn kiểu "khác cấu trúc" hay gặp nhất
Trước khi sửa, hãy gọi đúng tên bệnh. Trong thực tế bốn kiểu sau chiếm gần hết:
- Khác tên cột cho cùng một thứ. Chi nhánh A ghi "Doanh thu", chi nhánh B ghi "Doanh số", chi nhánh C ghi "Thành tiền". Đây là kiểu phổ biến nhất và cũng nguy hiểm nhất, vì Power Query sẽ coi chúng là ba cột khác nhau.
- Thừa hoặc thiếu cột. Một chi nhánh tự thêm cột "Ghi chú", một chi nhánh khác không có cột "Chiết khấu" vì tháng đó không phát sinh.
- Khác thứ tự cột. Cột "Mã hàng" ở file này nằm thứ 2, ở file kia nằm thứ 5.
- Tiêu đề không nằm ở dòng đầu. File có logo công ty, dòng "BÁO CÁO BÁN HÀNG THÁNG 8", một dòng trống, rồi mới tới tiêu đề cột. Mỗi nơi chèn số dòng rác khác nhau.
Ba kiểu đầu xử lý được bằng cách chuẩn hóa tên cột và ép về bộ cột chuẩn. Kiểu thứ tư phải xử lý ngay từ bước đọc file.
Sáu bước gộp file khác cấu trúc
Bước 1 — Đừng bấm Combine, bấm Transform Data
Vào Data → Get Data → From File → From Folder, chọn thư mục chứa các file. Ở màn hình xem trước, không bấm Combine. Bấm Transform Data.
Bạn sẽ thấy một bảng danh sách file với các cột Content, Name, Extension, Date modified, Folder Path. Hãy lọc luôn ở đây: giữ đúng phần mở rộng bạn cần (.xlsx), bỏ các file tạm bắt đầu bằng ~$, bỏ thư mục con không liên quan. Giữ lại hai cột Content và Name — cột Name về sau chính là dấu vết để bạn truy ngược dòng nào đến từ file nào.
Bước 2 — Tự viết bước đọc từng file
Thêm một cột tùy chỉnh (Add Column → Custom Column) với công thức:
= Excel.Workbook([Content], true)
Tham số true thứ hai là nói "dòng đầu của mỗi vùng dữ liệu là tiêu đề, hãy dùng làm tên cột luôn". Kết quả trả về một bảng liệt kê mọi thứ có trong file: các Sheet, các Table, các Defined Name.
Mở rộng cột đó ra, rồi lọc theo cột Kind = "Sheet". Đây là mẹo quan trọng: đừng lọc theo tên sheet. Chi nhánh nào đó đổi tên sheet từ "Sheet1" thành "BC thang 8" là bộ lọc theo tên chết ngay, còn lọc theo Kind thì không.
Nếu file có dòng rác phía trên, đây là chỗ xử lý: thêm bước Table.Skip bỏ số dòng đầu, rồi Promote Headers. Trường hợp mỗi file có số dòng rác khác nhau, đừng đếm cứng — lọc bỏ các dòng mà cột đầu tiên rỗng trước, rồi mới promote.
Bước 3 — Chuẩn hóa tên cột bằng bảng ánh xạ
Đây là bước quyết định, và cũng là bước hầu hết mọi người bỏ qua. Bạn cần một bảng ánh xạ: mỗi dòng gồm "tên cột thực tế trong file" và "tên cột chuẩn".
| Tên thực tế trong file | Tên chuẩn |
|---|---|
| Doanh số | Doanh thu |
| Thành tiền | Doanh thu |
| Mã SP | Mã hàng |
| Mã sản phẩm | Mã hàng |
| Ngày CT | Ngày |
Bảng này để trong một sheet Excel riêng, kéo vào Power Query như một truy vấn. Sau đó đổi tên bằng:
= Table.RenameColumns(BangHienTai, DanhSachDoiTen, MissingField.Ignore)
Tham số MissingField.Ignore chính là chìa khóa. Tài liệu Microsoft ghi rõ: nếu cột cần đổi tên không tồn tại thì hàm sẽ báo lỗi, trừ khi bạn khai tham số này. Nhờ nó, cùng một danh sách đổi tên áp được cho cả 12 file dù mỗi file chỉ có vài cột trong danh sách.
Lợi ích lớn nhất của cách này: tháng sau có chi nhánh đặt tên cột kiểu mới, bạn chỉ thêm một dòng vào bảng ánh xạ trong Excel, không phải mở Power Query sửa bước nào.
Bước 4 — Ép về bộ cột chuẩn
Sau khi tên cột đã thống nhất, ép mọi bảng về đúng bộ cột bạn cần:
= Table.SelectColumns(BangHienTai, {"Ngày", "Mã hàng", "Số lượng", "Doanh thu"}, MissingField.UseNull)
MissingField.UseNull làm đúng việc bạn cần: cột nào file không có thì tạo ra một cột toàn null, thay vì làm hỏng cả truy vấn. Tài liệu Microsoft có ví dụ đúng tình huống này — chọn hai cột trong đó một cột không tồn tại, kết quả trả về cột đó với giá trị null.
Bước này còn giải luôn vấn đề khác thứ tự cột: các cột trong bảng kết quả nằm theo đúng thứ tự bạn liệt kê, bất kể chúng nằm ở đâu trong file gốc.
Bước 5 — Nối bằng Table.Combine, và hiểu nó làm gì
Giờ mới nối. Nhưng cần biết trước Table.Combine cư xử thế nào khi các bảng khác cấu trúc: nó lấy hợp của tất cả các cột, ô nào bảng gốc không có thì điền null. Ví dụ trong tài liệu Microsoft minh họa rất rõ — nối ba bảng có các cột Name/Phone, Fax/Phone, Cell thì kết quả có đủ bốn cột Name, Phone, Fax, Cell, các ô thiếu là null.
Nghĩa là nếu bạn bỏ qua bước 3 và 4, Table.Combine không báo lỗi — nó lặng lẽ đẻ ra bảng có "Doanh thu", "Doanh số", "Thành tiền" thành ba cột riêng, mỗi cột đầy null. Đây chính là kiểu hỏng nguy hiểm nhất: bảng trông vẫn chạy, cộng tổng vẫn ra số, chỉ có điều số đó sai.
Nếu muốn chắc chắn hơn nữa, Table.Combine có tham số thứ hai để khóa cứng khuôn:
= Table.Combine(DanhSachBang, type table [Ngày = date, Mã hàng = text, Số lượng = number, Doanh thu = number])
Khai như vậy thì bảng kết quả chỉ có bốn cột đó, không cột lạ nào chen được vào.
Bước 6 — Ba phép kiểm bắt buộc
Gộp xong đừng vội đóng. Ba phép kiểm này mất năm phút và cứu bạn khỏi phải giải trình cuối tháng:
- Đếm dòng theo file nguồn. Group By theo cột Name (tên file), đếm số dòng. Chi nhánh nào ra 0 dòng là file đó rơi mất — thường do sheet đặt tên lạ hoặc dòng rác chưa xử lý.
- Soi cột toàn null. Cột nào null gần hết là dấu hiệu tên cột chưa được ánh xạ. Đây là cái bẫy im lặng, phải chủ động đi tìm.
- Dò tên cột lạ. Trước bước 4, chụp lại danh sách tên cột thực tế của tất cả các file, đối chiếu với bảng ánh xạ. Tên nào chưa có trong bảng là tên bạn sắp mất dữ liệu.
Một tùy chọn nhỏ nhưng hay bị bỏ quên
Nếu bạn vẫn muốn dùng nút Combine cho bộ file tương đối đồng nhất, hộp thoại Combine files có hai thứ đáng để ý:
- Example file — một danh sách thả xuống cho phép bạn chọn file mẫu khác thay vì để mặc định file đầu tiên. Hãy chọn file đầy đủ cột nhất, không phải file may mắn đứng đầu.
- Skip files with errors — bỏ qua các file gây lỗi để phần còn lại vẫn chạy. Tiện, nhưng nguy hiểm nếu bạn không kiểm lại: file bị bỏ qua sẽ biến mất khỏi báo cáo mà không có tiếng động nào. Chỉ bật kèm phép kiểm đếm dòng theo file nguồn ở trên.
Câu hỏi thường gặp
Có bao nhiêu file thì nên chuyển sang cách này?
Không phải chuyện số lượng mà là chuyện nguồn. Nếu tất cả file do một người xuất ra từ một phần mềm, nút Combine dùng được kể cả với hàng trăm file. Nếu file do nhiều người khác nhau điền tay, hãy làm cách này ngay từ file thứ ba — vì sớm muộn cũng có người đổi tên cột.
Tên cột tiếng Việt có dấu có gây lỗi không?
Không, Power Query xử lý được tên cột tiếng Việt có dấu. Cái gây lỗi là khoảng trắng thừa ở đầu hoặc cuối tên cột — "Doanh thu " và "Doanh thu" là hai cột khác nhau, mà mắt thường không phân biệt được. Thêm một bước Text.Trim cho tên cột trước khi ánh xạ là né được hẳn nhóm lỗi này.
File có nhiều sheet, mỗi sheet một tháng thì làm sao?
Cách trên xử lý được luôn. Sau khi Excel.Workbook và lọc Kind = "Sheet", mỗi sheet là một dòng — bạn giữ cả cột tên sheet để biết dòng dữ liệu thuộc tháng nào, rồi mở rộng bình thường. Nếu chỉ cần gộp nhiều sheet trong một file, xem thêm bài gộp nhiều sheet Excel bằng Power Query.
Bảng ánh xạ để ở đâu cho tiện?
Để ngay trong file báo cáo, một sheet riêng tên "DanhMuc_Cot", định dạng thành Table của Excel. Như vậy người dùng cuối tự thêm dòng được mà không cần biết Power Query là gì — đúng tinh thần: phần cần sửa thường xuyên thì để ngoài, phần logic thì để trong.
Chốt lại
Nút Combine không sai, nó chỉ được thiết kế cho bộ file cùng cấu trúc. Khi các file khác nhau, bạn phải tự khai khuôn dữ liệu: chuẩn hóa tên cột bằng bảng ánh xạ, ép về bộ cột chuẩn bằng MissingField.UseNull, rồi mới nối. Cách này dài hơn vài bước, nhưng đổi lại nó không im lặng làm sai — và tháng sau bạn chỉ bấm Refresh.
Nếu bạn muốn học bài bản phần Power Query dùng cho dữ liệu kế toán — từ gộp file, chuẩn hóa danh mục đến dựng báo cáo tự động — khóa DATA FINANCE của đội ngũ EzTech đi thẳng vào nhóm bài toán này. Còn nếu bạn đang cần xem dữ liệu sau khi gộp có thể lên báo cáo tới đâu, ghé 41 báo cáo Power BI mẫu xem demo trực tiếp, hoặc liên hệ để được tư vấn theo đúng dữ liệu bạn đang có.
