엑셀 파워쿼리 예제 - egsel pawokwoli yeje

▶ 데이터 전처리 (Data Pre-processing)

- 비정형 데이터 (Unstrucured Data) -> 정형화된 테이블 (Structured Table) -> AI

                                                    · 하나의 변수에는 한 종류의 데이터만 기록되어 있어야 한다.

                                                    · 변수의 헤더는 변수의 레코드를 포함하는 개념이어야 한다.

▶ 테이블(Table)의 구성요소 

- 머리글 (헤더, Header) : 각 변수들의 의미를 설명해 주는 첫 행의 집합, 관측 값이 어떤 내용에 대한 값인지 설명해 주는 것과 동시에 관측 값들의 범위를 제한하는 역할도 한다.

- 변수 (Variables) : 변수는 보통 테이블에서 하나의 열로 표현되며 데이터가 표현고자 하는 현상에 대한 특정한 정보이다. 예를 들어 하나의 테이블이 6개의 변수로 구성되어 있다는 의미는 해당 데이터 셋이 특정 현상[기록하고자 하는 정보]을 6개의 측면에서 보여준다는 의미이다

- 관측 값 (Records) : 관측 값은 현상이 기록으로 표현되어 변수에 맞게 수집되어 저장되는 모든 값이며 테이블에서는 행으로 표현되어 진다. 이 때 하나의 행을 레코드(Record)라고 하며 하나의 레코드 즉 행은 다른 레코드와 구분되는 독립된 값을 갖는다.

- 정보의 수 = 변수의 수 = 테이블 열의 수

▶ 파워쿼리 (Power Query)

- BI(Bussiness Intelligence) 전문가 및 그 밖의 Excel 사용자에게 데이터 전처리, 데이터 검색, 데이터 전송 및 데이터 강화에 대한 원활하고 완벽한 환경을 제공하는 기능 (Excel 2016부터 가능)

- 기존 엑셀의 외부데이터 가져오기 기능과는 구별되는 기능이다.

- 기본의 데이터 가져오기 기능을 확장하여 더욱 다양한 원본에서 데이터를 가져올 수 있게 함 (폴더 및 빈쿼리)

- 이를 정형화된 테이블로 정리할 수 있는 여러 기능들을 포함하고 있다.

- 파워쿼리를 활용할 수 있는 직업

    · 다양한 데이터 원본(ex 관계형 데이터베이스, Excel, 텍스트 및 XML 파일, Odata 피드, 웹 페이지, Hadoop HDFS 등)에서 원하는 데이터 가져오기

    · 서로 다른 여러 데이터 원본의 데이터를 정제하여 정형화된 테이블(Structured Table) 만들기

    · 데이터 수집 및 전처리 과정 자동화

    · 데이터 전처리 과정(쿼리작업)의 공유

    · 웹쿼리(RSS 및 API를 활용한 웹 데이터 쿼리)가 가능

- 쿼리편집기 (Query Editor) : 엑셀의 추가 기능이지만 엑셀과는 독립된 리본 메뉴를 가지고 있다.

    · 각각 '파일', '변환', '열 추가', '보기' 텝은 데이터를 정형화 하고 조합 할 수 있는 다양한 기능들을 제공한다.

    · 쿼리 입력 창은 연결된 모든 쿼리와 이를 활용해 만드는 새로운 쿼리 모두 나타난다.

    · 쿼리편집기 수식 입력 창 : 엑셀과 마찬가지로 수식을 활용하여 데이터를 편집할 수 있다. 파워쿼리에서 사용하는 수식은 엑셀과는 다른 형태의 수식이며 이는 'M 코드'라고 지칭한다.

    · 쿼리 설정 창 : 모든 쿼리(전처리)작업이 기록되는 창으로 모든 쿼리 동작은 쿼리설정 창에 차례로 기록되기 떄문에 언제든 해당 작업을 수정하거나 삭제 또는 삽입할 수 있다.

    · 파워쿼리 데이터 창 : 파워쿼리 데이터 창에는 작업하고 있는 데이터가 테이블 형식으로 표시되는데 엑셀과는 달리 셀 값은 수정할 수 없다.

▶ 파워쿼리 (Power Query)를 활용한 데이터 수집

- 시트별로 흩어져 있는 데이터 파워쿼리 활용하여 통합하기

    · 여러 개의 엑셀 시트에 나뉘어져 있는 데이터를 하나로 통합하고자 할 때 파워쿼리를 사용하면 Ctrl+C, Ctrl+V의 무한 반복이나 VBA 등의 프로그래밍 도움 없이도 손쉽게 이를 통합하고 정형화 할 수 있다.

- 데이터의 형태가 모두 동일한 경우(같은 헤더 값을 갖는 경우) 각각의 레코드를 통합하여 하나의 정형화된 테이블로 작성이 가능

- 데이터의 형태가 다른 경우 테이블 연결을 통해 원하는 형태의 새로운 테이블 작성이 가능

▶ 데이터 추가와 그룹화

- 파워쿼리의 '열 추가 기능'은 엑셀의 데이터 입력 기능과 같다. 분석을 확장하가 위해 데이터를 추가해야 하는 경우 열 추가 기능을 사용한다

- 열 추가 기능은 기본적으로 파생 변수를 만들어 준다.

- 파워쿼리에서는 개별 셀을 편집하여 데이터를 입력할 수 없고 변수(열)단위의 데이터만 추가할 수 있다.

1. 기존의 열로부터 수식 또는 패턴을 찾아 새 열 값을 입력할 때 사용

2. 기존의 열에 수식 또는 파워쿼리 수식을 적용하여 새 열 값을 입력해야 하는 경우 사용

3. 기존의 열에 특정한 쿼리 함수 식을 적용해서 새 열을 만들어야 하는 경우 사용

4. 특정한 조건식을 활용하여 열 값을 입력할 때 사용

5. 1 또는 0 부터 시작하는 인덱스 값을 입력할 때 사용

- '예제의 열' 기능을 사용하면 기존의 열을 활용하여 (패턴 또는 수식을 적용해) 새로운 데이터를 손쉽게 입력할 수 있다.

- 패턴이나 규칙이 없는 경우는 데이터를 추가하기 어렵다.

- 즉 패턴이나 수식을 적용할 수 있는 데이터만 열(추가변수)로 입력 가능

- 인덱스 열을 사용하면 거의 모든 형태의 데이터를 입력할 수 있다. 

- '사용자 지정 열'을 활용한 열(변수) 추가

- 계산 식 입력 창 : 엑셀과 마찬가지로 사칙연산 등이 가능하다. 단, 사칙 연산의 대상은 셀이 아닌 열(변수)로 한정된다. 변수에 테이블이 저장되어 있는 경우는 테이블 자체가 계산의 대상이 된다.

- 사용 가능한 열 : 계산식에 사용할 수 있는 모든 열. 기본적으로 불러온 데이터의 변수 모두를 계산식에서 사용할 수 있다.

- 변수를 바탕으로하는 일반적인 계산 이외에 인덱스 및 기존열에서의 추출이나 조건식을 통한 열 또한 새로운 '열 추가' 기능으로 삽입이 가능하다.

- '조건 열' 삽입을 활용하면 새로운 열에 각각 다른 연산을 적용하여 값을 지정할 수 있다.

 파워쿼리 열 추가 기능은

    · 새로운 변수를 만들어 준다는 관점에서 계산 식이나 함수 식과는 구별된다.

    · 열 추가는 파생변수를 만들기때문에 측정값과 같은 형태의 계산에는 한계가 있다.

    *데이터 값 집계 에러를 피하기 위해서는 새로운 열을 만들어주는 대신에 측정값을 삽입하여 각각의 예산 값을 먼저 더하고 이에 대응하는 집행 값을 역시 더해 전체 값을 계산해주는 DAX(Data Analysis Expression)식을 사용해야 한다.

- 파워쿼리의 '그룹과'기능은 행 별 데이터의 요약과 집계를 편리하게 해줌으로써 데이터를 조금 더 효율적으로 관리할 수 있게 해준다.

- 그룹화 기준열을 선택하고 이에 따라 집계하기 원하는 형태(합계 또는 행 수 등)와 열을 선택해주면 된다.

▶ 데이터 통합

- 서로 다은 정보를 가지고 있는 테이블을 공통의 열을 통해 연결하거나 하나의 테이블로 병합하면 더욱 다양한 측면에서 정보를 생산하고 분석할 수 있다.

- 서로 다른 테이블을 통합하기 위해서는 기본적으로 두 테이블을 이어줄 수 있는 Key 값(공통 값)이 있어야 한다.

- 1:다 형태의 테이블 결합은 엑셀의 VLOOKUP 함수를 통해서 가능하다.

- 파워쿼리에서는 1대 다 대응의 내부조인 뿐만 아니라 외부조인과 복수 Key 값을 통한 통합이 가능하다.

 테이블 조인의 형태

    · 내부조인 : 두 테이블의 Key 값이 일치하는 행만 통합하여 테이블을 만드는 연결 형태

    · 외부조인 : 두 테이블의 Key 값이 일치하는 행 뿐만 아니라 일치하지 않는 행도 포함하여 통합 테이블을 만드는 형태

- 파워쿼리를 사용하면 'Ket 값'과 조인유형 등을 손쉽게 설정하고 다양한 형태로 테이블을 통합할 수 있다.

- 파워쿼리를 활용하면 다양한 연결 방법을 통해 원하는 형태의 통합된 데이터를 쉽게 만들 수 있다.

*'왼쪽 앤티'는 첫번째 테이블에만 있는 행을 나타내는 연결 방법이고 '오른쪽 앤티'는 두번째 테이블에만 있는 행을 나타내는 연결 방법

- 파워쿼리를 활용한 데이터 통합의 장점

    · 원본 데이터가 업데이트 될 때 추가적인 작업 없이 손쉽게 이를 통합 데이터에 반영할 수 있다.

    · 다양한 형태의 연결 방법을 활용하여 정형화된 테이블을 만들 수 있다.

▶ 데이터 수집 사례

- 파워쿼리를 활용하면 여러 파일로 흩어져 있는 데이터를 하나의 정형화된 테이블로 만들 수 있다.

- 쿼리 함수를 통한 파일 통합에 대한 이해

    · 파워쿼리의 함수는 동일한 형태로 반복되는 전처리작업(쿼리 작업)을 한번에 할 수 있도록 지원해주는 기능이다. 쿼리를 작성한 후 해당 쿼리를 함수화 하면 동일한 작업을 원하는 인수에 모두 적용할 수 있다.

- 함수화 된 쿼리작업 : 해당 쿼리를 수정함으로써 전체 파일에 적용되는 쿼리 작업을 변경할 수 있다. 이에 따라 통합된 최종 쿼리의 결과 값도 달라진다.

▶ 웹 데이터 수집 기초

- 웹 파싱(Parsing) 또는 크롤링(Crawling)은 보통 프로그래밍 언어를 이용하는게 보통이다.

    하지만 엑셀의 웹 쿼리와 기초적인 HTML 지식만 있다면 엑셀만으로도 거의 비슷한 수준의 웹 파싱을 할 수 있다.

- 엑셀의 웹 쿼리는 엑셀의 기존 외부데이터 가져오기 기능과는 다른 형태와 방식으로 웹 데이터를 가져온다.

- 원하는 정보가 있는 웹 페이지 주소를 입력하면 웹쿼리(파워쿼리)는 해당 페이지의 모든 테이블 자료를 탐색창에 보여준다.

▶ RSS 피드를 활용한 웹 데이터 수집 

- RSS(Rich Site Summary)는 뉴스나 블로그 등의 최신 웹 자료를 쉽게 배포하고 받아 볼 수 있게 디자인된 웹 표준의 한 형태이다. RSS 주소를 사용하면 누구나 뉴스 사업자나 블로거의 최신 자료 또는 (사용자가 설정한) 키워드로 검색된 자료를 잘 정리된 테이블 형태로 받아 볼 수 있다.

- RSS는 정기적으로 배포되는 뉴스나 금융정보 그리고 블로그 글 등을 손쉽게 수집할 수 있게 해준다.

- RSS 주소는 사업자들이 자신들의 자료를 배포할 목적으로 공개하는 것이 일반적인데, RSS 주소가 공개되지 않은 경우에도 뉴스나 블로그 등의 구독 형 웹 자료는 그 주소를 어렵지 않게 찾아 낼 수 있다.

- 또한 사업자가 정보를 제공하려고 의도하지 않은 웹 사이트 정보도 사용자가 강제로 RSS 주소를 만들어 불러 오는 것이 가능하다.

- 이 방법을 잘 활용하면 웹 상의 거의 모든 자료를 RSS를 통해 불러 올 수 있다. 단, 이를 위해서는 RSS 주소를 만들어 주는 웹 서비스를 이용해야 한다.

- 웹 쿼리를 통해 연결한 웹 페이지는 보통 복합 테이블 구조로 테이블을 이루고 있다. 해당 데이터를 확인하기 위해서는 직접 클릭하거나 (클릭 후 취소) 셀의 빈 공간을 클릭한다.

- 파워쿼리 Language (M 코드) : 쿼리 편집기의 단계별 쿼리 동작을 나타내는 코딩 언어

- 한 번 작성된 쿼리는 쿼리편집기 오른쪽 적용된 단계에 기록되고 언제든 다시 사용할 수 있다.

- 적용된 단계의 쿼리 작업은 각각 독립적으로 수정이 가능하다.

- 뿐만 아니라 '적용된 단계' 창을 거치지 않고서도 '고급편집기'를 통해 각 단계별 쿼리동작을 수정하거나 삭제 또는 추가가 가능하다.

- 원본만 다른 두 쿼리는 정확히 같은 작업을 거쳐 같은 형태의 데이터를 반환한다.

- 같은 형태의 데이터는 '쿼리 추가' 기능을 활용하여 하나의 테이블로도 만들 수 있다.

▶ API를 활용한 웹 데이터 수집 

- API(Application Programming Interface) : 다른 응용 프로그램에서 특정 소프트웨어를 사용할 수 있도록 해당 소프트웨어의 운영 체제 또는 기능을 제어할 수 있도록 해주는 인터페이스

- 쉽게 이야기 하면 하나의 응용 프로그램이 가지고 있는 데이터나 기능을 다른 프로그램에서 (또는 사용자가) 사용할 수 있도록 이를 요청하는 통로라고 이야기할 수 있다.

- 기존에 만들어진 소프트웨어나 축적된 데이터를 잘 활용하여 비용을 줄이기 위해서는 공급자가 제공하는 API를 잘 활용할 수 있어야 한다.

- 새로운 데이터가 필요할 때마다 이를 수집하는 것은 비효율적이다. 따라서 효과적인 데이터 분석을 위해서는 필요로 하는 데이터를 어떻게 하면 효과적으로 확보 할 수 있는지에 대해서 아는 것이 무엇보다도 필요하다.

- API를 활용한 데이터 수집 프로세스

    1단계 : 필요한 데이터 확인

    2단계 : 인증키 발급 받기

    3단계 : 조회 변수 함수화 하기

- API는 기본적으로 제 3자에게 데이터 또는 기능에 대한 접근을 허가하는 것이기 때문에 이를 '승인키'라는 Access Code로 관리하는 것이 필요하다.

- 엑셀 웹 쿼리로 API 형태의 데이터를 불러오기 위해서는 RSS Feed와 같이 호출 주소를 만들어 주어야 한다.

- 웹 호출 주소는 보통 해당 데이터의 기술 문서를 통해 확인할 수 있다.

- 호출 주소는 키 값을 포함하고 각각의 필터링에 적용할 요청 변수로 구성된다.

- 요청 변수 : 전체 데이터에서 필터링 하기 원하는 조건을 설정해 주는 변수

- 요청 변수는 기술문서를 통해 확인할 수 있다.

- API 주소를 만들 때 요청변수는 '&'를 통해 연결한다.

- 공공데이터 포털에서는 특정 데이터의 API 주소가 제대로 동작하는지 먼저 확인해 볼 수 있다.

- 조회변수를 확인한 후 '미리보기'를 클릭하면 (브라우저에서) 최종 요청 주소를 확인할 수 있다.

(최종 주소는 기술문서를 통해서도 확인 할 수 있다.)

- 공공데이터 포털을 활용하여 증권정보(배당정보) 가져오기

▶ 파워쿼리 함수와 매개변수

- 파워쿼리 함수는 동일한 작업(데이터 전처리 등의 쿼리작업)을 각각 다른 대상의 소스에 반복적으로 사용해야 하는 경우 해당 작업을 여러 데이터 소스에 쉽고 빠르게 적용할 수 있게 해준다.

- 파워쿼리는 엑셀과 달리 VBA 등의 프로그램 언어를 사용하지 않더라도 직접 함수를 만들 수 있는 기능을 제공한다.

- 함수를 만들기 위해서는 보통 인수에 해당하는 '매개변수'가 필요하다.

- 매개변수를 만들면 해당 변수가 쿼리 창에 하나의 개체로 인식되며 데이터 창에서 현재 입력된 매개변수를 직접 확인 할 수 있다.

- 매개변수는 보통 원본 데이터 또는 파일, 특정 값(숫자, 문자 또는 테이블), 등의 모든 쿼리 작업 대상이 될 수 있다.

1. 매개변수 직접 입력을 통한 함수 호출 

2. 사용자 지정함수 추가(열 추가) 기능으로 함수 호출하기

    · 매개변수를 직접 입력하는 방법은 독립된 매개변수를 활용하여 데이터를 호출하기는 편리하지만 동시에 여러 매개변수의 데이터를 호출하는 것은 불가능하다.

    · 예를 들어 여러 매개변수 값의 데이터를 동시에 불러오고자 하는 경우 이를 테이블로 작성하여 사용자 지정함수 추가 기능을 활용하면 동시에 여러 매개변수의 데이터를 불러올 수 있다.

    · 이를 위해서는 매개변수가 저장된 테이블이 필요하다.

▶ 파워피벗(Power Pivot) 이해

- 파워피벗은 효과적인 데이터 분석과 효율적인 데이터 모델을 만들기 위한 엑셀의 추가기능이다.

- 파워피벗을 활용하면 여러 소스의 대용량 데이터를 손쉽게 가공하고 분석하여 사용자와 공유할 수 있다.

- 파워피벗의 5가지 특징 

    1. 백만 행 이상의 대용량 데이터를 다양한 소스로부터 가져올 수 있다.

    2. 데이터에 대한 복잡한 수식과 빠른 계산을 지원해 (In Memory 계산 방식 지원) 효과적인 데이터 분석 모델을 만들 수 있도록 해준다.

    3. 데이터르 손쉽게 연결하여 새로운 데이터 모델을 만들 수 있도록 지원한다,

    4. 대용량 데이터의 크기를 줄여 준다.

    5. 다양한 데이터 조합 및 께산을 가능하게 해주는 DAX(Data Analysis Expression) 수식을 지원한다.

    Big Data -> Power Pivot -> Data Analysis

- Excel 데이터 분석 vs Power Pivot 데이터 모델링 (언제 활용하는 것이 효율적일까?)

- Office 365 엑셀 2016을 사용하는 경우 아래 두 제품군을 구독 또는 사용해야 파워피벗 기능을 사용할 수 있다.

    · Office 365 Pro Plus

    · Office 365 E3

- 아래 제품군은 파워피벗이 포함되어 있지 않다.

    · Office 365 Home, Office 365 Personal, Office 365 Business Exxentials, Office 365 Business, Office 365 Business Premiun, Office 365 Enterprise

- 엑셀 2016에서는 '옵션' -> '추가기능' -> 'COM추가기능' -> 'Power pivot'메뉴를 통해 파워피벗 기능을 활성화 시킬 수 있다.

*엑셀 2010 Professional plus 이상 버전 그리고 엑셀 2013버전에서는 파워피벗 ADD-IN 기능을 내려 받아 사용할 수 있다.

- 정형화된 테이블을 파워피벗으로 불러와야 손쉬운 데이터 모델링이 가능하다. 따라서 엑셀 또는 파워쿼리를 활용하여 먼저 데이터를 정형화된 테일블 형태로 만들고 이를 파워피벗으로 연결해 분석 모델을 만드는 것이 일반적인 프로세스이다.

- 파워피벗은 파워쿼리와 마찬가지로 독립된 편집 창을 갖는다.

- 데이터를 불러온 이후는 위 메뉴들을 통해 데이터를 가공하거나 연결하여 데이터 모델을 만들 수 있다.

- 파워피벗은 원 데이터(Raw Data) 연결 시점부터 데이터 쿼리가 가능하다. 따라서 원하는 데이터만 선별하여 불러오는 것이 가능하다.

▶ 파워피벗(Power Pivot) 활용

- 파워피벗의 '측정값'과 '열 추가' & DXA(Data Analysis Expression)

- DAX는 데이터 분석을 위해서 Power Pivot 이나 Power Bl 등 SQL 데이터 기반에서 사용되는 동적 언어로 데이터 가공과 처리 그리고 분석에 매우 효과적이며 엑셀 함수와 유사한 문법 구조를 가지고 있다.

- '열 추가'를 활용한 계산식 만들기 (파생변수 만들기)

- 전년도 매출액과 올해 매출액을 비교할 수 있는 측정값을 만들기 위해 날짜 Table을 삽입한다.

매출액 측정값에 사용할 DAX 함수 정리

- 엑셀의 계산식과 같이 일반적인 계산도 측정값으로 만들어 피벗테이블의 필드 값으로 만들 수 있다.