대한민국 직장인들에게 엑셀은 떼려야 뗄 수 없는 도구입니다. 하지만 아이러니하게도 가장 많은 시간을 잡아먹는 도구이기도 합니다. 매일 아침 전날의 데이터를 내려받아 VLOOKUP을 돌리고, 중복된 행을 삭제하며, 날짜 형식을 고치는 일에 귀한 오전 시간을 다 쓰고 있지는 않나요?
과거에는 이러한 ‘노가다’성 업무를 자동화하기 위해 복잡한 VBA(Visual Basic for Applications) 코드를 배워야 했습니다. 그러나 코딩 장벽은 높았고, 유지보수는 더욱 어려웠습니다. 이제 그 대안을 넘어 표준이 된 솔루션이 바로 **엑셀 파워 쿼리(Power Query)**입니다. 본 포스팅에서는 파워 쿼리의 본질부터 실무 성능을 극대화하는 전문가급 기술까지 낱낱이 파헤쳐 보겠습니다.
1. 엑셀 파워 쿼리의 본질: 데이터 엔지니어링의 정수, ETL
1-1. 추출(Extract): 파편화된 데이터의 통합
우리의 데이터는 한 곳에 예쁘게 모여 있지 않습니다. 어떤 데이터는 CSV 파일로, 어떤 데이터는 사내 SQL 서버에, 심지어 어떤 데이터는 웹 사이트의 표 형태로 존재합니다. 파워 쿼리는 이 모든 이기종 소스에 직접 연결하여 데이터를 ‘빨아들입니다’.
1-2. 변환(Transform): M 언어 기반의 자동화 기록부
파워 쿼리 편집기에서 우리가 열을 삭제하거나 형식을 바꾸면, 내부적으로는 **M 언어(M-Code)**라는 함수형 스크립트가 생성됩니다. 사용자는 GUI 버튼을 누르지만, 파워 쿼리는 이를 논리적인 ‘적용된 단계’로 저장합니다.
- 재사용성: 내일 새로운 파일이 들어오면, 버튼 하나만 누르세요. 저장된 M 코드가 첫 단계부터 마지막 단계까지 빛의 속도로 재실행됩니다.
- 투명성: 어떤 가공 과정을 거쳤는지 단계별로 클릭해 보며 ‘타임머신’처럼 확인할 수 있습니다.
1-3. 로드(Load): 분석 목적에 맞는 결과 전송
정제된 데이터를 엑셀 시트에 표로 뿌려줄 것인지, 아니면 보이지 않는 메모리 영역인 ‘데이터 모델’로 보낼 것인지 결정합니다. 이 선택이 전체 파일의 속도를 좌우합니다.
2. 왜 지금 파워 쿼리를 시작해야 하는가?
2-1. 대용량 데이터 처리: 104만 행의 한계를 넘어서
엑셀 시트가 수용할 수 있는 최대 행 수는 1,048,576행입니다. 하지만 실제 비즈니스 데이터는 수백만, 수천만 건에 달하는 경우가 많습니다. 파워 쿼리는 데이터를 시트에 직접 올리지 않고 **’연결만 생성’**하거나 **’데이터 모델(Power Pivot)’**에 로드함으로써 엑셀 엔진 내에서 수백만 행의 분석을 가능케 합니다. 물론, 실제 처리 속도는 사용자의 PC RAM 사양과 CPU 성능에 비례하므로 하드웨어 자원을 고려한 쿼리 설계가 병행되어야 합니다.
2-2. 비파괴적 편집: 원본 데이터의 성역화
VBA나 일반 함수 작업의 무서운 점은 실수로 원본 데이터를 덮어쓰거나 훼손할 수 있다는 것입니다. 파워 쿼리는 원본 데이터에 빨대를 꽂아 ‘읽어오기’만 할 뿐, 원본을 절대로 건드리지 않습니다. 작업 중 논리 오류가 발견되면 ‘적용된 단계’에서 해당 단계만 지우거나 수정하면 그만입니다.
2-3. 복잡한 관계 중심의 데이터 병합
수많은 VLOOKUP 함수는 엑셀 파일을 무겁게 만들고 에러( #N/A )의 주범이 됩니다. 파워 쿼리의 ‘쿼리 병합’ 기능은 SQL의 Join 연산과 동일한 원리로 작동합니다. 여러 개의 조건으로 테이블을 합치거나, 중복을 제거하며 결합하는 작업이 시각적으로 명확하고 훨씬 빠릅니다.
3. 전문가와 초보자를 가르는 고급 최적화 기술
3-1. 성능 최적화의 꽃: 쿼리 폴딩(Query Folding)
파워 쿼리의 진정한 강력함은 쿼리 폴딩에 있습니다. 사용자가 파워 쿼리 편집기에서 수행한 변환 단계(필터링, 그룹화 등)를 파워 쿼리 엔진이 원본 데이터 소스(예: SQL 서버)가 이해할 수 있는 언어로 번역하여 전달하는 기술입니다.
- 왜 중요한가? 1억 건의 데이터 중 특정 날짜의 1만 건만 필요할 때, 내 PC로 1억 건을 다 가져와서 거르는 것이 아니라, 서버에서 1만 건만 추려진 결과물만 가져오기 때문입니다.
- 주의사항: M 코드를 직접 수정하거나 일부 복잡한 함수를 사용하면 폴딩이 깨질 수 있습니다. 단계 우클릭 시 ‘기본 쿼리 보기’가 활성화되어 있다면 폴딩이 정상 작동 중인 것입니다.
3-2. 로드 옵션의 전략적 선택법
정제가 끝난 후 닫기 및 로드를 누를 때, 다음 세 가지 전략을 상황에 맞게 구사해야 합니다.
- 표(Table): 결과값이 작고 시트에서 직접 눈으로 확인해야 할 때 사용합니다.
- 데이터 모델에 이 데이터 추가: 피벗 테이블 분석이 목적이고 데이터가 대용량일 때 필수입니다. 시트의 행 제한을 우회합니다.
- 연결만 생성: 당장 결과가 필요 없거나, 다른 쿼리(중간 단계)에서 참조하기 위한 용도입니다. 불필요한 메모리 낭비를 막는 최고의 방법입니다.
4. 실무 성능을 2배 높이는 단계별 정제 전략
쿼리가 느려지는 대부분의 원인은 ‘순서’에 있습니다. 전문가들은 다음 순서를 엄격히 지킵니다.
STEP 1: 열 제거(Remove Columns)
데이터를 가져오자마자 가장 먼저 해야 할 일입니다. 수백 개의 열 중 분석에 필요한 5개만 남기고 나머지는 제거하세요. 파워 쿼리가 메모리에 담아야 할 부하가 90% 이상 줄어듭니다.
STEP 2: 행 필터링(Filter Rows)
필요 없는 기간, 필요 없는 카테고리는 즉시 제거합니다. 처리해야 할 ‘레코드’의 개수를 최소화하는 단계입니다.
STEP 3: 그룹화 및 요약(Group By)
상세 내역이 필요 없다면 쿼리 수준에서 미리 합계나 평균으로 요약하세요. 데이터의 부피가 획기적으로 줄어듭니다.
STEP 4: 데이터 형식 지정(Change Type)
많은 사용자가 초기 단계에서 형식을 지정하지만, 이는 리소스 소모가 큽니다. 모든 정제와 계산이 끝난 후, 로드 직전 단계에서 한꺼번에 형식을 지정하는 것이 성능상 유리합니다.
5. 도입 전 반드시 확인해야 할 환경 조건
- Windows 환경: 엑셀 2016 이상 버전은 [데이터] 탭 내에 ‘가져오기 및 변환’ 그룹으로 내장되어 있습니다. 2010/2013 버전 사용자는 MS 공식 홈페이지에서 ‘Power Query Add-in’을 별도로 설치해야 합니다.
- Mac 환경: 과거에는 기능이 매우 제한적이었으나, 최근 Microsoft의 업데이트로 많은 기능이 탑재되었습니다. 단, SQL 직접 연결이나 고급 M 코드 편집 등은 Windows 버전에 비해 여전히 제약이 있을 수 있으니 업무 환경을 확인하세요.
- 성능의 마지노선: 대용량 데이터(500만 행 이상)를 빈번하게 다룬다면 최소 16GB 이상의 RAM을 권장합니다.
결론: 엑셀 실력의 임계점을 넘어서는 법
엑셀 파워 쿼리는 단순히 업무 시간을 줄여주는 도구가 아닙니다. 이는 데이터를 체계적으로 다루는 **’프로세스의 설계’**를 배우는 과정입니다. 처음 마주하는 M 언어나 쿼리 폴딩이라는 개념이 벽처럼 느껴질 수 있습니다. 하지만 이 벽을 넘어서는 순간, 여러분은 더 이상 단순 반복 작업에 매몰된 직원이 아니라 데이터의 흐름을 제어하는 분석가로 거듭날 것입니다.
오늘 업무에서 가장 짜증 났던 반복 작업 하나를 골라보세요. 그리고 파워 쿼리의 [데이터 가져오기] 버튼을 누르는 것으로 그 지옥 같은 반복에서 탈출하시길 바랍니다.