Excel로 여러 페이지로 구성된 웹 데이터 불러오기

단일 URL을 사용하여 웹 사이트에서 다중 페이지 데이터 참조

질문

동일한 URL을 사용하는 테이블의 두 페이지 이상에있는 웹 사이트의 데이터에 연결하려면 어떻게해야합니까?

 

조언 

우리가하는 모든 일이 파워 쿼리 인 것처럼 보이는 위험을 감수하고 (그렇지 않습니다!), 다소 어색하지만 일반적인 문제에 대한 솔루션을 여러분과 공유 할 것이라고 생각했습니다. http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html 웹 사이트의 데이터를 고려 하십시오 .

웹 페이지는 멋지게 설정되어 있으며 하키 선수 통계표가 포함되어 있습니다. 문제는 포함 된 테이블에 실제로 17 페이지의 데이터가 있으며 다른 곳에서 분석하기 위해이 모든 데이터를 추출한다고 가정 해 보겠습니다.

그래도 문제가 있습니다. 데이터의 두 번째 또는 후속 페이지를 클릭해도 웹 사이트의 URL은 변경되지 않습니다. 이는 테이블 데이터의 각 페이지에 대한 URL이 필요하므로 파워 쿼리 (또는 Power BI)를 무력화하는 것 같습니다.

그렇다면 모든 데이터를 어떻게 추출 할 수 있습니까? 이에 답하기 위해 5 단계로 이동하겠습니다.

 

1 단계 : 데이터 수동 검색

제안 된 솔루션으로 이동하기 전에 먼저 하키 통계 사이트에서 데이터를 수동으로 가져 오는 방법을 다루어야합니다. 이 방법은 전체 목록을 생성하지 않습니다. 이것은 나중에 자세히 설명합니다. 먼저 수동 추출에 중점을 둡니다.

파워 쿼리를 사용 하여이 하키 통계 사이트 http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html 에서 데이터를 수동으로 가져 오려면 먼저 Excel을 연 다음 '데이터'탭으로 이동합니다. '새 쿼리'옵션을 클릭 한 다음 '기타 소스'옵션과 '웹'을 차례로 선택합니다.

URL을 삽입 할 수있는 대화 상자가 나타납니다. 다음으로 '확인'을 클릭하십시오.

데이터를 가져올 테이블을 정확히 선택할 수있는 'Navigator'대화 상자가 나타납니다. 이 시점에서 모두 괜찮아 보이지만 테이블 이름을 지정해야하므로 '편집'을 클릭 해 보겠습니다. 

'쿼리 편집기'대화 상자에서 쿼리에 친숙한 이름 ( 'Hockey Data')을 지정한 다음 '닫기 및로드'를 선택해야합니다. 

모두 잘 작동하는 것 같지만 자세히 살펴보면 파워 쿼리가 처음 50 개 항목, 즉 테이블 데이터의 1 페이지 만 검색 할 수 있음을 알 수 있습니다 .

이는 파워 쿼리가 URL을 기반으로 데이터를 검색하고이 경우 파워 쿼리 친화적 인 하키 통계 웹 사이트가 JavaScript를 사용하여 데이터를 표시하기 때문입니다. 기본적으로 웹 사이트는 JavaScript 코드를 사용하여 한 페이지의 플레이어 목록을 동적으로 새로 고치므로 웹 페이지의 URL을 변경하지 않고도 웹 페이지에서 한 페이지의 플레이어 목록을 동적으로 새로 고칠 수 있습니다. 또한이 웹 사이트에 얼마나 많은 데이터 페이지가 있는지도 알 수 없습니다.

요약하면 세 가지 문제가 있습니다. 

1. 웹 사이트에서 모든 데이터를 수동으로 가져올 수 없습니다.
2. 우리는 웹 사이트에 얼마나 많은 데이터 페이지가 있는지 모릅니다.
3. 웹 페이지는 새 데이터 페이지가 표시 될 때 URL을 변경하지 않습니다. 

한 번에 한 가지 문제 만 다루겠습니다.

728x90

2 단계 : 사용자 지정 함수

첫 번째 문제, 즉 파워 쿼리를 사용하여 데이터를 가져 오는 것만으로는 모든 데이터를 수동으로 검색 할 수없는 문제를 해결하겠습니다 . 

MVP Reza Rad가 제안한 한 가지 제안 된 솔루션은 파워 쿼리에서 사용자 지정 함수를 활용하는 것입니다. 사용자 정의 함수는 커피 원두에서 Java를 알고있는 사용자를 위해 다른 쿼리에 의해 실행되는 쿼리이며 Object Method라고하는 것과 유사합니다. 사용자 지정 함수의 이점은 필요한 경우 새로 고침 된 데이터 세트에 동일한 단계를 반복 할 수 있다는 것입니다.

Custom Function의 유틸리티를 설명하기 위해 간단한 예제를 살펴 보겠습니다. 예를 들어 특정 연도에 개봉 된 모든 영화의 총 수입과 현재 순위 및 스튜디오를 검색하려고합니다. 어느 연도부터 시작하든 상관 없습니다. 이 예에서는 2017 년부터 시작할 것입니다 (2018 년에는 너무 이른시기입니다!).

http://www.boxofficemojo.com/yearly/chart/?yr=2017&p=.htm 

파워 쿼리를 시작하기 위해 Excel 2016을 사용하고 '데이터'탭에서 '새 쿼리'를 선택합니다.

그런 다음 기본 옵션을 채택하고 '확인'을 클릭합니다. 

테이블을 선택하라는 대화 상자가 나타납니다. 'Table 1'을 선택한 다음 'Edit'를 클릭합니다.

이제 쿼리 편집기 창이 열렸으므로 매개 변수를 정의 할 수 있습니다. 사용자 정의 함수가 작동하려면 매개 변수가 필요합니다. 

간단한 매개 변수를 만들고 이름을 'Year'유형으로 'text'로 설정하고 초기 값을 2017로 설정합니다.

이제 '표 1'에 맞춤 열을 추가하고 '표 1'을 클릭 한 다음 '열 추가'탭을 클릭 한 다음 '맞춤 열'을 클릭 할 수 있습니다. 

맞춤 열에 'Year'라는 이름을 지정하고 매개 변수 'Year'와 동일하게 만듭니다. 

사용자 정의 열의 데이터 유형을 '텍스트'로 변경해야합니다.

다음으로 매개 변수를 URL에 통합해야합니다. 이를 달성하면 URL을 동적으로 변경하여 궁극적으로 원하는 연도까지 데이터베이스 소스를 변경할 수 있습니다.

'Table 1'을 선택한 상태에서 'Applied Steps', 즉 'Source'에 대한 설정 아이콘을 클릭 할 수 있습니다 . 

'고급'옵션을 선택하면 URL에서 날짜가있는 부분을 식별하고 대신 매개 변수를 입력 할 수 있습니다. Year 매개 변수 뒤에 URL의 마지막 비트도 포함해야합니다. URL에 '부품 추가'를 통해이를 수행합니다.

완료되면 '확인'을 클릭하십시오.

이제 쿼리를 함수로 변환해야합니다. 이를 위해 'Table 1'쿼리를 마우스 오른쪽 버튼으로 클릭 한 다음 'Create Function…'을 선택합니다.

여기에서 함수 이름을 ' GetMovies '로 지정한 다음 '확인'을 클릭합니다.

이제 원래 'Table 1'쿼리, Year 2017 매개 변수 및 GetMovies 함수가 포함 된 그룹 폴더가 있습니다. 

우리는 '표 1'쿼리의 복사본을 생성 '을 불렀다 GetMovies 우리가 전화 할 때마다 지금부터,' GetMovies , 전원 질의 순서대로 동일한 작업을 수행합니다.

단순하게하기 위해 간단한 생성기 를 만들고 List.Numbers 함수를 사용하여 생성기를 만듭니다. 이렇게하려면 쿼리 편집기에서 '홈'탭으로 이동 한 다음 '새 소스', '기타 소스'로 이동 한 다음 빈 쿼리를 선택하여 새 쿼리를 만들 수 있습니다. 그런 다음 수식 입력 줄에 다음 수식을 입력합니다.

= List.Numbers (2002,16)

그런 다음 '변환'그룹에있는 '테이블로'옵션을 사용하여 목록을 테이블로 변환 할 수 있습니다. 

기본 변환 설정으로 충분합니다. 마지막으로 데이터 유형을 '텍스트'로 변경합니다. 

'Query1'쿼리를 선택한 상태에서 '열 추가'탭으로 이동하여 '일반'그룹에서 '사용자 지정 함수 호출'을 선택하여 사용자 지정 함수를 호출 할 수 있습니다. 

새 열의 이름을 ' GetMovieData '로 지정하고 ' GetMovies '기능을 선택한 다음 '확인'을 클릭합니다.

새 열이 추가됩니다. 

각 개별 테이블을 클릭하면 해당 연도의 영화 데이터가 표시됩니다. 예를 들어 2006 년 데이터는 다음과 같습니다. 

그러나 몇 가지 제한 사항이 있습니다.

• 함수 의 M 스크립트를 편집하면 함수 및 쿼리가 실패합니다.
• 사용자 지정 함수는 Power BI에서 업데이트하도록 예약 할 수 없습니다.

아무것도없는 것보다 낫습니다. 

데이터 세트를 확장하면

이것은 매년 상위 100 편의 영화가 포함 된이 컴파일 된 표를 보여줍니다.

데이터는 여전히 약간의 정리가 필요하지만 파워 쿼리 포인터 시리즈를 따라 가면이를 수행하는 방법을 배울 수 있습니다!

위의 내용은 데이터 수동 가져 오기를 다루지 만 페이지 번호 문제는 어떻습니까?

 

3 단계 : 알 수없는 페이지 수

이 문제에 대한 해결책은 Matt Mason, 그의 블로그 게시물은 여기 에서 찾을 수 있으며 Miguel Escobar의 비디오 는 그의 방법을 잘 설명합니다.

Matt Mason의 방법은 파워 쿼리가 1 ~ 10,000 페이지를 통해 실행하고 파워 쿼리가 오류 또는 'null'값이 발생하면 중지하도록 지시하는 무차별 대입 방법을 채택합니다. 그는이 방법을 Fiddler (나중에 Fiddler에 대해 자세히 설명 함)와 같은 타사 소프트웨어와 함께 사용하면 10,000 페이지를 모두 평가하려고 시도하는 파워 쿼리가 발견 될 것이라고 지적합니다. 또한 최신 버전의 파워 쿼리에서 Matt의 방법을 지금 시도하면 데이터베이스에 대한 액세스 권한이 없다는 오류가 표시 될 수 있습니다.

이것은 Miguel이 들어 와서 코드를 약간 조정하여 더 이상 무차별 대입 방법을 채택하지 않고 파워 쿼리에서 개발 된 권한 버그를 수정합니다. Matt Mason의 이상을 바탕으로 그의 ' GetData '기능 만 사용 하고이를 기반으로 구축합니다.  

먼저 Excel에서 파워 쿼리를 열고 Matt의 GetData 쿼리를 함수로 변환 해 보겠습니다 .

이제 완전히 새로운 쿼리를 만듭니다. '새 소스'로 이동 한 다음 '기타 소스'및 '빈 쿼리'로 이동합니다. 입력 할 코드의 첫 번째 줄은 List.Generate 함수를 사용합니다 .

= List.Generate (() =>

() => 함수 명명법은 본질적으로 매개 변수가없는 함수를 찾을 것이라고 말합니다. 

다음 줄은 다음과 같습니다.

[결과 = GetData (1) 시도, 그렇지 않으면 null, 페이지 = 1],

이 줄은 Matt의 원래 기능을 사용합니다. 그러나 오류 검사가 발생합니다. 기본적으로 GetData에 'try'라고 표시  하고 오류와 함께 반환되면 1 페이지에서 'Null'로 반환합니다.

다음 줄 :

각 [결과] <> null,

결과가 null 일 수없는 조건을 지정합니다. 또는 결과가 null이 아닌 한이 기능을 수행하십시오. 

다음 줄은 페이지를 2 페이지로 증가시킵니다.

각 [Result = try GetData ([Page] +1) 그렇지 않으면 null, Page = [Page] +1],

이 함수의 마지막 줄은 결과 필드를 표시하도록 파워 쿼리에 지시합니다.

각 [결과])

ENTER 를 누르면 테이블 목록이 표시됩니다.

이것은 Box Office Mojo 웹 사이트에서 2016 년 국내 총액을 가져온 모든 페이지입니다. 파워 쿼리는 10,000 페이지를 평가하지 않습니다.

이제 테이블을 살펴보고 각 열의 데이터 유형을 정의합니다. 이것은 여전히 ​​목록이지만이를 테이블로 변환하고 데이터를 확장 할 수 있습니다.

테이블이 변환되면 테이블을 확장 할 수 있습니다.

확장 된 표는 다음과 같아야합니다.

닫고로드하면 오류가 발생하지 않고 Box Office Mojo의 2016 년 영화 데이터가 모두 표시됩니다.

이제 페이지 번호 문제를 다루었으므로 이제 마지막 부분으로 넘어갈 수 있습니다.

 

4 단계 : URL 조작, URL 조작 Fiddle (r)

파워 쿼리에는 몇 가지 도움이 필요합니다 . Fiddler 라는 소프트웨어 프로그램 이 그러한 도우미 중 하나입니다. 이 소프트웨어를 다운로드하려면 Telerik의 소프트웨어 페이지 로 이동하여 Fiddler를 다운로드 하십시오 . PC에 Fiddler 설치가 완료되면이를 열면 다음과 같은 페이지가 표시됩니다.

"영향을 미칠 단일 웹 세션을 선택하십시오"라는 프롬프트에 웹 브라우저가 필요합니다. 이 예에서는 Chrome을 사용했습니다. 스크린 샷을 찍었을 때의 느낌 이었기 때문입니다.

그래서 우리는 시작했던 곳으로 돌아가서 다시 하키 통계 웹 사이트 (URL : http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html )로 이동합니다. Fiddler에 나타나는 몇 가지 흥미로운 사항을 참조하십시오.

Fiddler는 URL의 소스를 가져와 여기에 표시하므로 Hockey Stats의 2 페이지를 시도 할 때 어떤 일이 발생하는지 살펴 보겠습니다. Fiddler는 이제 대체 URL을 반환합니다.

시즌과 잠재적으로 페이지로 분류 된 것 같습니다. 패턴을 발견하는 데 도움이되도록이를 복사하여 Excel 스프레드 시트에 저장하겠습니다. URL 줄을 마우스 오른쪽 버튼으로 클릭하고 'URL 만 복사'를 선택합니다.

이 과정을 몇 번 반복 한 후 패턴을 발견합니다. Fiddler는 URL을 검색하여 페이지로 나눌 수 있습니다. 이것은 좋은 소식입니다. 마침내 이것을 사용하여 파워 쿼리를 사용할 수 있습니다.

5 단계 : 솔루션

이제 모든 것을 결합하는 마지막 부분입니다.

첫 번째 단계는 파워 쿼리에서 새 쿼리를 만들고 새 매개 변수를 만드는 것입니다.

매개 변수의 이름을 ' PageNumber '로 지정하고 10 진수 유형으로 설정 한 다음 현재 값 1을 지정하십시오. 

이제 새 빈 쿼리를 만들고 Matt Mason의 원래 코드를 수식 입력 줄에 붙여 넣습니다.

출처 = Web.Page (Web.Contents ( "http://boxofficemojo.com/yearly/chart/?page="& Number.ToText (page) & "& view = releasedate & view2 = domestic & yr = 2013 & p = .htm")),

그런 다음 Fiddler에서 제공 한 새 URL을 사용하여 수정합니다.

= Web.Page (Web.Contents ( "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 = en & rnd = 167379793 & dt = 1 "))

또한 PageNumber 매개 변수와 Text.From 파워 쿼리 함수를 포함하여 URL에 텍스트 형식으로 삽입되도록해야합니다. 다음 코드는 페이지 번호를 대체해야합니다 (궁금한 경우 앰퍼샌드 기호는 연결을 의미합니다).

= "& 텍스트 .From (페이지 번호) &"

이것을 산출 :

= Web.Page (Web.Contents ( "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 (페이지 번호) & " & league = KHL & lang = en & rnd = 276273473 & dt = 1"))

보시다시피 PageNumber 매개 변수가 URL에 연결되었습니다. 파워 쿼리는 압축 된 테이블로 반환됩니다. 다음 단계는 오른쪽 상단의 '표'옵션을 선택하는 것입니다.

이렇게하면 테이블이 확장되어 첫 페이지 또는 처음 50 개 레코드에서만 데이터를 가져 오는 테이블이 생성됩니다.

이제 새 빈 쿼리를 만들고이 코드를 복사합니다. 이것은 우리의 목적을 위해 Matt의 GetData 함수 의 수정 된 버전입니다 .

= (번호로서의 페이지 번호) => let

        출처 = Web.Page (Web.Contents ( "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")),

        데이터 0 = 소스 {0} [데이터],

        # "Changed Type"= Table.TransformColumnTypes (Data0, {{ "Rk", Int64.Type}, { "", type text}, { "Name", type text}, { "Age", Int64.Type}, { "Pos", type text}, { "GP", Int64.Type}, { "G", Int64.Type}, { "A", Int64.Type}, { "P", Int64.Type}, { "PIM", Int64.Type}, { "+/-", Int64.Type}, { "PPG", Int64.Type}, { "SHG", Int64.Type}, { "GWG", Int64.Type} , { "G / GP", 유형 번호}, { "A / GP", 유형 번호}, { "P / GP", 유형 번호}})

    에

        # "변경된 유형"

코드의 두 번째 섹션에서는 각 열에 따라 데이터 유형을 변경하여 수행 할 필요가 없습니다! 입력되면 함수의 이름을 ' PageData ' 로 바꿉니다 .

이제 Miguel의 방법을 통합하려면 다른 빈 쿼리를 만들고이 코드를 다음 위치에 복사합니다.

= List.Generate (() =>

[Result = try PageData (1) 그렇지 않으면 null, Page = 1],

각 [결과] <> null,

각 [Result = try PageData (Page) 그렇지 않으면 null, Page = [Page] +1],

 각 [결과])

입력 한 후 쿼리 이름을 ' AllData '로 변경합니다 .

이번에는이 목록을 테이블로 변환하는 것 외에 코드를 수정할 필요가 없습니다.

파워 쿼리가이를 테이블로 변환하면 테이블을 확장 할 수 있습니다.

테이블을 확장하면 파워 쿼리가 하키 선수의 전체 목록을 50 개 이상으로 컴파일 할 수있는 결과가 생성됩니다.

이제 '닫기 및로드'로 진행할 수 있습니다.

하나의 워크 시트에 모든 829 개의 하키 선수 통계 (작성 당시)가 있습니다!

마이크로 소프트가이 모든 불쾌한 코딩을 피할 수있는 새로운 내장 기능을 도입하기 전까지는이 일반적인 문제에 대한 대안이 거의없는 것으로 보입니다. 

이 솔루션에 기여한 Reza Rad , Miguel Escobar , Matt Mason 및 Simon Sabin 에게 감사드립니다 .

 

 

원문 :

www.sumproduct.com/thought/referencing-multi-page-data-from-a-web-site-with-a-single-url

https://unsplash.com/photos/FXFz-sW0uwo