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

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.

  • Microsoft Excel
  • Microsoft Excel cho Android
  • Microsoft Excel cho iOS
  • Microsoft Excel Online

Để 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.

Ô dán URL web

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.

Bảng điều hướng của Power Query

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ừ YearFinal Winers.

1. Trong ô lưới Query Preview, sử dụng Ctrl+Click để chọn cột YearFinal Winners.

2. Click Remove Columns > Remove Other Columns.

Chỉnh sửa kết quả truy vấn

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.

Thay thế các giá trị như ý muốn

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.

Lọc giá trị trong cột

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.

Cài đặt Power Query

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!

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *