1. 파워 쿼리(Power Query)란?
파워 쿼리는 여러 종류의 외부 데이터 소스에서 데이터를 가져온 후, 필요에 따라 데이터를 정제하거나, 변환 및 재구성할 수 있는 도구입니다.
파워 쿼리는 엑셀 및 파워BI Desktop등 Microsoft사의 여러 제품에 포함되어 있으므로 추가적인 설치없이 간단하게 사용할 수 있습니다.
엑셀에서 데이터 정리작업을 할 때 수작업으로 복사하여 붙여넣고 기존의 엑셀의 여러 기능을 이용하여 정리할 수 있지만, 작업이 번거롭고, 시간이 오래 걸리고 원본 데이터를 변경해야 하는 경우도 많습니다.
예를 들어 텍스트 나누기만 하더라도 새로 생기는 열의 수만큼 열을 추가해야 하는 등 작업 자체가 번거롭습니다. 대신 파워쿼리를 이용하면 원본 데이터는 그대로 두고 변환작업을 통해 간단히 데이터를 정리할 수 있고 작업 과정을 간단하게 자동화할 수 있습니다.
2. 엑셀에서 파워 쿼리 사용
엑셀에서는 2016이상 버전부터 파워쿼리가 지원됩니다.
파워쿼리 메뉴가 따로 있는 것이 아니라 다음과 같이 기본 기능으로 포함되어 있습니다.
엑셀 2016의 파워 쿼리 메뉴
엑셀 2016에는 리본메뉴 [데이터] 탭 → [가져오기 및 변환] 그룹에서 파워쿼리 기능을 사용할 수 있습니다.
[주의!] [가져오기 및 변환] 그룹의 왼쪽에 있는 [외부 데이터 가져오기] 그룹에 있는 메뉴는 파워쿼리 기능이 아니고 이전부터 사용되던 가져오기 기능입니다.
엑셀 2019/2021, Microsoft 365의 파워 쿼리 메뉴
리본메뉴 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹에서 파워쿼리 기능을 사용할 수 있습니다.
엑셀 2013, 2010 버전에서는 Excel 추가기능을 다운로드해서 설치후 사용할 수 있습니다.
다운로드 링크 : https://www.microsoft.com/ko-KR/download/details.aspx?id=39379
추가기능을 추가하면 Office 2016이상의 버전과 달리 아래와 같이 파워쿼리 메뉴가 따로 생깁니다.
[주의!] 엑셀 2013, 2010 버전의 리본메뉴 [데이터] 탭에 있는 [외부 데이터 가져오기] 그룹에 있는 메뉴는 파워쿼리 기능이 아니고 이전부터 사용되던 가져오기 기능입니다.
3. 파워 쿼리 기능
파워쿼리 기능은 다양한 원본 데이터(엑셀, Database,웹, CSV파일, Text파일 등)로부터 1)데이터를 가져와서 2)변환하고 3)로드하는 3가지 기능으로 요약할 수 있습니다.
1) 데이터 가져오기
다양한 형태의 외부 데이터를 연결하여 가져올 수 있습니다.
- 엑셀을 포함하여 CSV, Text파일, Database에 직접 연결하여 데이터 가져오기
- 비정형화된 외부 데이터 소스에서 정형화된 부분을 선택하여 데이터를 가져오기(웹, PDF파일 등)
- 파일이 있는 폴더를 지정하여 데이터를 가져와서 합치기 등
2) 변환
데이터를 정리하고 원하는 형태로 변환할 수 있습니다. 엑셀의 기존 기능을 이용해서 정리하고 변환할 수 있지만 파워쿼리를 이용하면 훨씬 간편하게 처리할 수 있습니다.
- 데이터에 포함된 오류제거
- 중복된 항목 제거
- 데이터 형식 변경(통화,정수,백분율,텍스트 등으로 변경)
- 열을 추가하거나 제거, 필터링
- 구조화되지 않은 데이터를 구조화(언피벗)
3) 로드
- 최종 결과를 엑셀 등 원하는 위치로 가져옴
4. 파워쿼리 사용해 보기
실습: [판매실적] 시트
1) 텍스트 나누기
다음 자료의 [B]열에는 '거래처명' 뿐만 아니라 '대리점 유형' 정보도 같이 포함되어 있고 괄호로 구분되어 있습니다.
이런 데이터를 분리하기 위해서는 리본메뉴의 [데이터] 탭의 '텍스트 나누기' 기능을 많이 이용합니다. 그런데 문제는 텍스트 나누기를 이용하면 새로 생기는 열을 추가해야 하고 다음에 이런 자료가 추가되면 반복되는 작업을 계속해야 한다는 것입니다.
대신 파워쿼리를 이용하면 기존 기능인 '텍스트 나누기'와 유사하지만 기능이 훨씬 강력하고 반복되는 작업을 자동화할 수 있는 장점이 있습니다.
그럼 파워쿼리 기능을 이용하여 텍스트 나누기를 해 보겠습니다.
01 ① 원본 데이터의 아무셀이나 선택한 후 ② [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → [테이블 범위에서]를 누르면 [표 만들기] 대화상자가 나타납니다.
③ 표의 범위가 가져오고자 하는 데이터 범위와 맞는 지 확인 후 <확인> 버튼을 누릅니다.
02 [Power Query편집기] 창이 나타납니다. 파워쿼리(Power Query)화면으로 이 화면에서 데이터를 변환하고 로드하는 작업을 할 수 있습니다.
메뉴 밑의 데이터 영역을 보면 엑셀 시트의 데이터를 가져온 것을 알 수 있습니다.
03 '거래처명' 열을 나누어야 하므로 ① '거래처명' 열을 선택하고 [홈] 탭 → [변환] 그룹 → ② [열 분할] 메뉴 → ③ [구분 기호 기준]을 선택합니다.
04 [구분 기호 선택 또는 입력]에 ① 구분자로 사용할 괄호 '('를 입력한 후
② <확인>을 누릅니다.
05 '거래처명' 열이 거래처명1, 거래처명2로 분리되었습니다.
'거래처명2'에는 우괄호가 남아 있으므로 '값 바꾸기'기능을 이용하여 괄호를 제거해 줍니다.
① '거래처명2' 열을 선택하고
② [홈] 탭 → [변환] 그룹 →[값 바꾸기]를 선택해 [값 바꾸기] 대화상자를 실행합니다.
③ [찾을 값]에 우괄호 ')'를 입력하고 [바꿀 항목]은 비워두고 ④ <확인>을 누르면 누릅니다.
06 '거래처명 2'열의 우괄호가 제거된 것을 확인할 수 있습니다. 리본메뉴 왼쪽의 [닫기 및 로드]를 누릅니다.
07 변환된(텍스트 나누기가 된) 데이터를 엑셀로 가져옵니다. 이때 원본 시트가 아니라 별도의 시트가 만들어지면서 가져옵니다.
2) 새로 고침으로 작업 자동화
여기까지의 작업은 약간 편해졌다는것 외에는 평소 엑셀의 기존 기능으로 많이 하던 '텍스트 나누기'와 크게 다르지 않습니다.
파워쿼리의 진정한 능력은 지금부터라고 할 수 있습니다.
01 ① 원본 시트의 19행에 새로운 데이터를 추가합니다
※ 시트가 원래 이런 모양이 아니었는데 파워쿼리에서 원본을 참조하면 이렇게 '표' 형식으로 자동으로 변경됩니다.
02 조금 전에 파워쿼리로 변환된 결과를 로드한 시트로 돌아와서 ① 아무 셀이나 선택하고 메뉴에서 ② '새로 고침'을 누릅니다.
03 '새로 고침'하기 전에는 없는 19행이 새로 생겼고, 거래처명이 거래처명1, 거래처명2로 자동으로 분할되어 있습니다.
이와 같이 파워 쿼리를 이용해서 한 번 작업해 놓으면 원본 시트에 값을 입력하고 결과 시트에서 '새로 고침'만 하면 자동으로 작업을 반복해서 처리해 줍니다. 이 기능을 이용하면 복잡하고 반복되는 많은 작업들을 자동화할 수 있습니다.
이상으로 파워쿼리가 가진 강력한 기능의 극히 일부를 소개하고 글을 마칩니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
첨부 파일 2개 모두 다운로드 되지 않습니다.
첨부파일 새로 올렸습니다. 이제 다운로드 가능합니다.