엑셀 파워 쿼리 입문, 반복 데이터 정제 자동화하기
매달 공장에서 원재료가 들어올 때마다 작업자가 직접 껍질을 벗기고 불량 부품을 골라낸 뒤 길이를 잘라 작업대에 올린다면 인건비와 시간이 끝없이 듭니다. 반대로 한 번만 세척·절단 컨베이어 벨트를 조립해 두면 다음 달에는 새 원재료만 투입구에 올려놓고 시작 버튼만 눌러도 똑같은 규격품이 쏟아집니다.
ERP나 쇼핑몰 관리자 페이지에서 매주 CSV·엑셀 원본을 내려받아 “위 3줄 삭제 → 불필요한 열 5개 지우기 → 날짜 텍스트를 날짜 형식으로 바꾸기 → 빈 행 필터링”을 반복하고 계신가요? 엑셀 안에 기본 내장된 엑셀 파워 쿼리(Power Query)를 쓰면 코딩 한 줄 없이 이 모든 정제 과정을 클릭만으로 녹화해 두고, 다음 달부터는 새로 고침 한 번으로 끝낼 수 있습니다.
매주·매달 원본 파일을 받아 똑같은 편집을 반복하는 비효율
외부 시스템에서 내려받은 원본 데이터는 당장 보고서나 피벗 테이블에 쓰기 어려운 형태가 많습니다. 위쪽에 제목 3줄이 들어가 있거나, 안 쓰는 코드 열이 수십 개 섞여 있고, 금액에 원 단위가 붙어 문자로 인식되거나 UTF-8 인코딩 문제로 그대로 열면 글자가 깨집니다.
이를 매번 원본 파일에 직접 들어가 손으로 지우고 바꾸다 보면, 하루는 실수로 필요한 열을 지워 버리거나 행 순서가 어긋나도 어디서 잘못됐는지 되돌리기가 어렵습니다.
엑셀 파워 쿼리의 ‘적용된 단계’ 자동 녹화 원리
엑셀 파워 쿼리는 원본 파일을 직접 수정하지 않고, 원본을 읽어 들여 가공한 뒤 깨끗한 결과물만 엑셀 시트로 내보내는 데이터 추출·변환·적재(ETL, Extract-Transform-Load) 도구입니다.
적용된 단계(Applied Steps)의 작동 구조
파워 쿼리 편집기 창 오른쪽에는 사용자가 클릭한 모든 작업(상위 행 제거, 열 제거, 텍스트 자르기, 데이터 형식 변경 등)이 적용된 단계 목록에 시간순 레시피로 자동 기록됩니다. 다음 주에 새 원본 파일로 바꿔 치고 엑셀에서 모두 새로 고침을 누르면, 파워 쿼리가 이 단계 목록을 1번부터 끝까지 자동으로 다시 실행해 최신 결과 표를 만들어 줍니다.
컨베이어 벨트 비유와 다른 점은, 파워 쿼리 작업은 원본 파일의 데이터를 훼손하지 않으므로 단계 목록 중 중간 단계 하나를 클릭해 언제든 과거 상태를 미리 보거나 톱니바퀴 아이콘을 눌러 조건을 수정할 수 있다는 점입니다.
수작업 편집·VBA 매크로 vs 엑셀 파워 쿼리 비교
| 구분 | 매번 수작업 편집 | VBA 매크로 | 엑셀 파워 쿼리 |
|---|---|---|---|
| 구축 난이도 | 쉬움 (단, 매번 반복 피로 누적) | 어려움 (VBA 코드 작성·수정 필요) | 쉬움 (마우스 메뉴 클릭으로 단계 기록) |
| 파일 형식 및 보안 | .xlsx |
.xlsm (외부 다운로드 시 차단 경고) |
.xlsx 유지 (매크로 차단 없음) |
| 원본 행 수 변화 대응 | 수동으로 수식·범위 다시 잡기 | 끝 행 계산 코드 작성 필요 | 행이 100줄이든 10만 줄이든 자동 처리 |
| 추천 업무 | 1회성 간단한 자료 정리 | UI 제어·파일 자동 발송 등 복합 동작 | 정기적인 CSV/엑셀 정제·병합·형식 통일 |
열 삭제·공백 제거·형식 변환을 파워 쿼리로 자동화하는 순서
Excel 2016 이상(및 Microsoft 365)에는 별도 설치 없이 데이터 탭에 엑셀 파워 쿼리가 기본 탑재되어 있습니다. 아래 순서대로 첫 자동화 레시피를 만들어 보시기 바랍니다.
엑셀 파워 쿼리 데이터 정제 자동화 순서
- 원본 파일 연결하기: 빈 엑셀 파일을 열고 상단
데이터 → 데이터 가져오기 → 파일에서 → Excel 통합 문서에서(또는텍스트/CSV에서)를 눌러 매달 내려받는 원본 파일을 선택합니다. 미리보기 창이 뜨면 바로로드하지 말고 반드시 아래쪽데이터 변환을 누릅니다. - 불필요한 상단 행과 빈 행 제거: 파워 쿼리 편집기가 열리면
홈 → 행 제거 → 상위 행 제거로 맨 위 제목 껍데기 줄을 없앱니다. 첫 줄이 열 제목이라면홈 → 첫 행을 머리글로 사용을 클릭합니다. - 필요한 열만 남기기(열 삭제 팁): 쓰지 않는 열을 하나씩 지우기보다, 꼭 남길 열들만
Ctrl키로 선택한 뒤 우클릭 →다른 열 제거를 누르는 편이 안전합니다. 나중에 원본에 새 불필요 열이 추가되어도 내가 고른 열만 깔끔하게 살아남습니다. - 형식 변환 및 공백 정리: 각 열 제목 왼쪽 아이콘(
ABC,123, 달력 모양)을 눌러 날짜, 정수, 텍스트 형식을 정확히 지정하고, 문자열 열은 우클릭 →변환 → 자르기로 앞뒤 숨은 공백을 날립니다. - 엑셀로 내보내기 및 다음 달 재사용: 정제가 끝났으면 왼쪽 위
홈 → 닫기 및 로드를 눌러 엑셀 시트에 녹색 표로 내보냅니다. 다음 달에는 같은 폴더의 원본 파일만 새 파일로 덮어쓰거나(또는데이터 원본 설정에서 경로만 바꿔) 시트에서 우클릭 →새로 고침만 누르면 즉시 갱신됩니다.
만약 원본 파일의 열 이름 자체가 매달 조금씩 달라진다면 제거된 열 단계에서 없는 열 이름을 찾다 오류가 날 수 있으므로, 3단계처럼 “제거할 열”이 아니라 “남길 열(다른 열 제거)”을 지정해 두는 것이 유지보수에 훨씬 유리합니다.
핵심 정리
엑셀 파워 쿼리는 반복적인 데이터 청소 작업을 마우스 클릭만으로 자동화해 주는 강력한 정제 엔진입니다. 데이터 가져오기 → 데이터 변환으로 들어가 행 제거, 다른 열 제거, 형식 지정 단계를 한 번만 저장해 두면 .xlsm 매크로 보안 경고 걱정 없이 매달 새로 고침 한 번으로 완벽하게 정제된 표를 받아 볼 수 있습니다.
재발 방지 체크리스트
재발 방지 체크리스트
- 불러오기 미리보기 창에서 바로
로드하지 않고데이터 변환을 눌러 정제 단계를 거쳤는지 확인하기 - 원본 구조 변경에 대비해
열 제거대신 필요한 열만 고르는다른 열 제거방식 활용하기 - 각 열의 데이터 형식(날짜·정수·텍스트)이 자동 추측으로 잘못 잡히지 않았는지 마지막 단계에서 꼭 점검하기
- 원본 CSV 파일이 한글 깨짐 없이 열리도록 초기 가져오기 창에서
파일 원본인코딩(UTF-8등) 확인하기









