매달 말이 되면 반복되는 풍경이 있습니다. 여러 팀에서 받은 엑셀 파일들을 하나하나 열고, 복사하고, 붙여넣고, 형식을 맞추고… 몇 시간씩 걸리는 그 작업입니다. 이걸 파워쿼리(Power Query) 하나로 완전히 자동화할 수 있다면 어떨까요?
이 글에서는 파워쿼리가 무엇인지부터, 실무에서 바로 쓸 수 있는 핵심 기능까지 단계별로 설명합니다. 엑셀 매크로를 몰라도, VBA를 한 줄도 몰라도 괜찮습니다. 마우스 클릭만으로 데이터 자동화를 구현할 수 있는 것이 파워쿼리의 가장 큰 장점입니다.
파워쿼리(Power Query)란 무엇인가?
파워쿼리는 엑셀에 기본 내장된 데이터 변환·자동화 도구입니다. 2016년 이후 엑셀 버전이라면 별도 설치 없이 바로 쓸 수 있습니다. 상단 메뉴에서 [데이터] → [데이터 가져오기 및 변환] 섹션에 있는 기능이 바로 파워쿼리입니다.
파워쿼리의 핵심은 “한 번 설정해 두면 다음에는 새로고침 버튼 하나로 끝난다”는 점입니다. 데이터를 가져오고 → 정리하고 → 원하는 형태로 만드는 모든 과정을 쿼리로 저장해 두고, 다음 달에는 새 데이터를 넣은 뒤 새로고침만 누르면 됩니다.
파워쿼리가 필요한 순간 — 이런 상황이라면 필수입니다
파워쿼리가 특히 빛을 발하는 실무 상황들이 있습니다. 아래 중 하나라도 해당된다면 파워쿼리를 배울 이유가 충분합니다.
- 여러 시트나 파일을 하나로 합쳐야 할 때 — 팀별로 작성한 보고서 파일 10개를 매달 합치는 작업
- 데이터 형식이 제각각일 때 — 날짜가 어떤 파일은 “2024.01.05”, 다른 파일은 “2024-01-05″로 되어 있는 경우
- 빈 행·중복 데이터가 많을 때 — 원본 데이터에 불필요한 행이 섞여 있어 정리가 필요한 경우
- 피벗 형태를 세로 형태로 바꿔야 할 때 — 월별 열로 되어 있는 데이터를 행 형태로 변환해야 하는 경우
- 외부 시스템 데이터를 정기적으로 가져와야 할 때 — ERP, 그룹웨어, 웹 데이터를 주기적으로 업데이트하는 경우
파워쿼리 시작하기 — 처음 열어보는 분을 위한 기본 흐름
파워쿼리를 처음 사용한다면 아래 순서로 따라 해보세요.
1단계: 데이터 불러오기
엑셀 상단 메뉴에서 [데이터] → [텍스트/CSV에서] 또는 [Excel 통합 문서에서]를 선택합니다. 파일을 선택하면 파워쿼리 편집기가 열리면서 데이터를 미리 볼 수 있습니다.
이 단계에서 중요한 점은, 파워쿼리가 원본 파일을 변경하지 않는다는 것입니다. 원본은 그대로 두고, 파워쿼리 내부에서만 데이터를 가공합니다. 원본이 훼손될 걱정 없이 마음껏 편집할 수 있습니다.
2단계: 파워쿼리 편집기에서 데이터 정리
데이터를 불러오면 파워쿼리 편집기 창이 열립니다. 이곳에서 실제 데이터 정리 작업을 합니다. 주요 기능들을 살펴보겠습니다.
불필요한 열 제거: 필요 없는 열을 선택하고 마우스 오른쪽 클릭 → [제거]를 누르면 됩니다. 이후 새로고침을 해도 해당 열은 자동으로 제거됩니다.
데이터 형식 변환: 열 머리글 왼쪽의 아이콘을 클릭하면 해당 열의 데이터 형식(텍스트, 숫자, 날짜 등)을 바꿀 수 있습니다. 예를 들어 날짜처럼 보이지만 텍스트로 인식된 열을 날짜 형식으로 한 번에 변환할 수 있습니다.
빈 행 제거: 상단 메뉴 [홈] → [행 제거] → [빈 행 제거]를 선택하면 빈 행이 모두 삭제됩니다. 클릭 한 번이면 충분합니다.
필터 적용: 각 열 머리글의 드롭다운 버튼을 클릭해서 특정 값만 남기거나 제외할 수 있습니다. 엑셀의 자동 필터와 비슷하지만, 이 설정이 쿼리로 저장된다는 점이 다릅니다.
3단계: 엑셀 시트로 내보내기
정리가 끝나면 편집기 상단 [홈] → [닫기 및 로드]를 클릭합니다. 그러면 정리된 데이터가 엑셀 시트에 표 형태로 불러와집니다. 다음에 원본 데이터가 바뀌면 시트에서 마우스 오른쪽 클릭 → [새로 고침]만 누르면 자동으로 최신 데이터로 업데이트됩니다.
실무에서 가장 많이 쓰는 파워쿼리 기능 5가지
① 여러 파일 한 번에 합치기 (폴더에서 가져오기)
실무에서 가장 많이 쓰는 기능입니다. 같은 형식의 엑셀 파일 여러 개가 한 폴더에 있을 때, 이를 한꺼번에 하나의 표로 합칠 수 있습니다.
사용 방법: [데이터] → [파일에서] → [폴더에서]를 선택합니다. 폴더 경로를 지정하면 해당 폴더 안의 모든 파일 목록이 뜹니다. [데이터 결합 및 변환]을 클릭하면 각 파일의 내용이 자동으로 하나로 합쳐집니다.
예를 들어 1월~12월 매출 파일이 각각 따로 있다면, 이 기능으로 12개 파일을 단 몇 번의 클릭으로 하나의 데이터로 통합할 수 있습니다. 다음 달에 새 파일이 폴더에 추가되면, 새로고침 한 번으로 자동 반영됩니다.
② 피벗 해제 (열→행 변환)
월별 데이터가 열로 펼쳐진 형태(피벗 형태)를 분석하기 좋은 세로 형태로 바꾸는 작업입니다. VLOOKUP이나 피벗테이블로 분석하려면 데이터가 세로 형태여야 하는 경우가 많습니다.
사용 방법: 월별 열들을 선택한 뒤 마우스 오른쪽 클릭 → [열 피벗 해제]를 선택합니다. 그러면 “특성(월)”과 “값(금액)” 두 열로 자동 변환됩니다. 수동으로 하면 몇 시간이 걸리는 작업을 클릭 한 번으로 해결합니다.
③ 열 분할 및 합치기
“홍길동 (마케팅팀)”처럼 한 열에 이름과 팀이 같이 들어 있는 경우, 이를 분리하거나 반대로 흩어진 정보를 하나로 합쳐야 할 때 사용합니다.
열 분할: 해당 열을 클릭하고 [홈] → [열 분할] → [구분 기호 기준]을 선택합니다. 공백, 쉼표, 괄호 등 원하는 구분 기호를 지정하면 자동으로 여러 열로 분리됩니다.
열 합치기: 합칠 열들을 선택하고 마우스 오른쪽 클릭 → [열 병합]을 선택한 뒤 구분 기호를 지정합니다. 성과 이름이 분리된 경우 하나로 합치는 데 유용합니다.
④ 두 표 병합하기 (VLOOKUP 대체)
파워쿼리에서는 두 쿼리를 키 값 기준으로 병합할 수 있습니다. 엑셀의 VLOOKUP과 같은 역할이지만 훨씬 안정적이고, 데이터 변경 시 자동으로 반영됩니다.
사용 방법: 편집기 상단 [홈] → [쿼리 병합]을 선택합니다. 기준이 되는 열과 가져올 테이블을 지정하면 VLOOKUP처럼 값을 불러옵니다. 단, VLOOKUP과 달리 오류가 발생해도 수식이 깨지지 않습니다.
⑤ 조건부 열 추가
특정 조건에 따라 새 열을 만들 수 있습니다. 예를 들어 매출이 100만 원 이상이면 “달성”, 미만이면 “미달성”으로 표시하는 열을 추가하는 식입니다.
사용 방법: [열 추가] → [조건부 열]을 클릭합니다. IF 함수처럼 조건과 결과값을 설정할 수 있습니다. 코드를 전혀 몰라도 드롭다운 선택만으로 조건을 설정할 수 있어 누구나 쉽게 사용할 수 있습니다.
파워쿼리 자동화의 진짜 효과 — 실제 시간 절약 사례
파워쿼리를 도입했을 때 실무에서 얼마나 시간이 절약되는지 구체적인 예를 들어보겠습니다.
사례 1: 월간 영업 보고서 취합
기존 방식: 10개 지역 파일 열기 → 각각 복사 → 하나의 파일에 붙여넣기 → 형식 통일 → 약 3시간 소요
파워쿼리 적용 후: 폴더에서 가져오기 설정 1회 → 이후 매달 새로고침 버튼 클릭 → 약 2분 소요
사례 2: 인사 데이터 정기 업데이트
기존 방식: ERP에서 내보낸 CSV 파일을 열고, 불필요한 열 삭제, 부서명 일치시키기, 날짜 형식 변환 → 약 1시간 소요
파워쿼리 적용 후: CSV 파일만 교체 후 새로고침 → 약 10초 소요
처음 파워쿼리를 설정하는 데 30분~1시간이 걸린다고 해도, 이후 매월 반복될 때마다 그 시간을 그대로 돌려받을 수 있습니다. 1년이면 수십 시간을 절약하는 셈입니다.
파워쿼리 사용 시 주의할 점
파워쿼리를 처음 쓸 때 자주 헷갈리는 부분들이 있습니다. 미리 알아두면 시행착오를 줄일 수 있습니다.
원본 파일 경로가 바뀌면 오류가 납니다. 파워쿼리는 처음 지정한 파일 경로를 기억합니다. 파일을 다른 폴더로 옮기거나 이름을 바꾸면 새로고침 시 오류가 발생합니다. 경로를 바꾸려면 편집기에서 [소스] 단계의 경로를 직접 수정해야 합니다.
열 이름이 바뀌면 쿼리가 깨집니다. 파워쿼리는 열 이름을 기준으로 작동합니다. 원본 파일의 열 이름이 변경되면 해당 열을 참조하는 단계에서 오류가 발생할 수 있습니다. 팀원들과 공유하는 파일이라면 열 이름 규칙을 미리 맞춰두는 것이 좋습니다.
파워쿼리로 불러온 표는 직접 편집하지 않는 것이 좋습니다. 파워쿼리로 생성된 결과 표는 새로고침 시 덮어써집니다. 추가적인 계산이나 코멘트는 별도 열이나 시트에서 작업하는 것을 권장합니다.
파워쿼리와 매크로, 무엇이 다를까?
엑셀 자동화 하면 보통 매크로(VBA)를 떠올립니다. 파워쿼리와 매크로는 어떻게 다를까요?
매크로는 “어떻게 처리할지”를 코드로 작성합니다. 반복 동작을 자동화하는 데 강점이 있지만, 코드를 알아야 하고 유지보수가 어렵습니다. 반면 파워쿼리는 “데이터를 어떻게 변환할지”를 클릭으로 설정합니다. 데이터 정리·변환·통합에 특화되어 있고, 코드 없이도 사용할 수 있습니다.
둘은 경쟁 관계가 아닌 보완 관계입니다. 데이터를 가져오고 정리하는 단계는 파워쿼리로, 정리된 데이터를 바탕으로 특정 동작(이메일 발송, 파일 저장 등)을 자동화하는 단계는 매크로로 처리하면 최적의 조합이 됩니다.
파워쿼리 학습 로드맵 — 어떤 순서로 배워야 할까?
파워쿼리를 처음 배운다면 아래 순서로 학습하는 것을 추천합니다.
1주차: 파워쿼리 편집기 기본 인터페이스 익히기, 단일 파일 불러오기 및 기본 정리 작업(열 제거, 형식 변환, 필터)
2주차: 여러 파일 합치기(폴더에서 가져오기), 피벗 해제, 조건부 열 추가
3주차: 쿼리 병합(VLOOKUP 대체), 그룹화(집계), 열 추가 수식
4주차: M 언어 기초(파워쿼리 내부 코드), 매개변수를 활용한 동적 쿼리 만들기
처음 1~2주만 따라와도 실무에서 체감할 수 있는 자동화가 가능합니다. 나머지는 필요한 순간이 생기면 그때그때 배워도 늦지 않습니다.
마무리 — 파워쿼리는 엑셀 자동화의 시작점입니다
파워쿼리는 엑셀을 쓰는 직장인이라면 반드시 알아야 할 기능입니다. 코딩 없이, 복잡한 함수 없이, 반복적인 데이터 정리 작업을 처음 한 번만 설정하면 이후에는 버튼 하나로 끝낼 수 있습니다.
처음엔 낯설어 보이지만, 실제로 써보면 “왜 진작 이걸 몰랐을까”라는 생각이 드는 것이 파워쿼리입니다. 다음 달 보고서 취합 전에 딱 30분만 투자해서 파워쿼리를 열어보세요. 그 30분이 앞으로의 수십 시간을 돌려줄 것입니다.
다음 글에서는 파워쿼리에서 자주 쓰는 M 언어 코드 10가지를 다룰 예정입니다. 기본을 익힌 뒤 한 단계 더 나아가고 싶은 분들께 도움이 될 것입니다.
