Home > 강좌 > 엑셀수식강좌 > 04. 엑셀을 제대로 쓰는 데이터처리 > 파워 쿼리(Power Query) 소개

파워 쿼리(Power Query) 소개

  • by

1. 파워 쿼리(Power Query)란?

파워 쿼리는 여러 종류의 외부 데이터 소스에서 데이터를 가져온 후, 필요에 따라 데이터를 정제하거나, 변환 및 재구성할 수 있는 도구입니다.

파워 쿼리는 엑셀 및 파워BI Desktop등 Microsoft사의 여러 제품에 포함되어 있으므로 추가적인 설치없이 간단하게 사용할 수 있습니다.

엑셀에서 데이터 정리작업을 할 때 수작업으로 복사하여 붙여넣고 기존의 엑셀의 여러 기능을 이용하여 정리할 수 있지만, 작업이 번거롭고, 시간이 오래 걸리고 원본 데이터를 변경해야 하는 경우도 많습니다.

예를 들어 텍스트 나누기만 하더라도 새로 생기는 열의 수만큼 열을 추가해야 하는 등 작업 자체가 번거롭습니다. 대신 파워쿼리를 이용하면 원본 데이터는 그대로 두고 변환작업을 통해 간단히 데이터를 정리할 수 있고 작업 과정을 간단하게 자동화할 수 있습니다.

실습용 엑셀파일 다운로드 : 데이터처리-파워쿼리소개.xlsx

 

2. 엑셀에서 파워 쿼리 사용

엑셀에서는 2016이상 버전부터 파워쿼리가 지원됩니다.

파워쿼리 메뉴가 따로 있는 것이 아니라 다음과 같이 기본 기능으로 포함되어 있습니다.

<엑셀 2016에서 파워쿼리 메뉴>

엑셀 2016에는 리본메뉴 [데이터] 탭 → [가져오기 및 변환] 그룹에서 파워쿼리 기능을 사용할 수 있습니다.

파워 쿼리(Power Query) 소개

[주의!] [가져오기 및 변환] 그룹의 왼쪽에 있는 [외부 데이터 가져오기] 그룹에 있는 메뉴는 파워쿼리 기능이 아니고 이전부터 사용되던 가져오기 기능입니다.

 

<Office 365에서 파워쿼리 메뉴>

리본메뉴 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹에서 파워쿼리 기능을 사용할 수 있습니다.

파워 쿼리(Power Query) 소개

 

엑셀 2013, 2010 버전에서는 Excel 추가기능을 다운로드해서 설치후 사용할 수 있습니다.

다운로드 링크 : https://www.microsoft.com/ko-KR/download/details.aspx?id=39379

추가기능을 추가하면 Office 2016이상의 버전과 달리 아래와 같이 파워쿼리 메뉴가 따로 생깁니다.

파워 쿼리(Power Query) 소개

[주의!]  엑셀 2013, 2010 버전의 리본메뉴 [데이터] 탭에 있는 [외부 데이터 가져오기] 그룹에 있는 메뉴는 파워쿼리 기능이 아니고 이전부터 사용되던 가져오기 기능입니다.

 

3. 파워 쿼리 기능

파워쿼리 기능은 다양한 원본 데이터(엑셀, Database,웹, CSV파일, Text파일 등…)로부터  1)데이터를 가져와서 2)변환하고 3)로드하는 3가지 기능으로 요약할 수 있습니다.

1) 데이터 가져오기

다양한 형태의 외부 데이터를 연결하여 가져올 수 있습니다.

  • 엑셀을 포함하여 CSV, Text파일, Database에 직접 연결하여 데이터 가져오기
  • 비정형화된 외부 데이터 소스에서 정형화된 부분을 선택하여 데이터를 가져오기(웹, PDF파일 등)
  • 파일이 있는 폴더를 지정하여 데이터를 가져와서 합치기 등

2)  변환

데이터를 정리하고 원하는 형태로 변환할 수 있습니다. 엑셀의 기존 기능을 이용해서 정리하고 변환할 수 있지만 파워쿼리를 이용하면 훨씬 간편하게 처리할 수 있습니다.

  • 데이터에 포함된 오류제거
  • 중복된 항목 제거
  • 데이터 형식 변경(통화,정수,백분율,텍스트 등으로 변경)
  • 열을 추가하거나 제거, 필터링
  • 구조화되지 않은 데이터를 구조화(언피벗)

3) 로드

  • 최종 결과를 엑셀 등 원하는 위치로 가져옴

 

4. 간단한 사용 예

1) 텍스트 나누기

아래와 같이 B열에는 '거래처명' 뿐만 아니라 '대리점 유형' 정보도 같이 포함되어 있고 괄호로 구분되어 있습니다.

이런 데이터를 분리하기 위해서는 리본메뉴의 [데이터] 탭의 '텍스트 나누기' 기능을 많이 이용합니다. 그런데 문제는 텍스트 나누기를 이용하면 새로 생기는 열을 추가해야 하고 다음에 이런 자료가 추가되면 반복되는 작업을 계속해야 한다는 것입니다.

대신, 파워쿼리를 이용하면  기존 기능인 '텍스트 나누기'와 유사하지만 기능이 훨씬 강력하고 반복되는 작업을 자동화할 수 있는 장점이 있습니다.

파워 쿼리로 텍스트 나누기

 

그럼, 지금부터 파워쿼리 기능을 이용하여 텍스트 나누기를 해 보겠습니다.

원본 데이터의 아무셀이나 선택한 후 리본메뉴 [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → ① [테이블 범위에서]를 누르면 [표 만들기] 대화상자가 나타납니다.
표의 범위가 가져오고자 하는 데이터 범위와 맞는 지 확인 후 ② <확인> 버튼을 누릅니다.

파워 쿼리로 텍스트 나누기

 

[Power Query편집기]라는 창이 나타납니다. 파워쿼리(Power Query)화면입니다. 이 화면에서 데이터를 변환하고 로드하는 작업을 할 수 있습니다.

메뉴 밑의 데이터 영역을 보면 엑셀 시트의 데이터를 가져온 것을 알 수 있습니다.

파워 쿼리로 텍스트 나누기

 

'거래처명' 열을 나누어야 하므로  ③ '거래처명' 열을 선택하고 ④ 리본메뉴 [홈] 탭 → [변환] 그룹 → [열 분할] 메뉴 → ⑤ [구분 기호 기준]을 선택합니다.

파워 쿼리로 텍스트 나누기

 

다음과 같은 대화상자가 나타나면 [구분 기호 선택 또는 입력]에 ⑥ 구분자로 사용할 괄호 '('를 입력한 후
⑦ <확인>을 누릅니다.

파워 쿼리로 텍스트 나누기

 

'거래처명' 열이 거래처명1, 거래처명2로 분리되었습니다.

'거래처명2'에는 우괄호가 남아 있으므로 '값 바꾸기'기능을 이용하여 괄호를 제거해 줍니다.

⑧ '거래처명2' 열을 선택하고
⑨ 리본메뉴 [홈] 탭 → [변환] 그룹 → [값 바꾸기]를 누르면 [값 바꾸기] 대화상자가 나타납니다.
⑩ [찾을 값]에 우괄호 ')'를 입력하고 [바꿀 항목]은 비워두고
⑪ <확인>을 누르면 누르면

파워 쿼리로 텍스트 나누기

 

다음과 같이 '거래처명 2'열의 우괄호가 제거된 것을 확인할 수 있습니다.
마지막으로 리본메뉴 왼쪽의 ⑫ [닫기 및 로드]를 누르면

파워 쿼리로 텍스트 나누기

 

변환된(텍스트 나누기가 된) 데이터를 엑셀로 가져옵니다.
이때 원본 시트가 아니라 별도의 시트가 만들어지면서 가져옵니다.

파워 쿼리로 텍스트 나누기

 

2)  새로 고침으로 작업 자동화

여기까지의 작업은 약간 편해졌다는것 외에는 평소 엑셀의 기존 기능으로 많이 하던  '텍스트 나누기'와 크게 다르지 않습니다.

파워쿼리의 진정한 능력은 지금부터라고 할 수 있습니다.

①  아래의 원본 시트의  19행에 새로운 데이터를 추가합니다
※ 시트가 원래 이런 모양이 아니었는데 파워쿼리에서 원본을 참조하면 이렇게 '표' 형식으로 자동으로 변경됩니다.

파워 쿼리로 텍스트 나누기

 

조금 전에 파워쿼리로 변환된 결과를 로드한 시트로 돌아와서 ② 아무 셀이나 선택하고 메뉴에서 ③ '새로 고침'을 누릅니다.

파워 쿼리로 텍스트 나누기

 

'새로 고침'하기 전에는 없는 19행이 새로 생겼습니다.
거래처명이 거래처명1, 거래처명2로 자동으로 분할되어 있습니다.

파워 쿼리로 텍스트 나누기

어떻게 된 일일까요?

파워쿼리를 이용해서 한번만 작업을 해 놓으면 원본시트에 값을 입력하고 결과 시트에서 '새로 고침'만 하면 자동으로 작업을 반복해서 처리해 줍니다.

이 기능을 이용하면 복잡하고 반복되는 많은 작업들을 자동화 할 수 있습니다.

이상으로 파워쿼리가 가진 강력한 기능의 극히 일부를 소개하고 글을 마칩니다.

 

[ 엑셀 수식 강좌  - 엑셀 데이터 처리]

 

답글 남기기

이메일 주소는 공개되지 않습니다.