Giả sử có tập dữ liệu về Student’s Records, gồm Student ID và Name (tên học sinh) và một tập dữ liệu về Passed Students Records, gồm Term-1 và Term-2. Người dùng cần so sánh tập dữ liệu Passed Students Records của 2 học kỳ và tìm ra những học sinh đã đậu trong 2 học kỳ và những học sinh đã đậu ít nhất một học kỳ. Hãy cùng UniTrain thao tác so sánh 2 bảng bằng Excel Power Query nhé!
So sánh hai bảng và hợp nhất tất cả giá trị bằng Power Query
Trước tiên, người dùng cần tìm ra những sinh viên đậu ít nhất một học kỳ. Đồng nghĩa với việc phải so sánh và hợp nhất các giá trị của hai bảng. Bạn có thể so sánh hai bảng và hợp nhất tất cả các giá trị bằng Power Query như sau:
Bước 1: Chuyển đổi tất cả các phạm vi tập dữ liệu thành các bảng bằng cách chọn bất kì ô nào trong phạm vi bảng Students Record > chọn Insert trên thanh công cụ > Tables > tùy chọn Table.
Bước 2: Cửa sổ Create Table sẽ hiển thị, chọn phạm vi là B4:C14 > chọn My table has headers > chọn OK.
Sau đó, tập dữ liệu Students Record được chuyển thành dạng bảng Excel.
Bước 4: Để đặt tên cho bảng này, hãy nhấp vào bất kỳ giá trị nào bên trong bảng > Chọn Table Design > ghi Students vào hộp văn bản Table Name.
Bước 5: Tạo thêm hai bảng với tên là Term_1 và Term_2 từ hồ sơ học sinh đã vượt qua của từng học kỳ.
Bước 6: Để đến cửa sổ Power Query, nhấp chuột phải vào bất kỳ ô nào bên trong bảng Students Record và chọn Get Data from Table/Range.
Sau đó, bảng Students sẽ hiển thị trong Power Query.
Bước 7: Chọn Home trên thanh công cụ > chọn Close & Load > chọn tùy chọn Close & Load To…
Bước 8: Khi cửa sổ Import Data sẽ hiển thị, hãy chọn Only Create Connection > OK.
Thao tác tương tự cho các bảng Term_1 và Term_2 để nhập dữ liệu từ các bảng trong Power Query.
Bước 9: Nhấp chuột phải vào ngăn Queries > chọn tùy chọn New Query > Other Sources > Blank Query.
Sau đó, một truy vấn mới sẽ được tạo ra, hãy đổi tên thành “Merge All Values“.
Bước 10: Nhấp vào truy vấn Merge All Values và chèn công thức để so sánh hai bảng và hợp nhất các giá trị thông qua truy vấn nguồn.
Công thức: =List.Union({Term_1[Name],Term_2[Name]},Comparer.OrdinalIgnoreCase)
Bước 11: Nhấp Enter.
Như vậy, bạn đã so sánh và hợp nhất hai bảng và có được danh sách những học sinh đậu ít nhất một học kỳ.
Bước 12: Để lấy IDs, chọn ngăn Query Settings phía bên phải > APPLIED STEPS > nhấp chuột phải vào tùy chọn Source > chọn Insert Step After.
Do đó, một bước mới sẽ được thêm vào.
Bước 13: Đổi tên thành “Insert IDs”.
Bước 14: Nhấp vào Insert IDs và chèn công thức sau:
=Table.SelectRows(Students,eachList.ContainsAny({[Name]},Source))
Bước 15: Nhấn Enter. Bạn sẽ nhận được IDs của học sinh cho các giá trị được hợp nhất tương ứng.
Cuối cùng, bạn đã so sánh và hợp nhất hai bảng trong Power Query thành công và kết quả sẽ hiển thị như hình dưới đây:
Xem thêm
Cách xóa hàng bị lỗi bằng Power Query