Làm cách nào tôi có thể liên kết đến dữ liệu trên một trang web có nhiều trang trong bảng, tất cả đều có cùng một URL?
Lời khuyên
Có vẻ như tất cả những gì chúng tôi làm là Power Query [chúng tôi không. ], chúng tôi nghĩ rằng chúng tôi sẽ chia sẻ với bạn giải pháp của chúng tôi cho một vấn đề khá khó xử – nhưng phổ biến. Xem xét dữ liệu từ trang web http. //www. quanthockey. com/khl/mùa/2017-18-khl-players-stats. html
Trang web được trình bày độc đáo và chứa một bảng thống kê về vận động viên khúc côn cầu. Vấn đề là, bảng nhúng thực sự có 17 trang dữ liệu và giả sử chúng tôi muốn trích xuất tất cả dữ liệu này để phân tích ở nơi khác
Có một vấn đề mặc dù. Khi bạn nhấp vào trang dữ liệu thứ hai hoặc trang tiếp theo, URL của trang web không thay đổi. Điều này dường như đánh bại Power Query [hoặc Power BI] vì bắt buộc phải có URL cho mỗi trang của dữ liệu bảng
Vậy làm thế nào chúng ta có thể trích xuất tất cả dữ liệu?
Bước 1. Truy xuất dữ liệu thủ công
Trước khi chuyển sang giải pháp được đề xuất, trước tiên chúng ta nên giới thiệu cách nhập dữ liệu theo cách thủ công từ trang thống kê khúc côn cầu. Bạn nên lưu ý rằng phương pháp này không mang lại danh sách đầy đủ; . Trước tiên, hãy tập trung vào trích xuất thủ công
Để nhập dữ liệu theo cách thủ công từ trang web thống kê môn khúc côn cầu này http. //www. quanthockey. com/khl/mùa/2017-18-khl-players-stats. html bằng Power Query, trước tiên hãy mở Excel, sau đó điều hướng đến tab 'Dữ liệu' và nhấp vào tùy chọn 'Truy vấn mới', sau đó chọn tùy chọn 'Các nguồn khác', sau đó chọn 'Web'
Một hộp thoại sẽ xuất hiện cho phép chúng tôi chèn URL. Tiếp theo nhấp vào 'OK'
Hộp thoại ‘Navigator’ sẽ xuất hiện, cho phép chúng tôi chọn chính xác bảng nào sẽ lấy dữ liệu từ đó. Tại thời điểm này, tất cả đều ổn, tuy nhiên chúng ta nên đặt tên cho bảng, vì vậy hãy nhấp vào 'Chỉnh sửa'.
Trong hộp thoại 'Trình chỉnh sửa truy vấn', chúng ta nên đặt tên thân thiện cho Truy vấn của mình, chẳng hạn như 'Dữ liệu khúc côn cầu', sau đó chọn 'Đóng & Tải'.
Tất cả dường như đã hoạt động tốt, tuy nhiên khi kiểm tra kỹ hơn, chúng ta có thể thấy rằng Power Query chỉ có thể truy xuất 50 mục nhập đầu tiên, tôi. e. trang 1 của bảng dữ liệu
Điều này là do Power Query truy xuất dữ liệu dựa trên URL và trong trường hợp này, trang web thống kê khúc côn cầu thân thiện với Power Query của chúng tôi hiển thị dữ liệu bằng cách sử dụng JavaScript. Về cơ bản, trang web sử dụng mã JavaScript để tự động làm mới danh sách người chơi trên một trang, do đó cho phép trang web tự động làm mới danh sách người chơi trong một trang mà không thay đổi URL của trang web. Ngoài ra, chúng tôi cũng lưu ý rằng chúng tôi không thể chắc chắn có bao nhiêu trang dữ liệu mà trang web này có hoặc
Tóm lại, chúng ta có ba vấn đề.
- Chúng tôi không thể lấy tất cả dữ liệu từ trang web theo cách thủ công
- Chúng tôi không biết trang web có bao nhiêu trang dữ liệu
- Trang web không thay đổi URL của nó khi một trang dữ liệu mới được hiển thị.
Hãy giải quyết từng vấn đề một
Bước 2. Chức năng tùy chỉnh
Hãy giải quyết vấn đề đầu tiên, sau đó, tôi. e. không thể truy xuất tất cả dữ liệu theo cách thủ công chỉ bằng cách nhập dữ liệu đó bằng Power Query.
Một giải pháp được đề xuất, do MVP Reza Rad đề xuất, là sử dụng Hàm tùy chỉnh trong Power Query. Hàm tùy chỉnh là một truy vấn được chạy bởi các truy vấn khác, dành cho những bạn biết Java từ hạt cà phê và tương tự như cái được gọi là Phương thức đối tượng. Lợi ích của việc có chức năng tùy chỉnh là chúng ta có thể lặp lại các bước tương tự đối với tập dữ liệu được làm mới nếu cần
Hãy làm việc với một ví dụ đơn giản để minh họa tiện ích của Hàm tùy chỉnh. Ví dụ: chúng tôi muốn truy xuất tổng thu nhập của tất cả các bộ phim được phát hành trong một năm cụ thể, cùng với thứ hạng hiện tại và hãng phim của họ. Không quan trọng chúng ta muốn bắt đầu từ năm nào; . ]
http. //www. boxofficemojo. com/yearly/chart/?yr=2017&p=. htm
Để khởi chạy Power Query, chúng tôi sẽ sử dụng Excel 2016 và chọn 'Truy vấn mới' từ tab 'Dữ liệu'
Sau đó, chúng tôi sẽ áp dụng các tùy chọn mặc định và nhấp vào 'OK'.
Một hộp thoại sẽ xuất hiện, nhắc chọn bảng. Chúng tôi chọn 'Bảng 1', sau đó nhấp vào 'Chỉnh sửa'
Bây giờ chúng ta đã mở cửa sổ Trình soạn thảo truy vấn, chúng ta có thể xác định tham số của mình. Các thông số là cần thiết để các chức năng tùy chỉnh hoạt động.
Chúng tôi tạo một tham số đơn giản, đặt tên cho loại 'Năm' thành 'văn bản' và giá trị ban đầu là 2017
Giờ đây, chúng tôi có thể thêm một cột tùy chỉnh vào 'Bảng 1', nhấp vào 'Bảng 1', sau đó trên tab 'Thêm cột', sau đó nhấp vào 'Cột tùy chỉnh'.
Chúng tôi đặt tên cho cột tùy chỉnh là 'Năm' và đặt nó bằng tham số 'Năm'.
Đảm bảo thay đổi loại dữ liệu của cột tùy chỉnh thành 'Văn bản'
Tiếp theo, chúng ta cần tích hợp Tham số của mình vào URL. Nếu đạt được điều này, chúng tôi sẽ có thể tự động thay đổi URL, cuối cùng là thay đổi nguồn của cơ sở dữ liệu theo năm mong muốn
Với 'Bảng 1' được chọn, chúng ta có thể nhấp vào biểu tượng cài đặt cho 'Nguồn' trong 'Các bước đã áp dụng', viz.
Chọn tùy chọn 'Nâng cao', chúng tôi có thể Xác định phần URL có ngày và thay vào đó nhập tham số vào vị trí của nó. Chúng ta cũng nên bao gồm bit cuối cùng của URL sau tham số Năm. Chúng tôi làm điều này bằng cách 'Thêm phần' vào URL
Khi đã xong, hãy nhấp vào 'OK'
Bây giờ chúng ta phải chuyển đổi truy vấn thành một chức năng. Để thực hiện việc này, chúng tôi nhấp chuột phải vào truy vấn 'Bảng 1' và sau đó chọn 'Tạo hàm…'
Ở đây, chúng tôi sẽ đặt tên cho chức năng là 'GetMovies', sau đó nhấp vào 'OK'
Hiện có một thư mục nhóm chứa truy vấn 'Bảng 1' ban đầu, thông số Năm 2017 và chức năng GetMovies.
Chúng tôi đã tạo một bản sao của truy vấn 'Bảng 1' và gọi nó là 'GetMovies', từ bây giờ mỗi khi chúng tôi gọi GetMovies, Power Query sẽ thực hiện các tác vụ tương tự theo thứ tự đó
Để đơn giản, chúng ta sẽ tạo một trình tạo đơn giản và sử dụng Danh sách. Chức năng số để tạo trình tạo của chúng tôi. Để làm điều này, bạn có thể tạo một truy vấn mới bằng cách điều hướng đến tab 'Trang chủ' trong Trình chỉnh sửa Truy vấn, sau đó 'Nguồn Mới', 'Các Nguồn Khác' và chọn Truy vấn Trống. Sau đó, nhập công thức sau vào thanh công thức
Sau đó, danh sách có thể được chuyển đổi thành bảng bằng cách sử dụng tùy chọn 'To Table' nằm trong nhóm 'Convert'.
Cài đặt chuyển đổi mặc định sẽ đủ. Cuối cùng, thay đổi kiểu dữ liệu thành 'Văn bản'.
Với truy vấn 'Truy vấn1' được chọn, chúng ta có thể gọi hàm tùy chỉnh bằng cách chuyển đến tab 'Thêm cột' và chọn 'Gọi hàm tùy chỉnh' trong nhóm 'Chung'.
Chúng tôi sẽ đặt tên cho cột mới là 'GetMovieData', chọn chức năng 'GetMovies' và nhấp vào 'OK'
Một cột mới sẽ được thêm vào.
Nhấp vào từng Bảng riêng lẻ sẽ hiển thị dữ liệu phim cho năm tương ứng. Ví dụ: đây là dữ liệu năm 2006.
Tuy nhiên có một số hạn chế
- Chỉnh sửa tập lệnh M của hàm sẽ khiến hàm và truy vấn không thành công
- Không thể lên lịch cập nhật chức năng tùy chỉnh trong Power BI
Nó vẫn tốt hơn không có gì.
Nếu chúng tôi mở rộng tập dữ liệu của mình,
điều này tiết lộ bảng tổng hợp này với 100 bộ phim hàng đầu cho mỗi năm
Tuy nhiên, dữ liệu vẫn cần được dọn dẹp, bạn có thể tìm hiểu cách làm điều đó bằng cách cập nhật chuỗi Con trỏ Power Query của chúng tôi
Phần trên đề cập đến việc nhập dữ liệu thủ công, nhưng vấn đề về số trang thì sao?
Bước 3. Số trang không xác định
Giải pháp cho vấn đề này được tạo ra bởi nỗ lực tổng hợp của Matt Mason, bài đăng trên blog của anh ấy có thể được tìm thấy tại đây và Miguel Escobar, người có video giải thích rõ về phương pháp của anh ấy
Phương pháp của Matt Mason áp dụng một phương pháp mạnh mẽ trong đó nó hướng dẫn Power Query chạy qua các trang từ 1 đến 10.000 và dừng khi Power Query gặp lỗi hoặc giá trị 'null'. Ông chỉ ra rằng nếu phương pháp này được sử dụng cùng với phần mềm của bên thứ ba chẳng hạn như Fiddler [thêm về Fiddler sau], Power Query sẽ được tìm thấy khi cố gắng đánh giá tất cả 10.000 trang. Hơn nữa, nếu bạn thử phương pháp của Matt ngay bây giờ với phiên bản Power Query mới nhất, thì bạn có thể nhận được thông báo lỗi rằng bạn không có quyền truy cập vào cơ sở dữ liệu
Đây là lúc Miguel đến và điều chỉnh mã một chút để nó không áp dụng phương pháp vũ phu nữa cũng như sửa lỗi quyền đã phát triển trong Power Query. Xây dựng lý tưởng của Matt Mason, chúng tôi sẽ chỉ sử dụng chức năng 'GetData' của anh ấy và xây dựng điều đó.
Để bắt đầu, hãy mở Power Query từ Excel và chuyển đổi truy vấn GetData của Matt thành một hàm
Bây giờ chúng tôi tạo một truy vấn hoàn toàn mới. chuyển đến 'Nguồn mới' rồi đến 'Nguồn khác' và 'Truy vấn trống'. Dòng mã đầu tiên được nhập sử dụng Danh sách. Tạo chức năng
Danh pháp hàm []=> về cơ bản nói rằng chúng ta sẽ tìm một hàm không có tham số.
[Kết quả= thử GetData[1] nếu không thì null, Trang = 1],
Dòng này sử dụng chức năng ban đầu của Matt; . Về cơ bản, nó cho biết 'thử' với GetData, nếu nó trả về lỗi, hãy trả về 'Null' trong Trang 1
xác định cụ thể một điều kiện, trong đó kết quả không thể rỗng. Hoặc thực hiện chức năng này miễn là Kết quả không bằng null.
Dòng tiếp theo tăng Trang đến trang 2
mỗi [Kết quả = thử GetData[[Trang]+1] nếu không thì null, Trang = [Trang]+1],
Dòng cuối cùng trong hàm này hướng dẫn Power Query hiển thị trường Kết quả
Khi chúng tôi nhấn ENTER, chúng tôi sẽ thấy danh sách các bảng
Đây là tất cả các trang khác nhau về tổng doanh thu nội địa năm 2016 từ trang web Box Office Mojo. Lưu ý rằng Power Query không cố gắng đánh giá 10.000 trang
Bây giờ chúng ta xem qua bảng và xác định kiểu dữ liệu của từng cột. Mặc dù đây vẫn là một danh sách nhưng chúng ta có thể chuyển đổi danh sách này thành một bảng và mở rộng dữ liệu
Khi bảng đã được chuyển đổi, chúng ta có thể mở rộng bảng
Bảng mở rộng sẽ trông giống như thế này
Việc đóng và tải sẽ không gây ra lỗi mà thay vào đó là tất cả dữ liệu phim từ năm 2016 từ Box Office Mojo
Bây giờ chúng ta đã xử lý vấn đề về số trang, bây giờ chúng ta có thể chuyển sang phần cuối cùng…
Bước 4. Fiddle với URL, Fiddle[r] với URL
Power Query cần một số hỗ trợ. một chương trình phần mềm có tên Fiddler là một trợ lý như vậy. Để tải xuống phần mềm này, vui lòng truy cập trang phần mềm của Telerik để tải xuống Fiddler. Khi PC của bạn đã hoàn tất cài đặt Fiddler, hãy mở nó và trang sẽ giống như thế này
Vì lời nhắc “Vui lòng chọn một Phiên web duy nhất để tác động” sẽ yêu cầu trình duyệt web;
Vì vậy, chúng tôi quay lại nơi chúng tôi đã bắt đầu và điều hướng lại đến trang web thống kê Khúc côn cầu [URL. http. //www. quanthockey. com/khl/mùa/2017-18-khl-players-stats. html] và chúng ta sẽ bắt đầu thấy một số điều thú vị xuất hiện trên Fiddler
Fiddler lấy nguồn của URL và hiển thị ở đây, vì vậy, hãy xem điều gì sẽ xảy ra khi chúng tôi thử trang 2 của Thống kê khúc côn cầu. Fiddler hiện trở lại với một URL thay thế
Nó dường như đã được chia thành các Phần và các trang có khả năng cũng vậy. Hãy sao chép nó và lưu nó vào bảng tính Excel để giúp chúng tôi khám phá bất kỳ mẫu nào. Nhấp chuột phải vào dòng URL và chọn 'Chỉ sao chép URL'
Sau khi lặp lại quá trình, một vài lần, chúng tôi phát hiện ra một mô hình. Fiddler có thể truy xuất URL và chia nhỏ nó thành các trang. Đây là một tin tuyệt vời, cuối cùng chúng ta cũng có thể sử dụng tin này để làm việc với Power Query
Bước 5. Một giải pháp
Bây giờ là phần cuối cùng, nơi chúng ta kết hợp mọi thứ lại với nhau
Bước đầu tiên là tạo một Truy vấn mới trong Power Query và tạo một tham số mới
Đặt tên cho tham số là 'Số trang', đặt nó thành loại Số thập phân và đặt cho nó giá trị hiện tại là 1.
Bây giờ hãy tạo Truy vấn trống mới và dán mã gốc từ Matt Mason vào thanh công thức
Nguồn = Web. Trang[Web. Nội dung["http. //boxofficemojo. com/yearly/chart/?page=" & Number. ToText[trang] & "&view=releasedate&view2=domestic&yr=2013&p=. htm"]],
Sau đó sửa đổi nó bằng URL mới được cung cấp từ Fiddler
=Web. Trang[Web. Nội dung["http. //www. quanthockey. com/scripts/AjaxPaginate. php?cat=Season&pos=Players&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page=2&league=KHL&lang=vi&rnd=167379793&dt=1"]]
Chúng tôi cũng phải bao gồm tham số PageNumber và Văn bản. Từ chức năng Power Query để đảm bảo rằng nó được chèn vào URL dưới dạng định dạng văn bản. Đoạn mã sau sẽ thay thế số trang [trong trường hợp bạn đang thắc mắc, các ký hiệu dấu và có nghĩa là nối]
= "&Văn bản. Từ[Số trang]&"
=Web. Trang[Web. Nội dung["http. //www. quanthockey. com/scripts/AjaxPaginate. php?cat=Season&pos=Players&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page="&Text. From[PageNumber]&"&league=KHL&lang=en&rnd=276273473&dt=1"]]
Như bạn có thể thấy, tham số PageNumber đã được liên kết với URL. Power Query sẽ trở lại với một bảng thu gọn. Bước tiếp theo là chọn tùy chọn 'Bảng' trên cùng bên phải
Điều này sẽ mở rộng bảng dẫn đến một bảng chỉ nhập dữ liệu từ trang đầu tiên hoặc 50 bản ghi đầu tiên
Bây giờ, hãy tạo một truy vấn trống mới và sao chép mã này vào, đây là phiên bản sửa đổi của hàm GetData của Matt cho mục đích của chúng tôi
= [Số trang dưới dạng số] => để
Nguồn = Web. Trang[Web. Nội dung["http. //www. quanthockey. com/scripts/AjaxPaginate. php?cat=Season&pos=Players&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page="&Text. From[PageNumber]&"&league=KHL&lang=vi&rnd=276273473&dt=1"]],
Dữ liệu0 = Nguồn{0}[Dữ liệu],
#"Đã thay đổi loại" = Bảng. TransformColumnTypes[Data0,{{"Rk", Int64. Nhập}, {"", nhập văn bản}, {"Tên", nhập văn bản}, {"Tuổi", Int64. Nhập}, {"Pos", nhập văn bản}, {"GP", Int64. Nhập}, {"G", Int64. Nhập}, {"A", Int64. Nhập}, {"P", Int64. Nhập}, {"PIM", Int64. Nhập}, {"+/-", Int64. Nhập}, {"PPG", Int64. Nhập}, {"SHG", Int64. Nhập}, {"GWG", Int64. Nhập}, {"G/GP", nhập số}, {"A/GP", nhập số}, {"P/GP", nhập số}}]
Trong
#"Đã thay đổi loại"
Đoạn code thứ 2 chỉ cần thay đổi kiểu dữ liệu cho phù hợp từng cột nên bạn không phải làm. Sau khi nhập, đổi tên chức năng thành 'PageData'
Bây giờ để kết hợp phương pháp của Miguel, hãy tạo một truy vấn trống khác và sao chép mã này vào
= Danh sách. Tạo [ []=>
[Kết quả= thử PageData[1] nếu không thì null, Trang = 1],
mỗi [Kết quả] null,
mỗi [Kết quả= thử PageData[Page] nếu không thì null, Trang = [Trang] +1],
từng [Kết quả]]
Sau khi nhập, hãy đổi tên của truy vấn thành 'AllData'
Lần này không cần sửa đổi gì trong mã, ngoài việc chuyển đổi danh sách này thành một bảng
Sau khi Power Query đã chuyển đổi thành bảng, chúng ta có thể mở rộng bảng
Mở rộng bảng sẽ mang lại kết quả này, trong đó Power Query có thể biên dịch toàn bộ danh sách những người chơi Khúc côn cầu, vượt quá 50
Bây giờ chúng ta có thể tiến hành 'Đóng và tải'
Ở đó bạn có nó, tất cả 829 số liệu thống kê Người chơi khúc côn cầu [tại thời điểm viết bài] trong một trang tính
Cho đến khi Microsoft giới thiệu một tính năng tích hợp mới để phá vỡ tất cả mã khó chịu này, dường như có rất ít giải pháp thay thế cho vấn đề phổ biến này.
Chúng tôi xin cảm ơn Reza, Miguel Escobar, Matt Mason và Simon Sabin vì tất cả những đóng góp cho giải pháp này