Power Query – Nhập dữ liệu từ Web

Web là một kho chứa thông tin cực kỳ đồ sộ. Tuy nhiên, việc lấy dữ liệu không phải lúc nào cũng dễ dàng Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách chúng ta có thể nhập dữ liệu từ web vào Power Query.

Có thể bạn sẽ thấy khá phấn khích với ý nghĩ thu thập dữ liệu từ mọi nơi chỉ bằng một cú nhấp chuột, nhưng thực sự thì không hẳn. Power Query có thể làm được nhiều việc, nhưng trang web phải ở định dạng hợp lý mới sử dụng được.

Hãy bắt đầu nhập một số dữ liệu từ web.

Xem nhanh

Nhập web cơ bản

Với mục đích của bài viết, chúng ta sẽ sử dụng https://www.xe.com để nhập tỷ giá hối đoái (exchange rates) vào Power Query.

Trang web này hiển thị tỷ giá hối đoái cho bất kỳ loại tiền tệ nào vào bất kỳ ngày nào. Dưới đây là tỷ giá USD cho ngày 1 tháng 7 năm 2019.

1-power query - Nhập dữ liệu từ web — 1-power query – Nhập dữ liệu từ web

Phần đáng chú ý nhất trong ảnh chụp màn hình ở trên là URL. Nếu bạn nhìn kỹ, nó chứa mã tiền tệ và ngày tháng trong URL. Hmmm… Mình tự hỏi liệu chúng ta có thể sử dụng nó theo cách nâng cao hơn không?

https://www.xe.com/currencytables/?from=USD&date=2019-07-01

Tuy nhiên trước khi bắt đầu nâng cao hơn, hãy bắt đầu từ những điều cơ bản. Sao chép URL, sau đó trong Excel nhấp vào Data -> Get Data -> From Other Sources -> From Web

2-power query - Nhập dữ liệu từ web — 2-power query – Nhập dữ liệu từ web

Cửa sổ From Web sẽ mở ra. Dán URL mà chúng ta đã sao chép trước đó vào hộp URL và nhấp vào OK.

3-power query - Nhập dữ liệu từ web — 3-power query – Nhập dữ liệu từ web

Cửa sổ tiếp theo xuất hiện sẽ là cửa sổ Navigator. Vì các bảng từ các trang web có thể có quy ước đặt tên kém, nên không phải lúc nào chúng ta cũng nhận biết được bảng nào. Chúng ta phải nhấp qua từng cái cho đến khi tìm được cái mà chúng ta muốn. Khi bạn đã tìm thấy bảng phù hợp, hãy nhấp vào Transform Data.

4-power query - Nhập dữ liệu từ web — 4-power query – Nhập dữ liệu từ web

Khi tải Power Query Editor, nó sẽ chứa tất cả dữ liệu tiền tệ.

5-power query - Nhập dữ liệu từ web — 5-power query – Nhập dữ liệu từ web

Tiếp theo hãy đặt tên hữu ích cho truy vấn, chẳng hạn như FXRates

Nhấp vào Home -> Close and Load để đẩy dữ liệu vào Excel. Thật dễ dàng đúng không?

6-power query - Nhập dữ liệu từ web — 6-power query – Nhập dữ liệu từ web

Nếu trang web chứa dữ liệu trực tiếp, nó sẽ cập nhật truy vấn với dữ liệu trực tiếp đó mỗi khi chúng ta nhấp vào làm mới. Đó là công cụ khá mạnh mẽ.

Chỉnh sửa truy vấn

Bây giờ chúng ta hãy chỉnh sửa truy vấn để sử dụng nó cho các ngày và đơn vị tiền tệ khác nhau (khi thực hiện việc này, chúng ta sẽ gặp lỗi cần sửa; mình sẽ giúp bạn xử lý nó).

Trong Excel, bấm Data -> Queries and Connections

Bấm đúp vào truy vấn trong ngăn Queries & Connections để mở trình soạn thảo Power Query.

Trình chỉnh sửa Power Query sẽ mở lại, nhấp vào bước Source trong Applied Steps. URL được mã hóa vào truy vấn. Lúc này hãy chỉnh sửa mã M trong thanh công thức, thay đổi truy vấn để bao gồm một đơn vị tiền tệ khác và một ngày khác. Đối với ví dụ của chúng ta, mình đã chọn EUR và ngày 1 tháng 1 năm 2019.

Mã M sau:

= Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=USD&date=2019-07-01“))

đổi thành

= Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=EUR&date=2019-01-01“))

Nhấp qua các bước còn lại của truy vấn. Bước Thay đổi Loại (Changed Type step) sẽ hiển thị lỗi sau.

Nhìn vào mã M trong Thanh Công thức bạn sẽ thấy có các tham chiếu cụ thể đến đơn vị tiền tệ USD trong tiêu đề (xem các phần được đánh dấu màu đỏ bên dưới).

= Table.TransformColumnTypes(Data0,{{“Currency code ▲▼”, type text},

{“Currency name ▲▼”, type text}, {“Units per USD“, type number}, {“USD per Unit“, type number}})

Đơn vị tiền tệ đã chọn của chúng ta hiện là EUR; tiêu đề cột sẽ trở thành Units per EUR và EUR per Unit.

Sự thay đổi trong tiêu đề cột đến trực tiếp từ trang web nguồn; do đó chúng ta không thể làm gì để ngăn chặn nó. Tuy nhiên, có những điều chúng ta có thể giải quyết vấn đề.

Xóa bước Change Type đang gây ra lỗi.
Giảm hạng tiêu đề xuống hàng đầu tiên bằng cách nhấp vào Transform -> User First Row as Headers (dropdown) -> Use Headers as First Row.
Xóa hàng trên cùng bằng cách nhấp vào Home -> Remove Rows -> Remove Top Rows. Nhập 1 vào hộp Number, bấm OK.
Nhập các tên cột phù hợp theo cách thủ công (Ta đã sử dụng Code, Currency, Rate and Inverted Rate).
Áp dụng các loại dữ liệu phù hợp cho từng cột (Ta đã sử dụng Text, Text, Decimal, Decimal)

Cửa sổ Xem trước bây giờ trông giống như sau:

Bấm Close & Load để đẩy truy vấn vào Excel. Bây giờ chúng ta có thể thay đổi đơn vị tiền tệ và ngày tháng trong URL nguồn, nghĩa là chúng ta có thể nhận được tỷ giá cho bất kỳ ngày nào chúng ta muốn.

Sử dụng các tham số với truy vấn từ web

Việc chỉnh sửa mã M mỗi khi chúng ta muốn nhập các ngày và tỷ lệ khác nhau không phải là lý tưởng hay. Việc này tốn khá nhiều thời gian, ngoài ra chúng ta không biết chắc rằng những người dùng khác không biết gì về Power Query cập nhật bước này có chính xác hay không.

Nếu bạn đang theo dõi loạt bài viết về Power Query này, bạn sẽ nhớ rằng trước đây chúng ta đã tạo các tham số để quản lý các biến. Nếu thử điều này với các truy vấn web, chúng ta sẽ gặp một lỗi lạ (xem ảnh chụp màn hình bên dưới).

11- Nhập dữ liệu power query

Nhưng đừng lo lắng, vẫn còn một cách khác để sử dụng các ô trên bảng tính để truy xuất thông tin chúng ta muốn.

Sử dụng công thức tùy chỉnh(custom formulas)

Trong một bài viết trước, chúng ta đã kết hợp tất cả các tệp từ một thư mục. Để đạt được điều này, chúng ta đã sử dụng hàm Excel.Workbook () để kết hợp bất kỳ tệp nào tình cờ có trong thư mục. Trong bài này bạn có thể tạo hàm của riêng mình thay vì kết hợp các tệp và sẽ trả về các truy vấn web dựa trên URL mà chúng bạn cung cấp cho nó.

Hãy bắt đầu với một bảng mới (mình đã gọi là bảng tblURLs). Nó chứa các giá trị sau:

Code
Date
URL
12- Nhập dữ liệu power query

Công thức trong Ô C2 được xây dựng bằng cách ghép mã đơn vị tiền tệ và ngày tháng vào cấu trúc URL.

=”https://www.xe.com/currencytables/?from=”&[@Code]&”&date=”&TEXT([@Date],”yyyy-mm-dd”)

Tiếp theo, hãy thêm bảng này vào Power Query. Chọn một ô bất kì bên trong bảng, sau đó chọn Data -> From Table/Range.

Bây giờ tất cả đã sẵn sàng để tạo một chức năng tùy chỉnh.(custom function)

Tạo chức năng

Hãy quay lại truy vấn ban đầu bằng cách nhấp vào truy vấn FXRates từ danh sách truy vấn.

Chúng ta sẽ thay đổi truy vấn này thành một chức năng có thể tùy chỉnh, nhấp vào Home -> Advanced Editor để hiển thị Mã M.

Thay đổi mã M như sau:

(1) Thêm mã sau vào đầu truy vấn:

(URL) as table =>

(2) Thay đổi URL nguồn trong mã thành URL chữ cái, là biến mà chúng ta đã tạo ở trên.

Mã M bây giờ sẽ như sau (những phần được đánh dấu là những phần chúng ta đã thay đổi).

(URL) as table =>

let

Source = Web.Page(Web.Contents(URL)),

Data0 = Source{0}[Data],

#”Demoted Headers” = Table.DemoteHeaders(Data0),

#”Changed Type” = Table.TransformColumnTypes(#”Demoted Headers”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}}),

#”Removed Top Rows” = Table.Skip(#”Changed Type”,1),

#”Renamed Columns” = Table.RenameColumns(#”Removed Top Rows”,{{“Column1”, “Code”}, {“Column2”, “Currency”}, {“Column3”, “Rate”}, {“Column4”, “Inverted Rate”}}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Rate”, type number}, {“Inverted Rate”, type number}, {“Code”, type text}, {“Currency”, type text}})

#”Changed Type1″

Nhấp vào Done trong cửa sổ Advanced Editor để chấp nhận các thay đổi.

Cửa sổ Power Query sẽ thay đổi thành như sau:

Trong danh sách các truy vấn, nó hiển thị truy vấn FX Rate mà chúng ta vừa thay đổi với một fx bên cạnh nó, điều này có nghĩa là nó là một hàm.

Sử dụng chức năng

Mở truy vấn tblURLs.

Nhấp vào Add Column -> Custom Column

Trong cửa sổ Custom Column, nhập thông tin sau:

Tên cột mới: Imported FX Rates

Công thức cột tùy chỉnh: =FXRate([URL])

Sau đó bấm OK.

Nhấp vào Continue trên cảnh báo quyền riêng tư.

16- Nhập dữ liệu power query

Trên cửa sổ Privacy Levels, chọn Ignore Privacy Levels, sau đó bấm Save.

Nhấp vào biểu tượng mở rộng ở đầu cột Imported FX Rates (cột mới chúng ta vừa thêm). Bỏ chọn hộp Use original column name as prefix box, sau đó bấm OK.

Dữ liệu từ XE.com đã được nhập vào truy vấn

Thu gọn truy vấn bằng cách:

Thay đổi kiểu dữ liệu của từng cột
Xóa các cột chúng ta không cần

Nhấp vào Close & Load để đẩy thông tin vào Excel.

Đã đến lúc kiểm tra điều này để chứng minh rằng chúng ta có thể nhận được tỷ giá hối đoái cho bất kỳ loại tiền tệ nào và bất kỳ ngày nào. Trong Excel, có thể thay đổi ngày hoặc mã đơn vị tiền tệ trong Bảng tblURL. Nhấp vào Data -> Refresh All, tỷ giá hối đoái từ truy vấn bây giờ sẽ được làm mới. Thật tuyệt phải không?

Nhập nhiều trang web

Từ đầu đến giờ chúng ta chỉ nhập dữ liệu từ một trang web duy nhất. Nhưng nếu chúng ta muốn nhập nhiều trang web thì sao? Nếu Chức năng tùy chỉnh(Custom) đang hoạt động thì điều này sẽ trở nên dễ dàng.

Thêm một số hàng dữ liệu khác vào bảng tblURL.

Nhấp vào Data -> Refresh All

Kiểm tra bảng tỷ giá hối đoái…Ồ Wowwww, Power Query đã nhập tất cả các ngày và tỷ lệ có trong bảng của tblURL. Chúng ta có thể nhận được tất cả tỷ giá FX cho bất kỳ ngày nào đối với bất kỳ loại tiền tệ cơ sở nào.

Một vài cảnh báo

Đây thực sự là một công cụ mạnh mẽ. Nhưng có một số điều bạn cần lưu ý:

Nếu bạn gọi quá nhiều URL, truy vấn có thể làm mới chậm.
Nếu xe.com thay đổi trang web của họ, truy vấn có thể sẽ không còn đúng nữa.
Chúng ta không thể lấy dữ liệu từ mọi trang web vì có một số trang web không được cấu trúc theo đúng cách.

Các bạn thấy đó việc nhập dữ liệu từ web vào trong Power Query cũng đơn giản thôi đúng không nào? Học Excel Online sẽ tiếp tục đồng hành cùng các bạn trong việc chinh phục Power Query. Xin chào và hẹn gặp lại!