Tip1-4 엑셀 파워쿼리로 Yes24 책 정보(웹페이지) 가져오기 4 마지막

[목표]

1. 파워쿼리에서 반복문(loop, for) 만들기 : 비슷한 처리를 여러번 반복시키기

2. 함수를 통해 책 정보 가져오기

3. 열 추가를 통해 전체 책 정보를 가져오기

4. 데이터 나누기를 통해 가져온 정보를 2개의 열로 만들기

지난 글에서는 개별 도서의 출간일과 판매지수를 묶어서 가져오는 방법까지 소개해드렸습니다.

이번 글에서는 지난 글에서 한 방법을 이용하여 목록에 있는 전체 책에 대한 정보를 반복해서 수집하고 적용하는 방법을 소개하겠습니다.

1. 파워쿼리에서 반복문(loop, for) 만들기 : 비슷한 처리를 여러번 반복시키기

파워쿼리는 전문 프로그램언어라기 보다는 엑셀의 쿼리문을 지원하기 위한 것이라서 일반적인 프로그램 언어에서 사용하는 for문이나 do문을 사용하기 보다는 함수를 통해 반복문을 처리합니다.

함수라는 단어가 생소하신 분들도 있으실텐데요. '어떤 것을 처리하는 기능 모음'이라고 생각하시면 됩니다.

예를 들어

1 + 2를 계산할 때 +는 앞의 수와 뒤의 수를 합하는 기능을 갖고 있습니다.

그런데, 1 + 2 * 1 + 2를 계산한다고 할 때 +, *를 함께 사용해서 식을 만들게됩니다.

이런 계산을 여러번 할 필요가 있다고 하면, 이 기능을 정의하고 반복해서 사용하기 편하게 이름을 붙이는 것이 편리한데요. 이렇게 정의해준 것을 사용자가 만든 함수라고 얘기할 수 있겠습니다.

우리는 책 정보에서 출간일과 판매지수만 가져오는 기능을 사용자가 만든 함수 형태로 만들어서 사용하려고 합니다.

그럼... 함수를 만드는 방법을 알아 보겠습니다.

함수의 기본 적인 형태는 아래와 같습니다.

========================================

let

결과1 = (데이터) =>

let

결과2 = 위에서 받은 데이터 처리

in

결과2

in

결과1

========================================

2. 함수를 통해 책 정보 가져오기

책 정보를 가져오는 파워쿼리를 위에 적은 반복문 형태로 만들어보겠습니다.

========================================

let 

    getDetail = (Booknums as text) =>

    

    let

        원본 = Web.Page(Web.Contents("http://www.yes24.com/24/goods/"&Booknums)),

        Data2 = 원본{2}[Data],

        Children = Data2{0}[Children],

        Children1 = Children{1}[Children],

        Children2 = Children1{4}[Children],

        Children3 = Children2{12}[Children],

        Children4 = Children3{18}[Children],

        Children5 = Children4{0}[Children],

        Children6 = Children5{0}[Children],

        Children7 = Children6{0}[Children],

        Children8 = Children7{0}[Children],

        Children9 = Children8{0}[Children],

        Children10 = Children9{9}[Children],

        Children11 = Children10{16}[Children],

        Children12 = Children11{2}[Children],

        Children13 = Children12{0}[Children],

        Children14 = Children13{0}[Text],

        Pubdate = Children14,

        Sell1 = Children11{5}[Children],

        Sell2 = Sell1{0}[Text],

        Sell3 = Text.Replace(Sell2, "판매중 | 판매지수 ",""),

        Salespoint = Sell3,

        Result = Pubdate&"||"&Salespoint

        //Result = Pubdate

    in

        Result

in

    getDetail

========================================

중간 부분은 이전 글에서 소개한 내용과 유사합니다. 다른 부분을 설명드리면...

getDetail = (Booknums as text) =>

: 결과값을 표시해주기 위해 let부분에 getDetail이라는 이름에 데이터를 할당합니다. 데이터는 Booknums라는 이름을 사용하였고, 이 부분에 각 도서의 책번호를 할당하여 전체 책에 대한 세부 정보를 가져오게 할 것입니다.

원본 = Web.Page(Web.Contents("http://www.yes24.com/24/goods/"&Booknums)),

: 위에서 넘겨받은 Booknums를 바꿔가면서 책정보를 가져오게 하기 위해, URL의 앞부분은 고정값으로 두고, 뒷부분의 숫자만 바꿀 수 있게 변수처리 했습니다.

파워쿼리 내용을 이와 같이 작성한 후 파워쿼리 이름을 fgetDetail이라고 지정한 후

상단메뉴 > 홈 > 닫기및로드로 작성을 완료합니다. 이렇게 함수 형태로 지정하면 전과 다르게 결과값 대신 값을 입력할 수 있는 형태의 화면이 보입니다.

닫기 및 로드를 한 후 엑셀 화면도 결과값이 보이지 않고, 오른쪽 쿼리 목록의 표시 내용도 fx라는 아이콘으로 바뀐 것을 확인하실 수 있습니다.

3. 열 추가를 통해 전체 책 정보를 가져오기

자, 이제 전체 목록에 대한 값을 가져오는 작업을 하겠습니다.

이 작업을 위해서는 전체 목록을 가져오는  'IT+모바일+YES베스트'를 더블클릭(또는 우마우스 > 편집)합니다.

상단메뉴 > 열 추가 > 사용자 지정 열 추가 > 새 열 이름을 '출간일과판매지수'로 입력 > 수식에 =fgetDetail( 입력 > 오른쪽 사용 가능한 열에서 '상품번호'선택 > 아래 삽입버튼 클릭 후 )를 입력

잠시 후 아래 그림처럼 책 정보를 가져온 것을 확인하실 수 있습니다.

일부 결과값에 의도하지 않은 값이 있어 아래 그림처럼

출간일과판매지수 열 선택 > 상단 메뉴 > 변환 > 값 바꾸기 > 찾을 값 입력 > 확인 클릭하여 결과값을 정제합니다.

4. 데이터 나누기를 통해 가져온 정보를 2개의 열로 만들기

가져온 값을 원래의 2개값으로 나누는 작업을 진행합니다.

출간일과판매지수 열 선택 > 상단메뉴 > 변환 > 열 분할 > 구분 기호로 열 분할 > 구분 기호 선택 또는 입력란 '사용자 지정' 선택 > 바로 아래 구분기호 입력란에 '||'입력 > 확인 클릭하여 출간일 정보와 판매지수를 분리합니다.

아래 그림처럼

첫번째 열을 '출간일'로 이름 변경하고, 두번째 열을 '판매지수'로 이름 변경합니다.

여기까지 진행하신 후 상단 메뉴 > 홈 > 파일 닫기 및 로드 클릭하시면,

전체 목록의 도서 정보를 수집합니다.(수십초에서 수분이 걸릴 수 있습니다.)

로딩 중에는 엑셀 화면 제일 아래 상태표시줄에 쿼리를 실행하고 있음이 표시됩니다.

로딩이 끝나면,

아래 화면처럼 출간일과 판매지수가 수집된 것을 확인하실 수 있습니다.

마지막으로 첫번째 글에서 말씀드린 것처럼 과도한 쿼리를 방지하기 위해, 각 도서에 대한 쿼리를 실행하기 전에 3초를 쉬게하는 부분을 추가하겠습니다.

파워쿼리 편집상태로 가서 아래 그림의 파란색 블록 부분의 소스를 추가해줍니다.

이 부분은 이해하려하지 마시고, 그냥 그런가보다... 생각하고 붙여주시면 됩니다.

나중에 대기 시간을 바꾸려면 

Pause = Wait(3,DateTime.LocalNow), 에 있는 숫자 3만 다른 숫자로 바꾸시면 됩니다.

========================================

Wait = (seconds as number, action as function) => 

    if (List.Count(List.Generate(() => DateTimeZone.LocalNow() + #duration(0,0,0,seconds), (x) => DateTimeZone.LocalNow() < x, (x) => x)) = 0) 

    then null 

    else action(),

Pause = Wait(3,DateTime.LocalNow),

========================================

[사례 실습 파일]

etc01웹파싱03.xlsx

이렇게 해서 총 4단계의 웹페이지 책 정보 가져오기 소개 글이 끝났습니다. 질문이나 오류 신고는 이 블로그 댓글 또는 facebook '엑셀로시작하는빅데이터분석'페이지에 올려주시기 바랍니다. 

다음 팁으로는 이런 수집 작업을 주기적으로 자동 실행하도록 하는 방법에 대해서 소개하겠습니다.

아마도... 메인 사례 2번(매출관리, 예측분석 소개)이 끝난 후 진행할 수 있을 것 같습니다.

감사합니다.