Để làm việc tương tự VLOOKUP trong Power Query, bạn dùng Merge Queries để ghép bảng theo mã chung, rồi mở rộng cột cần lấy. Với nhu cầu giữ toàn bộ giao dịch và bổ sung tên sản phẩm, hãy chọn Left Outer; trước khi mở rộng, cần kiểm tra mã trùng ở bảng danh mục để tránh nhân dòng.
Bài này hướng dẫn trên Excel với dữ liệu bán hàng giả lập, có code M tự chứa dữ liệu và kết quả đối soát. Bạn cũng có thể áp dụng cách ghép tương tự trong Power Query Editor của Power BI.
VLOOKUP trong Power Query khác công thức Excel thế nào?
Power Query dùng ngôn ngữ M, không nhận công thức VLOOKUP của worksheet. Merge là phép ghép hai bảng theo giá trị khoá; bảng giao dịch nên nằm bên trái, danh mục tra cứu nằm bên phải. Khi danh mục có một dòng cho mỗi mã, bạn lấy được thuộc tính giống nhu cầu dò tìm chính xác trong Excel.
Điểm cần nhớ: Merge có thể trả về nhiều dòng khớp cho một mã, còn VLOOKUP dò chính xác thường lấy kết quả khớp đầu tiên. Vì vậy, chuyển công thức sang Merge không có nghĩa là kết quả luôn giống nhau. Bài này chỉ xử lý dò chính xác, không thay thế cách dò gần đúng theo bậc doanh số hoặc khoảng giá.
Nếu đang phân vân giữa nối thêm giao dịch và lấy thêm thuộc tính, bạn có thể đọc phần phân biệt Merge và Append trong Power Query. Ở đây, mục tiêu cụ thể là thêm tên và nhóm sản phẩm vào từng dòng bán hàng.
Chuẩn bị hai bảng và xác định kết quả đúng
Tạo hai Excel Table bằng Ctrl+T rồi đặt tên BanHang và DanhMuc trong Table Design. Các mã sản phẩm cần lưu dạng Text ở cả hai bảng. Không dùng tên sản phẩm làm khoá nếu doanh nghiệp đã có mã, vì tên dễ đổi hoặc có cách viết khác nhau.
| MaDon | MaSP | ThanhTien |
|---|---|---|
| HD001 | CF01 | 300000 |
| HD002 | TR01 | 200000 |
| HD003 | CF01 | 150000 |
| HD004 | BK99 | 100000 |
Trong DanhMuc, nhập hai dòng: CF01 – Cà phê – Đồ uống và TR01 – Trà – Đồ uống, tương ứng ba cột MaSP, TenSP, Nhom. Chưa thêm BK99 để chủ động kiểm tra trường hợp không tìm thấy mã.
Kết quả đúng phải giữ bốn giao dịch, tổng Thành tiền là 750.000 đồng. HD001 và HD003 nhận tên Cà phê; HD002 nhận tên Trà; HD004 vẫn tồn tại nhưng thuộc tính lấy từ danh mục là null. Đặt đáp án trước khi thao tác giúp bạn phát hiện sai lệch ngay.
Các bước thay VLOOKUP bằng Merge Queries
1. Đưa từng bảng vào Power Query
Chọn một ô trong BanHang, vào Data → From Table/Range. Kiểm tra kiểu MaSP là Text, ThanhTien là số rồi dùng Close & Load To để tạo kết nối nếu chưa cần nạp bảng trung gian. Làm tương tự với DanhMuc. Tên lệnh có thể hiển thị khác theo ngôn ngữ giao diện.
2. Làm sạch khoá ở cả hai phía
Trong mỗi truy vấn, chọn MaSP rồi dùng Transform → Format → Trim và Clean để xử lý khoảng trắng đầu cuối và một số ký tự điều khiển. Với quy ước mã không phân biệt hoa thường, chuẩn hoá thêm Uppercase ở cả hai bảng. Không tự đổi hoa thường nếu mã của hệ thống phân biệt điều đó.
Giữ mã có số 0 đầu dưới dạng Text từ lúc nhập nguồn. Chuyển số 123 thành Text sau đó không khôi phục được mã 00123 đã mất. Cũng đừng xoá mọi khoảng trắng bên trong mã khi chưa biết quy tắc nghiệp vụ.
3. Ghép và mở rộng thuộc tính
Chọn truy vấn BanHang, vào Home → Merge Queries → Merge Queries as New. Chọn BanHang ở trên, DanhMuc ở dưới, bấm cột MaSP của từng bảng rồi chọn Left Outer. Không bật fuzzy matching cho mã sản phẩm cần khớp chính xác.
Sau khi xác nhận, cột bảng lồng xuất hiện. Bấm biểu tượng mở rộng ở tiêu đề cột, chỉ chọn TenSP và Nhom. Có thể bỏ tuỳ chọn dùng tên cột gốc làm tiền tố nếu tên kết quả không trùng cột hiện có. Kiểm tra bốn giao dịch rồi mới Close & Load.
Code M tự chạy với dữ liệu bán hàng mẫu
Tạo Blank Query, mở Advanced Editor và thay toàn bộ nội dung bằng đoạn dưới. Ví dụ không cần tệp bên ngoài; sau khi hiểu kết quả, bạn mới thay hai bảng mẫu bằng các truy vấn nguồn thực tế.
let
BanHang = #table(
type table [MaDon = text, MaSP = text, ThanhTien = number],
{
{"HD001", "CF01", 300000},
{"HD002", "TR01", 200000},
{"HD003", "CF01", 150000},
{"HD004", "BK99", 100000}
}
),
DanhMuc = #table(
type table [MaSP = text, TenSP = text, Nhom = text],
{
{"CF01", "Cà phê", "Đồ uống"},
{"TR01", "Trà", "Đồ uống"}
}
),
Ghep = Table.NestedJoin(
BanHang, {"MaSP"}, DanhMuc, {"MaSP"},
"TraCuu", JoinKind.LeftOuter
),
DemKhop = Table.AddColumn(
Ghep, "SoDongKhop",
each Table.RowCount([TraCuu]), Int64.Type
),
KetQua = Table.ExpandTableColumn(
DemKhop, "TraCuu", {"TenSP", "Nhom"}, {"TenSP", "Nhom"}
)
in
KetQuaCột SoDongKhop giữ dấu vết kiểm tra: CF01 và TR01 có giá trị 1, BK99 có giá trị 0. Nếu một giao dịch có SoDongKhop lớn hơn 1, hãy dừng đối soát trước khi dùng tổng tiền. Đây là cột chẩn đoán, không phải chỉ tiêu kinh doanh cần đưa lên dashboard.
Hai hàm ghép và mở rộng được mô tả trong tài liệu Table.NestedJoin và Table.ExpandTableColumn của Microsoft. Việc đếm số dòng khớp trong ví dụ là bước kiểm soát thêm cho bài toán tra danh mục.
Vì sao Merge làm doanh thu tăng dù không thêm đơn?
Thử thêm dòng CF01 – Cà phê rang – Đồ uống vào DanhMuc. Mỗi giao dịch CF01 lúc này tìm được hai dòng danh mục. Sau mở rộng, HD001 và HD003 đều xuất hiện hai lần: kết quả thành sáu dòng, tổng tiền tăng lên 1.200.000 đồng dù nguồn bán hàng vẫn chỉ có 750.000 đồng.
Đừng xoá trùng tuỳ tiện để ép số dòng trở lại. Nếu hai dòng khác nhau vì phiên bản sản phẩm hoặc thời điểm áp dụng, bạn cần quy tắc chọn đúng bản ghi. Nếu chúng thực sự là bản sao hoàn toàn, mới loại bản sao theo quy tắc đã thống nhất.
Để tìm mã có nhiều dòng, nhóm DanhMuc theo MaSP và đếm số dòng, rồi lọc các nhóm lớn hơn 1. Bạn có thể tham khảo cách Group By để đếm và tổng hợp dữ liệu. Bảng bán hàng có nhiều dòng cùng mã là bình thường; bảng danh mục mới cần duy nhất theo khoá tra cứu đã chọn.
Dò nhiều điều kiện và xử lý mã không tìm thấy
Nếu cùng mã sản phẩm có nhiều quy cách, hãy chọn đồng thời MaSP và QuyCach khi Merge. Giữ Ctrl và chọn hai cột theo cùng thứ tự ở hai bảng; trong code M, danh sách khoá ở cả hai phía sẽ là {"MaSP", "QuyCach"}. Kiểu dữ liệu của từng cặp cột cũng phải tương ứng.
Với dòng chưa khớp, lọc SoDongKhop bằng 0 để lập danh sách cần bổ sung danh mục. Chỉ lọc TenSP bằng null có thể nhầm với trường hợp đã khớp nhưng tên trong danh mục vốn để trống. Không đổi ngay null thành số 0: không tìm thấy đơn giá khác với đơn giá hợp lệ bằng 0.
Khi refresh, kiểm tra lại số dòng, tổng tiền, số giao dịch chưa khớp và số giao dịch khớp nhiều dòng. Thử thêm một mã mới vào danh mục rồi Refresh All để xác nhận nó được nhận diện. Không sửa trực tiếp bảng kết quả vì lần làm mới tiếp theo có thể ghi đè thay đổi đó.
Câu hỏi thường gặp
Có gõ VLOOKUP vào Custom Column được không?
Không dùng nguyên công thức VLOOKUP của Excel trong Custom Column. Với nhu cầu lấy thuộc tính từ bảng khác theo mã, dùng Merge rồi mở rộng cột sẽ dễ kiểm tra hơn.
Merge có bắt cột mã nằm đầu bảng không?
Không. Bạn chọn cột khoá ở vị trí phù hợp trong mỗi bảng. Tên hai cột có thể khác nhau, nhưng giá trị và kiểu dữ liệu cần tương thích để ghép đúng.
Left Outer có bảo đảm số dòng luôn giữ nguyên không?
Nó giữ các dòng bên trái, nhưng khi mở rộng nhiều kết quả khớp, số dòng có thể tăng. Muốn lấy một thuộc tính cho mỗi giao dịch, danh mục cần duy nhất theo khoá.
Vì sao sửa nguồn mà tên sản phẩm chưa đổi?
Kiểm tra bạn đã làm mới đúng truy vấn, nguồn có bao gồm dòng mới và các bước lọc có loại dòng đó không. Nếu mã vẫn chưa khớp, kiểm tra kiểu dữ liệu, khoảng trắng và quy ước mã.
Nếu bạn cần người xây luồng tra cứu, đối soát và báo cáo Power BI theo dữ liệu doanh nghiệp, có thể xem báo giá làm Power BI trên SenData để chuẩn bị phạm vi công việc phù hợp.
Sẵn sàng xây dựng dashboard Power BI?
Kết nối với freelancer Power BI phù hợp, xem báo cáo nhúng ngay trên trình duyệt và tải file .pbix về sau khi thanh toán.
Đào Ngọc Tú
Cộng tác viên chuyên gia tại SenData. Chia sẻ kiến thức về phát triển Power BI, trực quan hóa dữ liệu và best practice business intelligence.
