Cách so sánh 2 bảng bằng Power Query trong Excel

Khi làm việc với các bảng dữ liệu, nhân viên thường phải so sánh hai bảng với nhau nếu các giá trị giống nhau, hoặc khác nhau, hoặc có thể được liên kết và so sánh bằng một số cách khác. Thao tác so sánh này có thể được thực hiện một cách dễ dàng nhờ vào Power Query. Trong bài viết này, UniTrain sẽ hướng dẫn bạn cách so sánh 2 bảng bằng Power Query dựa trên ví dụ dưới đây:

Giả sử có tập dữ liệu Students Record gồm Student ID và Name (như hình dưới):

Và  tập dữ liệu Passed Students Record ở Term 1 và Term 2 (như hình dưới):

1. So sánh 2 bảng và hợp nhất tất cả giá trị

Đầu tiên, người dùng cần tìm danh sách những sinh viên đã vượt qua ít nhất một học kỳ bằng cách so sánh và hợp nhất các giá trị của hai bảng. Bạn có thể thao tác qua Power Query theo các bước dưới đây:

Bước 1: Nhấn CTRL+T để tạo bảng.

Sau đó, tập dữ liệu Students Record sẽ được chuyển thành bảng (như hình dưới):

Bước 2: Nhấp vào bất kỳ giá trị nào trong bảng để đặt tên cho bảng > chọn Table Design > nhập Students trong Table Name.

Tương tự, tạo bảng cho tập dữ liệu Passed Students Record (như hình dưới).

Bước 3: Chọn Data > From Table/Range.

Bước 4: Chọn Home > Close & Load > Close & Load to…

Bước 5: Cửa sổ Import Data hiển thị, chọn Only Create Connection > OK.

Bước 6: Thao tác tương tự với tập dữ liệu còn lại, chọn Data > From Table/Range. 

Bước 7: Nhấp chuột phải vào ngăn Queries >> chọn tùy chọn New Query > Other Sources > Blank Query.

Bước 8: Một truy vấn mới sẽ được tạo và đổi tên thành “Merge All Values“.

Bước 9: Chọn Merge All Values và chèn công thức dưới đây để so sánh hai bảng và hợp nhất các giá trị thông qua truy vấn nguồn.

=List.Union({Term_1[Name],Term_2[Name]},Comparer.OrdinalIgnoreCase)

Cuối cùng, bạn đã so sánh và hợp nhất hai bảng và lọc được học sinh đậu ít nhất một học kỳ.

Bước 10: Để lấy ID, hãy chọn Query Settings ở bên phải > Applied Steps > nhấp chuột phải vào Source> chọn Insert Step After.

Sau đó, thao tác mới sẽ được thêm. Hãy đổi tên thành “Insert IDs“.

Bước 11: Chọn bước Insert IDs và chèn công thức dưới đây vào thanh công thức.

=Table.SelectRows(Students,eachList.ContainsAny({[Name]},Source))

Kết quả sẽ hiển thị (như hình dưới):

2. So sánh 2 bảng bằng Power Query và tìm các giá trị chung

Bước 1: Tạo một truy vấn mới với tên là “Find Common Values“.

Bước 2: Chọn truy vấn Find Common Values và nhập công thức dưới đây:

=List.Intersect({Term_1[Name],Term_2[Name]},Comparer.OrdinalIgnoreCase )

Sau đó, bạn sẽ nhận được danh sách học sinh đậu cả 2 kỳ.

Bước 3: Để thêm IDs, chọn Query Settings > Applied Steps > nhấp chuột phải vào Source > chọn Insert Step After.

Bước 4: Khi bước mới được tạo ra, đổi tên thành “Insert IDs“.

Bước 5: Chọn Insert IDs và chèn công thức dưới đây:

=Table.SelectRows(Students,each List.ContainsAny({[Name]},Source))

Cuối cùng, kết quả sẽ hiển thị (như hình dưới):

Xem thêm

Khóa học Advanced Power Query 

Cách xóa khoảng cách trong Excel bằng Power Query

Cách thay thế giá trị trong Power Query

Học Power Query có lợi như thế nào?