엑셀 파워 쿼리 입문, 반복 데이터 정제 자동화하기

엑셀 파워 쿼리 입문, 반복 데이터 정제 자동화하기

매달 공장에서 원재료가 들어올 때마다 작업자가 직접 껍질을 벗기고 불량 부품을 골라낸 뒤 길이를 잘라 작업대에 올린다면 인건비와 시간이 끝없이 듭니다. 반대로 한 번만 세척·절단 컨베이어 벨트를 조립해 두면 다음 달에는 새 원재료만 투입구에 올려놓고 시작 버튼만 눌러도 똑같은 규격품이 쏟아집니다.

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)에는 별도 설치 없이 데이터 탭에 엑셀 파워 쿼리가 기본 탑재되어 있습니다. 아래 순서대로 첫 자동화 레시피를 만들어 보시기 바랍니다.

엑셀 파워 쿼리 데이터 정제 자동화 순서

  1. 원본 파일 연결하기: 빈 엑셀 파일을 열고 상단 데이터 → 데이터 가져오기 → 파일에서 → Excel 통합 문서에서(또는 텍스트/CSV에서)를 눌러 매달 내려받는 원본 파일을 선택합니다. 미리보기 창이 뜨면 바로 로드하지 말고 반드시 아래쪽 데이터 변환을 누릅니다.
  2. 불필요한 상단 행과 빈 행 제거: 파워 쿼리 편집기가 열리면 홈 → 행 제거 → 상위 행 제거로 맨 위 제목 껍데기 줄을 없앱니다. 첫 줄이 열 제목이라면 홈 → 첫 행을 머리글로 사용을 클릭합니다.
  3. 필요한 열만 남기기(열 삭제 팁): 쓰지 않는 열을 하나씩 지우기보다, 꼭 남길 열들만 Ctrl 키로 선택한 뒤 우클릭 → 다른 열 제거를 누르는 편이 안전합니다. 나중에 원본에 새 불필요 열이 추가되어도 내가 고른 열만 깔끔하게 살아남습니다.
  4. 형식 변환 및 공백 정리: 각 열 제목 왼쪽 아이콘(ABC, 123, 달력 모양)을 눌러 날짜, 정수, 텍스트 형식을 정확히 지정하고, 문자열 열은 우클릭 → 변환 → 자르기로 앞뒤 숨은 공백을 날립니다.
  5. 엑셀로 내보내기 및 다음 달 재사용: 정제가 끝났으면 왼쪽 위 홈 → 닫기 및 로드를 눌러 엑셀 시트에 녹색 표로 내보냅니다. 다음 달에는 같은 폴더의 원본 파일만 새 파일로 덮어쓰거나(또는 데이터 원본 설정에서 경로만 바꿔) 시트에서 우클릭 → 새로 고침만 누르면 즉시 갱신됩니다.

만약 원본 파일의 열 이름 자체가 매달 조금씩 달라진다면 제거된 열 단계에서 없는 열 이름을 찾다 오류가 날 수 있으므로, 3단계처럼 “제거할 열”이 아니라 “남길 열(다른 열 제거)”을 지정해 두는 것이 유지보수에 훨씬 유리합니다.

핵심 정리

엑셀 파워 쿼리는 반복적인 데이터 청소 작업을 마우스 클릭만으로 자동화해 주는 강력한 정제 엔진입니다. 데이터 가져오기 → 데이터 변환으로 들어가 행 제거, 다른 열 제거, 형식 지정 단계를 한 번만 저장해 두면 .xlsm 매크로 보안 경고 걱정 없이 매달 새로 고침 한 번으로 완벽하게 정제된 표를 받아 볼 수 있습니다.

재발 방지 체크리스트

재발 방지 체크리스트

  • 불러오기 미리보기 창에서 바로 로드하지 않고 데이터 변환을 눌러 정제 단계를 거쳤는지 확인하기
  • 원본 구조 변경에 대비해 열 제거 대신 필요한 열만 고르는 다른 열 제거 방식 활용하기
  • 각 열의 데이터 형식(날짜·정수·텍스트)이 자동 추측으로 잘못 잡히지 않았는지 마지막 단계에서 꼭 점검하기
  • 원본 CSV 파일이 한글 깨짐 없이 열리도록 초기 가져오기 창에서 파일 원본 인코딩(UTF-8 등) 확인하기