[Excel] 엑셀 반복 데이터 정리 파워 쿼리 완전 정복

파워 쿼리 완전 정복 — CSV·웹·DB 데이터를 자동으로 가져와서 변환하기

 

📋 목차 1. 파워 쿼리란? — 엑셀의 숨겨진 데이터 처리 엔진 2. 파워 쿼리 시작하기 — 편집기 열고 첫 쿼리 만들기 3. 데이터 가져오기 — CSV·웹·폴더·데이터베이스 4. 데이터 변환 — 정리·분리·합치기·피벗 5. 여러 파일 한 번에 합치기 — 폴더 쿼리 6. 새로 고침 자동화 — 한 번 설정, 매달 클릭 한 번 7. M 언어 기초 — 수식 직접 편집하기

 

파워 쿼리(Power Query)는 엑셀 2016부터 기본 내장된 데이터 처리 도구다. 데이터 가져오기, 정리, 변환을 GUI로 설정해두면 다음번에는 '새로 고침' 버튼 하나로 동일한 작업이 자동으로 반복된다.   VBA보다 쉽고, 수식보다 강력하다. 매달 CSV 파일을 복사해서 붙이고 정리하는 작업을 하고 있다면 — 오늘 이 글 하나로 그 작업이 완전히 사라진다.

 

1	파워 쿼리란? — 엑셀의 숨겨진 데이터 처리 엔진

 

파워 쿼리는 엑셀 안에 내장된 ETL(Extract-Transform-Load) 도구다. 외부에서 데이터를 가져오고(Extract), 원하는 형태로 변환하고(Transform), 엑셀 시트에 불러오는(Load) 전 과정을 자동화할 수 있다.

 

▸ 파워 쿼리 vs 기존 방식 비교

작업	기존 방식	파워 쿼리
CSV 가져오기	매번 수동 복사·붙이기	한 번 설정 → 새로 고침만
열 이름 정리	매번 수동 수정	설정 저장 → 자동 적용
여러 파일 합치기	하나씩 열어서 붙이기	폴더 지정 → 자동 합산
데이터 정제	수식·수동 작업	클릭으로 변환 규칙 저장
웹 데이터 수집	직접 복사	URL 지정 → 자동 수집

 

▸ 파워 쿼리 열기

데이터 탭 → 데이터 가져오기 및 변환 그룹 또는: 데이터 탭 → 쿼리 및 연결 → 쿼리 편집

Excel 2016 이상이면 별도 설치 없이 바로 사용 가능하다.

 

💡 핵심 개념 파워 쿼리는 '단계(Step)'를 저장한다. 열 이름 변경, 필터 적용, 형식 변환 등 모든 작업이 단계로 기록되어, 새 데이터가 들어와도 동일한 단계가 자동 적용된다.

 

 

2	파워 쿼리 편집기 — 기본 구조 이해

 

파워 쿼리 편집기는 엑셀과 별도 창으로 열린다. 처음에는 낯설어 보이지만 구조를 이해하면 어렵지 않다.

 

영역	위치	역할
쿼리 목록	왼쪽 패널	만든 쿼리 목록, 클릭해서 전환
데이터 미리보기	중앙	현재 쿼리 결과 확인
적용된 단계	오른쪽 패널	작업 이력, 클릭해서 되돌리기 가능
수식 입력줄	상단	M 언어 수식 확인 및 편집
리본 메뉴	최상단	변환 작업 버튼 모음

 

 

💡 실무 팁 '적용된 단계'에서 단계를 클릭하면 그 시점의 데이터로 되돌아간다. 실수를 해도 해당 단계만 삭제하면 되므로 Ctrl+Z보다 훨씬 안전하다.

 

 

3	데이터 가져오기 — CSV·웹·폴더·DB

 

파워 쿼리로 가져올 수 있는 데이터 원본은 매우 다양하다. 가장 많이 쓰는 4가지를 설명한다.

 

▸ ① CSV / Excel 파일 가져오기

데이터 탭 → 데이터 가져오기 → 파일에서 → CSV(또는 Excel)

파일 선택 → 미리보기 확인 → 데이터 변환(편집기로) 또는 로드

인코딩 주의: 한글 CSV는 UTF-8 또는 EUC-KR 선택 구분자 확인: 쉼표(,) / 탭 / 세미콜론(;) 중 선택

 

▸ ② 웹 데이터 가져오기

데이터 탭 → 데이터 가져오기 → 기타 원본에서 → 웹

URL 입력 → 엑셀이 페이지에서 표 형태의 데이터를 자동 감지

활용 예: 환율 정보, 공공 데이터 포털, 주식 시세 표 주의: 웹 구조가 바뀌면 쿼리가 깨질 수 있음 → 정기 확인 필요

 

▸ ③ 폴더에서 여러 파일 한 번에

데이터 탭 → 데이터 가져오기 → 파일에서 → 폴더

폴더 경로 지정 → 폴더 안의 모든 파일 목록이 표시됨 → 결합 → 파일 결합 및 변환

→ 폴더에 새 파일을 추가하고 새로 고침하면 자동으로 합쳐진다

 

▸ ④ 데이터베이스 연결

데이터 탭 → 데이터 가져오기 → 데이터베이스에서 → SQL Server / Access 등

서버 주소·데이터베이스명 입력 → 테이블 또는 쿼리 선택 → 로드

SQL Server: 서버명\인스턴스, 포트 번호 Access: .accdb 파일 경로 선택 MySQL: 별도 ODBC 드라이버 설치 필요

 

💡 실무 팁 CSV 파일 경로가 바뀌면 쿼리가 깨진다. 가능하면 공유 폴더나 OneDrive에 원본 파일을 저장하고 경로를 고정해두자. 파워 쿼리 편집기 → 홈 탭 → 데이터 원본 설정에서 경로를 바꿀 수 있다.

 

 

4	데이터 변환 — 파워 쿼리의 핵심

 

데이터를 가져왔으면 이제 원하는 형태로 변환할 차례다. 파워 쿼리 편집기에서 클릭 몇 번으로 대부분의 정제 작업을 처리할 수 있다.

 

▸ 자주 쓰는 변환 작업

작업	위치	설명
열 형식 변경	열 머리글 클릭 → 형식 아이콘	텍스트·숫자·날짜·불리언
열 이름 변경	열 머리글 더블클릭	직접 입력
행 필터링	열 머리글 드롭다운	조건 선택
열 분리	변환 탭 → 열 분리	구분자·문자 수 기준
열 합치기	열 선택 → 변환 → 열 병합	구분자 지정
공백 제거	변환 탭 → 서식 → 자르기	앞뒤 공백 일괄 제거
중복 제거	홈 탭 → 행 제거 → 중복 제거	기준 열 선택
피벗/역피벗	변환 탭 → 열 피벗/열 피벗 해제	행↔열 전환
그룹화	홈 탭 → 그룹화	합계·개수·평균 집계

 

▸ 조건 열 추가 — IF 함수 대신

추가 탭 → 조건 열을 클릭하면 GUI로 조건 분기를 설정할 수 있다. 수식 없이 IF와 같은 로직을 구현한다.

예: 매출 열이 100 이상이면 '달성', 미만이면 '미달' → 추가 탭 → 조건 열 → 조건·결과 입력 → 확인

 

💡 실무 팁 변환 작업의 순서가 중요하다. 예를 들어 '공백 제거'를 '형식 변경' 전에 해야 할 때가 있다. 적용된 단계를 드래그해서 순서를 바꿀 수 있다.

 

 

5	여러 파일 자동 합치기 — 폴더 쿼리

 

월별 보고서, 지점별 데이터, 담당자별 파일처럼 같은 형식의 파일이 여러 개 있을 때 파워 쿼리 폴더 쿼리를 쓰면 자동으로 하나로 합쳐준다.

 

▸ 폴더 쿼리 설정

① 데이터 탭 → 데이터 가져오기 → 파일에서 → 폴더

② 폴더 경로 선택 → 파일 목록 확인

③ '결합' 버튼 클릭 → '파일 결합 및 변환'

④ 샘플 파일에서 가져올 시트/테이블 선택

⑤ 필요한 변환 작업 후 '닫기 및 로드'

 

▸ 이후 업데이트 방법

폴더에 새 파일 추가 → 엑셀에서 데이터 탭 → 모두 새로 고침 → 새 파일이 자동으로 합쳐짐 (수동 작업 0)

 

활용 예시	폴더 구조	결과
월별 매출 합산	/매출/2025-01.csv, 2025-02.csv...	전체 월 매출 자동 집계
지점별 보고서	/보고서/서울.xlsx, 부산.xlsx...	전체 지점 통합 현황
담당자별 데이터	/데이터/김민준.csv, 이서연.csv...	전 담당자 통합 분석

 

💡 실무 팁 파일 형식과 열 구조가 동일해야 합쳐진다. 파일마다 열 이름이 다르면 오류가 생긴다. 표준 양식을 미리 정해두고 모든 파일에 동일하게 적용하자.

 

 

6	새로 고침 자동화 — 한 번 설정, 매달 클릭 한 번

 

파워 쿼리의 가장 큰 장점은 설정이 저장된다는 것이다. 처음 한 번만 설정해두면 이후에는 '새로 고침' 클릭 한 번으로 동일한 작업이 반복된다.

 

▸ 수동 새로 고침

데이터 탭 → 모두 새로 고침 (Ctrl+Alt+F5) 또는 특정 쿼리만: 해당 테이블 우클릭 → 새로 고침

 

▸ 자동 새로 고침 설정

쿼리 및 연결 창 → 쿼리 우클릭 → 속성 → 사용 제어 탭

파일 열 때마다 새로 고침: '파일 열기 시 데이터 새로 고침' 체크 정기 새로 고침: '정기적으로 새로 고침' → N분 간격 설정

 

▸ 쿼리 연결 끊기 & 복사

쿼리 복사:     쿼리 우클릭 → 복제 (동일 설정 재사용) 연결만 유지:   로드 설정 → '연결만 만들기' (시트에 표 안 만듦) 다른 파일에:   쿼리 우클릭 → 내보내기 → ODC 파일로 저장

 

💡 실무 팁 월말 보고서처럼 정기적으로 같은 작업을 반복한다면, 파워 쿼리로 한 번 설정해두고 매달 '새로 고침'만 하면 된다. 처음 설정에 30분이 걸려도 매달 2시간을 절약한다면 충분히 가치 있다.

 

 

7	M 언어 기초 — 수식 직접 편집하기

 

파워 쿼리의 모든 작업은 내부적으로 M 언어로 기록된다. GUI로 90%는 해결되지만, 나머지 10%는 M 언어를 직접 편집해야 한다. 기본 문법만 알면 충분하다.

 

▸ M 언어 기본 구조

let   원본 = Csv.Document(File.Contents("경로"),[Delimiter=","]),   헤더승격 = Table.PromoteHeaders(원본),   형식변경 = Table.TransformColumnTypes(헤더승격,{{"날짜", type date}}) in   형식변경

let~in 구조로 이루어져 있다. let 블록에서 단계를 정의하고, in 다음에 최종 결과를 지정한다.

 

▸ 자주 쓰는 M 언어 함수

함수	설명	예시
Table.FilterRows	행 필터링	특정 조건 행만 유지
Table.AddColumn	열 추가	계산 열, 조건 열
Table.TransformColumns	열 값 변환	대소문자, 형식 변환
Text.Trim	공백 제거	앞뒤 공백 삭제
Date.Year / Month	날짜에서 연·월 추출	연별·월별 집계
List.Contains	목록 포함 여부	특정 값이 목록에 있는지

 

▸ 파라미터 — 동적 경로·값 설정

파라미터를 만들면 쿼리에서 경로나 날짜 같은 값을 외부에서 바꿀 수 있다.

홈 탭 → 매개 변수 관리 → 새 매개 변수 이름: 폴더경로 / 형식: 텍스트 / 현재 값: C:\데이터\ → 쿼리에서 폴더경로 변수로 참조

 

💡 실무 팁 M 언어를 처음 배울 때는 GUI로 작업한 뒤 수식 입력줄에서 자동 생성된 M 코드를 읽어보는 것이 가장 빠른 방법이다. 고급 편집기(홈 탭 → 고급 편집기)를 열면 전체 M 코드를 한 번에 볼 수 있다.

 

 

오늘 배운 내용 정리

기능	핵심 내용	실무 활용
데이터 가져오기	CSV·웹·폴더·DB 연결	원본 경로 고정, 인코딩 확인
데이터 변환	클릭으로 정제·분리·합치기	단계 순서가 중요
폴더 쿼리	여러 파일 자동 합산	파일 형식 통일 필수
새로 고침	한 번 설정 → 매번 자동	파일 열 때 자동 새로 고침
M 언어	let~in 구조, 내장 함수	GUI로 먼저 작업 → 코드 학습
파라미터	경로·값을 외부에서 변경	환경별 파일 경로 관리

 

파워 쿼리는 '한 번 배우면 평생 쓰는' 기술이다. 처음 설정이 가장 어렵고, 두 번째부터는 비슷한 패턴이 반복된다. 지금 당장 반복하는 CSV 정리 작업 하나를 파워 쿼리로 만들어보자.