[파워쿼리기능소개] 1편. 전체 포스팅 개요

드디어 1편 '전체 포스팅 개요와 화면 구조의 간단한 설명' 에 대해 시작합니다. 

포스팅 계획 소개에서 계획한대로 총 8개 주제로 파워쿼리에 대한 소개 포스팅을 시작합니다. 그 첫번째로 전체 포스팅의 개요와 화면 구조 그리고 파워쿼리에서 의미하는 ETL에 대해서 간단히 살펴보겠습니다. 

 

1. 포스팅 개요 

포스팅은 다음과 같이 구성됩니다. 

• 데이터 가져오기
• 가져온 데이터를 엑셀에 로드하기 , 데이터 갱신 하기 (새로고침) 
• 기본 변환 : 쿼리내 변환과 관련된 각 탭 메뉴 설명
• 쿼리 병합 , 추가 , 관리 , 쿼리 종속성

크게 나누어 보면 가장 기본인 데이터를 가져오는 방법과 엑셀 시트에 추출 및 변환된 데이터를 로드 및 갱신하는 방법을 2~4 편에 걸쳐 소개를 하고 5~8편에서는 4편에 걸쳐 리본메뉴의 기능을 중심으로 변환 방법을 쿼리내에서 데이터를 다루는 방법과 쿼리를 병합/추가/복제&참조 그리고 종속성에 대한 설명까지 각각 진행할 계획입니다. 

특히 5편에서 다룰 기능이 많고 중요성(활용빈도)가 높은 만큼 여러 개의 포스팅으로 나누어 진행하고 사용수준이 높아짐에 따라 꼭 필요한 쿼리를 관리하는 노하우도 다시 고민하면서 적어볼 생각입니다. 

포스팅을 하다가 지쳐서 포기 하지 않도록 힘내야 겠네요. 

2. 화면 설명 

파워쿼리 화면은 이렇게 생겼습니다.

오피스 제품이 확 티나는 리본 메뉴로 구성이 되어 있습니다. 그림의 번호대로 보면 

1. 현재 생성된 쿼리 목록 : 쿼리와 매개변수, 사용자 함수등의 쿼리 객체들로 구성됩니다. 그룹을 만들어 관리가 가능합니다.
2. 함수입력 : 엑셀과 같이 함수가 입력되는 창이 존재하는데 각 적용단계별 함수가 표시됩니다. 수동으로 작성도 가능하지만 복잡한 경우가 아니면 대부분 리본 메뉴의 단계로 해결이 가능하므로 직접 작성할 일은 그리 많지 않습니다. 
3. 데이터 미리보기 : 로드될 데이터를 미리보기로 제공합니다. 변환과정에 따라 미리보기가 계속 변하기 때문에 단계별 수행내역을 직관적으로 확인할 수 있습니다. 
4. 적용된 단계 : 쿼리의 원본 부터 변환이 진행된 각 단계가 한 줄씩 추가되며 구성됩니다. 각 단계를 클릭하면 해당 단계의 적용된 내역을 확인 할 수 있고 수정도 가능합니다. 수정시에 하위 단계에 영향을 줄 수도 있습니다. 예를 들어 데이터 타입을 숫자에서 텍스트로 변환을 했는데 하위 단계에서 숫자연산을 하게 되면 하위 단계는 오류가 발생합니다. 

리본 메뉴를 제외 하고서는 크게 위의 4가지 영역을 가지고 작업이 진행 됩니다. 

파워쿼리 언어 설정에 대해서는 과거 포스팅 한것이 있으니 참고하시면 좋을 것 같네요.

2019/06/18 - [엑셀/파워피벗 연구] - 파워쿼리 옵션

파워쿼리 옵션

 

3. 데이터 가져오기 - 변환 - 로드 (ETL)

파워쿼리의 수행 원리를 조금 이해하면 기능을 익히는 데 큰 도움이 될 것 같아 ETL에 대해서 간단히 소개합니다. 아래는 위키백과에 정의된 ETL에 대한 내용입니다. 

추출, 변환, 적재(Extract, transform, load, ETL)는 컴퓨팅에서 데이터베이스 이용의 한 과정으로 특히 데이터 웨어하우스에서 다음을 아우른다:

• 동일 기종 또는 타기종의 데이터 소스로부터 데이터를 추출한다.
• 조회 또는 분석을 목적으로 적절한 포맷이나 구조로 데이터를 저장하기 위해 데이터를 변환한다.

• 최종 대상(데이터베이스, 특히 운영 데이터 스토어, 데이터 마트, 데이터 웨어하우스)으로 변환 데이터를 적재한다.

주로 DB 간의 데이터를 주고 받는 과정에서 컨버젼이 필요한 경우에 해당 과정을 의미합니다. 하지만 엑셀의 파워쿼리도 비록 로드되는 최종 대상이 거창한 무엇이 아니라 엑셀이지만 의미 상으로는 ETL과 동일합니다. 파워쿼리는 변환 결과를 엑셀 시트나 파워쿼리의 데이터 모델로 로드 하게됩니다. 또는 로드 없이 연결만 유지하는 쿼리를 구성할 수 도 있습니다. 

파워 쿼리를 사용하는 세 가지 주요 단계: 데이터 가져오기, 변환 및 로드

MS 파워쿼리 자습서 파일에 있는 단계를 표현한 그림입니다. 파워쿼리는 저에게 변환을 위해 노력했던 과거의 여러 노하우 중 많은 부문을 대체하는 중입니다. 경영관리 시스템 관련 컨설팅이 직업인 저에게는 많은 데이터를 프로세스에 맞추어 분석하는 것이 주 업무이고 따라서 이런 저런 구조로 데이터를 변환하고 요약하는 방법을 연구하는 것이 매우 중요합니다. 파워쿼리는 SQL을 몇십줄씩 타이핑하고 돌려보는 수고를 클릭 몇번으로 줄여주고 엑셀 시트에 피벗테이블을 몇개씩 만들어가며 요약하던 일들을 한번에 해결해 주었습니다. 

데이터 구조에 대한 분석 업무 뿐만 아니라 여러 실적을 집계하고 관리하는 것에도 매우 효율적인 도구 입니다. 조금만 일의 방법을 파워쿼리로 작업이 가능하도록 맞추고 기능을 적극 이용해보세요. 자동화가 그리 어려운일이 아닙니다. 매일 루틴한 일에서 벗어날 수 있도록 시간을 투자하면 더 크고 좋을일에 집중할 시간과 여유를 갖게 될 것입니다. 

끝으로 자습서 파일에서 파워쿼리를 사용하는 팁을 하나 주네요. 모니터 하나 사야 겠습니다. 아니면 엄청 큰걸 하나 쓰거나요.

파워쿼리 편집기 창이 활성화 되면 원본인 엑셀 창은 사용을 못합니다. 파워쿼리 편집기 창을 닫아야만 사용가능합니다. 팁이라기 보다는 어쩔 수 없는 거죠.