Power Query là add-in tuyệt vời của Excel, giúp quá trình xử lý dữ liệu trở nên dễ dàng hơn. Bài viết này sẽ hướng dẫn bạn sử dụng Power Query tạo truy vấn nhập dữ liệu từ trang web đơn giản nhất.
Để thực hiện công việc này, bạn sẽ cần phải điều hướng khắp các bảng trên trang web và áp dụng các bước chuyển đổi để chỉ hiển thị dữ liệu muốn thấy. Power Query ghi lại tất cả các bước. Những bước đó sẽ được lặp lại bất cứ khi nào bạn làm mới dữ liệu.
Bước 1: Kết nối trang Wikipedia
Excel 2016: Click tab Data, sau đó tới New Query > From Other Sources > From Web. Nếu không thấy nút New Query, click Data > From Web.
Excel 2010-2013: Click tab Power Query, rồi tới From Web. Nếu không thấy tab Power Query, đảm bảo bạn đã tải và cài đặt add-in Power Query.
1. Trong hộp thoại From Web, dán URL Wikipedia (http://en.wikipedia.org/wiki/UEFA_European_Football_Championship) vào text box URL.
2. Click OK.
Sau khi thiết lập kết nối tới trang web, bạn sẽ thấy một danh sách các bảng biểu có sẵn trên trang Wikipedia trong box Navigator. Bạn có thể click vào từng bảng để mở cửa sổ xem trước nhanh, được hiển thị dưới dạng bảng ở bên phải.
3. Click đúp bảng Results[edit], Query Editor sẽ mở ra kèm dữ liệu giải đấu.
Bước 2: Định dạng dữ liệu
Giờ bạn đã mở bảng trong Query Editor. Bạn có thể lọc và định dạng dữ liệu phù hợp nhu cầu. Trong bước này, bạn sẽ định hình lại cấu trúc dữ liệu bằng cách xóa tất cả cột ngoại trừ Year và Final Winers.
1. Trong ô lưới Query Preview, sử dụng Ctrl+Click để chọn cột Year và Final Winners.
2. Click Remove Columns > Remove Other Columns.
Bước 3: Dọn dẹp dữ liệu
Bạn sẽ thực thi nhiệm vụ này bằng cách thay thế các giá trị và lọc dữ liệu.
1. Chọn cột Year.
2. Trong Query Editor, click Replace Values.
3. Trong hộp thoại Replace Values, gõ “Details” trong text box Value to Find và bỏ trống Replace With.
4. Click OK.
Bước 4: Lọc các giá trị trong cột
Giờ bạn sẽ lọc cột Year để hiển thị các hàng không chứa năm.
1. Click mũi tên thả xuống của bộ lọc trên cột Year.
2. Trong Filter thả xuống, bỏ tích Year.
3. Click OK.
Bước 5: Đặt tên truy vấn
Giờ đã tới lúc đặt tên truy vấn đang tạo. Trong bảng Query Settings, ở text box Name, nhập Euro Cup Winners. Nếu click liên kết All Properties, bạn cũng có thể nhập mô tả truy vấn trong text box Description.
Bước 6: Tải truy vấn vào worksheet
Cuối cùng, bạn có thể tải truy vấn Euro Cup Winners vào trang tính.
Ở góc bên trái phía trên, click Close &Load. Power Query sẽ trả kết quả truy vấn vào bảng tính. Nếu cần cập nhật dữ liệu sau đó, hãy click chuột phải vào vị trí bất kỳ trong phạm vi dữ liệu, rồi nhấn Refresh. Mọi thông tin mới từ trang web sẽ được cập nhật tự động.
Bước 7: Khám phá nội dung phía sau
Click vào vị trí bất kỳ trong dữ liệu và trên tab Query, click Edit.
Ở bên phải, chú ý tới danh sách Applied Steps. Khi đã thực hiện các tác vụ truy vấn, các bước truy vấn dữ liệu đã được tạo. Mỗi bước có công thức riêng, được viết bằng ngôn ngữ “M”.
Dưới đây là bảng giải thích chi tiết từng bước:
| Bước truy vấn | Tác vụ | Công thức |
| Source | Kết nối tới nguồn dữ liệu web | = Web.Page(Web.Contents(“http://en.wikipedia.org/wiki/UEFA_European_Football_Championship”)) |
| Navigation | Chọn bảng để kết nối | = Source{2}[Dữ liệu] |
| Changed Type | Thay đổi kiểu – Power Query tự đọng thực thi nhiệm vụ này | = Table.TransformColumnTypes(Data2,{{“Year”,gõ text}, {“Host”, gõ text}, {“”, gõ text}, {“Final Winner”, gõ text}, {“Final Score”, gõ text}, {t”Final Runner-up”, gõ text}, {“2”, gõ text}, {“Third place match Third place”, gõ text}, {“Third place match Score”, gõ text}, {“Third place match Fourth place”, gõ text}, {“3”, gõ text}, {“Number of teams”, gõ text}}) |
| RemovedOtherColumns | Xóa cột để chỉ hiển thị cột mong muốn |
Table.SelectColumns = Table.SelectColumns(#”Changed Type”,{“Final Winner”, “Year”}) |
| ReplacedValue | Thay thế giá trị trong cột lựa chọn |
Table.ReplaceValue = Table.ReplaceValue(#”Removed Other Columns”,”Details”,””,Replacer.ReplaceText,{“Year”}) |
| FilteredRows | Lọc các giá trị trong cột |
Table.SelectRows = Table.SelectRows(#”Replaced Value”, each ([Year] <> “Year”)) |
Lưu ý: Bạn có thể xem toàn bộ code M trong tùy chọn Advanced Editor ở ribbon Power Query.
Hi vọng bài viết hữu ích với các bạn!
