엑셀 파워 쿼리(Power Query) 데이터 전처리

엑셀은 우리에게 너무나 익숙한 도구지만, 방대한 데이터를 효율적으로 다루기란 쉽지 않습니다.

특히 여러 소스에서 데이터를 가져와 가공하고 분석하는 과정은 많은 시간과 노력을 필요로 하죠. 하지만 파워 쿼리(Power Query)가 있다면 이야기는 달라집니다.

엑셀의 숨겨진 보석, 파워 쿼리는 데이터 전처리를 혁신적으로 자동화해주는 강력한 도구입니다.

엑셀 파워 쿼리란 무엇인가요?

파워 쿼리는 엑셀에 내장된 ETL(Extract, Transform, Load) 도구입니다. 

쉽게 말해, 다양한 원본(웹 페이지, 데이터베이스, CSV 파일 등)에서 데이터를 추출(Extract)하고, 필요한 형태로 변환(Transform)한 뒤, 엑셀로 로드(Load)하는 일련의 과정을 자동화해줍니다.

코드없이 직관적인 사용자 인터페이스를 통해 데이터 클렌징, 병합, 피벗 등 복잡한 데이터 작업을 손쉽게 처리할 수 있습니다. 

한 번 설정해두면 다음부터는 클릭 몇 번으로 최신 데이터를 불러와 동일한 변환 과정을 자동으로 적용할 수 있어, 반복적인 수작업을 줄여줍니다.

파워 쿼리, 왜 사용해야 할까요? (장점)

파워 쿼리는 수많은 장점을 가지고 있지만, 그중 핵심적인 몇 가지를 꼽아보겠습니다.

• 시간 절약 및 생산성 향상: 반복적인 데이터 전처리 작업을 자동화하여 엄청난 시간을 절약할 수 있습니다. 매일, 매주 데이터를 수동으로 복사하고 붙여넣고 가공하는 데 드는 시간을 이제 다른 중요한 업무에 할애할 수 있습니다.
• 데이터 정확성 향상: 수작업으로 인한 오류 가능성을 현저히 줄여줍니다. 정해진 규칙에 따라 일관된 변환이 이루어지므로 데이터의 신뢰도가 높아집니다.
• 다양한 데이터 원본 연결: 엑셀 파일, CSV, 텍스트 파일, 웹 페이지, SQL 서버, SharePoint 등 상상할 수 없을 정도로 다양한 데이터 원본에 연결할 수 있습니다. 더 이상 데이터 원본이 다르다고 따로 작업할 필요가 없습니다.
• 데이터 보안 강화: 원본 데이터를 직접 수정하지 않고 복사본을 가져와 변환하기 때문에 원본 데이터의 손상 위험이 없습니다.
• 재사용성 및 자동화: 한 번 구축한 쿼리는 저장해두고 필요할 때마다 재사용할 수 있습니다. 새로운 데이터가 들어와도 새로고침 버튼 하나로 모든 변환 과정을 자동으로 적용할 수 있습니다.
• 사용자 친화적인 인터페이스: 코딩 지식이 없어도 누구나 쉽게 사용할 수 있도록 직관적인 사용자 인터페이스를 제공합니다. 클릭 몇 번으로 대부분의 작업을 처리할 수 있습니다.
  
  

파워 쿼리, 아쉬운 점도 있나요? (단점)

모든 도구가 완벽할 수는 없듯이, 파워 쿼리에도 몇 가지 아쉬운 점이 있습니다.

• 학습 곡선: 처음 파워 쿼리를 접하는 사용자에게는 다소 생소하게 느껴질 수 있으며, 기본적인 개념과 사용법을 익히는 데 약간의 시간이 필요합니다. 하지만 일단 익숙해지면 그 진가를 발휘합니다.
• 복잡한 로직 처리의 한계: 매우 복잡하고 조건이 많은 데이터 변환 로직의 경우, 파워 쿼리만으로는 한계가 있을 수 있습니다. 이럴 때는 DAX (Power Pivot의 데이터 분석 표현식)나 VBA 등 다른 도구와의 연계가 필요할 수 있습니다.
• 대용량 데이터 처리 속도: 수백만 행 이상의 초 대용량 데이터를 처리할 때는 속도가 느려질 수 있습니다. 이 경우 Power BI 등 더 강력한 BI 도구를 고려해야 할 수도 있습니다.
• Excel 버전 호환성: 오래된 엑셀 버전에서는 파워 쿼리 기능이 제한적이거나 아예 없을 수 있습니다. (엑셀 2010/2013은 추가 기능 설치, 엑셀 2016부터는 기본 내장)
  
  

실무 예제로 파워 쿼리 정복하기!

이제 실제 업무에서 파워 쿼리를 어떻게 활용할 수 있는지 간단한 예시를 통해 살펴보겠습니다.

예제: 여러 지역의 판매 데이터를 하나로 통합하고 분석 준비하기
당신은 전국에 걸쳐 여러 지점을 운영하는 회사의 영업 관리자라고 가정해 봅시다. 

각 지점에서 매일 또는 매주 판매 실적을 엑셀 파일로 보고받고 있습니다. 

문제는 각 파일의 형식은 비슷하지만, 파일이 여러 개로 흩어져 있고, 일부 데이터는 정리가 필요합니다. 

파워 쿼리를 이용하면 이 모든 과정을 자동화할 수 있습니다.

1. 데이터 준비:

• "서울점_판매.xlsx" 파일 (시트1: 날짜, 상품명, 수량, 단가)
• "부산점_판매.xlsx" 파일 (시트1: 날짜, 상품명, 수량, 단가)
• "제주점_판매.xlsx" 파일 (시트1: 날짜, 상품명, 수량, 단가)

이 파일들이 특정 폴더(예: C:\판매데이터)에 있다고 가정합니다.

2. 파워 쿼리로 데이터 가져오기:

• 엑셀을 열고 [데이터] 탭 -> [데이터 가져오기] -> [파일에서] -> [폴더에서]를 클릭합니다.
• 판매 데이터 파일들이 있는 폴더 경로를 입력하고 [확인]을 클릭합니다.
• 폴더 내의 파일 목록이 나타나면 [변환] 버튼을 클릭하여 파워 쿼리 편집기를 엽니다.
  
  

3. 데이터 변환 및 통합:

파워 쿼리 편집기가 열리면 다음과 같은 변환 과정을 거칩니다.

• [Content] 열 클릭 후 [결합] 버튼 (이중 화살표 아이콘) 클릭: 각 엑셀 파일의 시트 내용을 통합합니다.
• 필요 없는 열 제거: Source.Name (파일 이름), Item (시트 이름) 등 분석에 필요 없는 열을 제거합니다. (해당 열 선택 후 마우스 우클릭 -> [열 제거])
• 데이터 형식 지정: 날짜 열은 날짜 형식으로, 수량과 단가 열은 숫자로 변환합니다. (해당 열 선택 후 [변환] 탭 -> [데이터 형식] 클릭)
• 새 열 추가 (매출액 계산): 수량과 단가를 곱하여 매출액 열을 새로 만듭니다. ([열 추가] 탭 -> [사용자 지정 열] 클릭 후 수식 입력: [수량] * [단가])
• 지점 정보 추가: 각 파일명(Source.Name 열의 .xlsx 확장자를 제거하고)에서 지점 정보를 추출하여 새로운 지점 열을 만듭니다. (만약 Source.Name 열을 제거했다면 다시 해당 폴더에서 가져오는 과정을 통해 Source.Name을 포함하여 진행해야 합니다.)
  
  

4. 엑셀로 로드:

모든 변환 작업이 완료되었다면, [홈] 탭 -> [닫기 및 로드]를 클릭하여 변환된 데이터를 엑셀 시트로 가져옵니다.

이제 모든 지점의 판매 데이터가 하나의 시트에 통합되고, 깔끔하게 정리된 것을 확인할 수 있습니다.

5. 데이터 업데이트:

만약 다음날 새로운 판매 데이터 파일(예: "대구점_판매.xlsx")이 C:\판매데이터 폴더에 추가된다면, 엑셀 시트에서 데이터가 있는 표를 클릭 후 [데이터] 탭 -> [모두 새로 고침]만 클릭하면 새로 추가된 데이터가 자동으로 통합되어 반영됩니다.

엑셀 파워 쿼리는 데이터 전처리라는 지루하고 반복적인 작업을 혁신적으로 바꿔줄 수 있는 강력한 도구입니다.

처음에는 조금 낯설게 느껴질 수 있지만, 몇 번 사용해보면 그 편리함과 효율성에 놀라게 될 것입니다.
이제 더 이상 복사, 붙여넣기, 수동 정리에 시간을 낭비하지 마세요!

파워 쿼리를 활용하여 데이터 처리의 전문가가 될 수 있습니다