Hướng dẫn sử dụng Power Query 101

Bình luận

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"))