Power Query로 데이터 정제 자동화 — 클릭 한 번으로 갱신되는 리포트

• 들어가며
• 1. Power Query란 — ETL을 클릭으로
• 2. 데이터 가져오기
• 3. 핵심 변환 — 클릭만으로 정제
  • 형식 변경
  • 열 분할과 병합
  • 피벗과 언피벗
  • 그룹화
  • 조건 열
• 4. 여러 파일 폴더 결합
• 5. 쿼리 병합과 추가 — 조인
  • 추가(Append)
  • 병합(Merge)
• 6. 매개변수 — 유연한 쿼리
• 7. M 언어 맛보기
• 8. 새로 고침 자동화와 한계
  • 자동 새로 고침 옵션
  • 한계
• 9. 동적 배열, Python in Excel과의 관계
• 10. 실무 사례 — 월간 리포트 자동화
• 11. 흔한 함정 — 데이터형과 로캘
• 마치며
• 참고 자료

들어가며

매달 같은 작업을 반복하고 있다면 이 글이 도움이 됩니다. CSV를 열고, 필요 없는 열을 지우고, 형식을 고치고, 두 표를 붙이고, 피벗을 새로 만들고... 이 과정을 매번 손으로 한다면 시간 낭비일 뿐 아니라 실수의 온상입니다.

Power Query는 엑셀(과 Power BI)에 내장된 데이터 변환 도구입니다. 한 번 변환 과정을 "기록"해 두면, 다음 달에는 원본 파일만 바꾸고 새로 고침 버튼 한 번으로 같은 정제가 자동 적용됩니다. 클릭으로 만든 모든 단계가 재실행되기 때문입니다.

이 글에서는 Power Query의 개념(ETL)과 핵심 변환, 폴더 결합과 쿼리 병합, 매개변수, M 언어 맛보기, 그리고 새로 고침 자동화의 한계와 실무 함정까지 단계별로 정리합니다.

Power Query는 Windows 데스크톱 엑셀에서 가장 완전하게 동작하며, Mac과 웹 버전은 기능이 일부 제한됩니다. 메뉴 위치는 "데이터" 탭의 "데이터 가져오기 및 변환" 그룹입니다.

1. Power Query란 — ETL을 클릭으로

Power Query는 전형적인 ETL(Extract, Transform, Load) 도구입니다.

• Extract(추출): 파일, 폴더, 웹, 데이터베이스에서 데이터를 가져옵니다.
• Transform(변환): 열 정리, 형식 변경, 분할/병합, 피벗 등 가공을 합니다.
• Load(적재): 정제된 결과를 워크시트나 데이터 모델로 내보냅니다.

핵심은 모든 작업이 단계로 기록된다는 점입니다. 오른쪽 "적용된 단계" 창에 변환 하나하나가 순서대로 쌓이고, 이 단계들은 새로 고침 때마다 위에서 아래로 재실행됩니다.

원본 데이터 ──► [단계1: 열 제거] ──► [단계2: 형식 변경]
            ──► [단계3: 분할]   ──► [단계4: 필터]  ──► 결과 테이블

손으로 한 번 하면, 그 절차가 영구히 저장되는 셈입니다. 다음 달 데이터로 바꾸면 같은 절차가 그대로 돌아갑니다.

2. 데이터 가져오기

"데이터" 탭의 "데이터 가져오기"에서 다양한 소스를 선택할 수 있습니다.

가져오기를 선택하면 미리 보기 창이 뜨고, "데이터 변환"을 누르면 Power Query 편집기로 들어갑니다. 여기서부터 모든 가공이 시작됩니다.

3. 핵심 변환 — 클릭만으로 정제

Power Query 편집기에서 자주 쓰는 변환을 정리합니다. 모두 메뉴 클릭으로 가능하며, 뒤에서 M 코드가 자동 생성됩니다.

형식 변경

각 열의 데이터 형식(텍스트, 정수, 날짜 등)을 명시적으로 지정합니다. 형식이 잘못 추론되면 이후 계산이 어긋나므로, 가장 먼저 점검할 단계입니다.

열 분할과 병합

• 분할: 구분 기호(쉼표, 공백 등)나 글자 수 기준으로 한 열을 여러 열로 나눕니다.
• 병합: 여러 열을 구분자와 함께 하나로 합칩니다.

"이름" 한 열을 "성"과 "이름"으로 나누거나, "시/도"와 "구"를 합쳐 주소를 만드는 일이 클릭 몇 번으로 끝납니다.

피벗과 언피벗

• 피벗: 행 값을 열 머리글로 펼칩니다.
• 언피벗: 여러 열을 "속성-값" 두 열로 접습니다.

특히 언피벗은 실무에서 매우 자주 쓰입니다. "1월, 2월, 3월..."처럼 월이 가로로 나열된 표를, 분석하기 좋은 "월, 값" 세로 형태로 바꿔 줍니다.

[ 피벗 형태 ]            [ 언피벗 형태 ]
부서  1월  2월           부서  월   값
영업  100  120     ──►   영업  1월  100
개발   80   90           영업  2월  120
                        개발  1월   80
                        개발  2월   90

그룹화

기준 열로 묶어 합계, 평균, 개수 등을 집계합니다. 피벗테이블과 비슷하지만, 변환 파이프라인의 한 단계로 들어가 새로 고침에 포함된다는 점이 다릅니다.

조건 열

규칙에 따라 새 열의 값을 채웁니다. 예를 들어 매출이 일정 기준 이상이면 "우수", 그 미만이면 "보통"으로 분류하는 식입니다. 복잡한 IF 중첩 없이 대화상자로 규칙을 정의합니다.

4. 여러 파일 폴더 결합

Power Query의 진짜 위력은 폴더 결합에서 나옵니다. 매달 같은 형식의 파일이 한 폴더에 쌓인다면, 폴더 전체를 한 번에 합칠 수 있습니다.

1. "데이터 가져오기 → 폴더에서"로 폴더 경로 지정
2. 폴더 안 모든 파일 목록이 표시됨
3. "결합 및 변환"을 누르면 샘플 파일 기준으로 변환 규칙 생성
4. 같은 변환이 모든 파일에 자동 적용되어 하나의 표로 합쳐짐

다음 달 파일을 폴더에 넣고 새로 고침만 하면, 새 파일까지 자동으로 포함됩니다. "월별 파일을 손으로 복사·붙여넣기"하던 작업이 통째로 사라집니다.

5. 쿼리 병합과 추가 — 조인

추가(Append)

같은 구조의 표를 세로로 이어 붙입니다. 1분기와 2분기 표를 합쳐 상반기 표를 만드는 식입니다. SQL의 UNION과 같은 개념입니다.

병합(Merge)

키 열을 기준으로 두 표를 가로로 연결합니다. 주문 표에 고객 표를 붙여 고객명을 가져오는, SQL의 JOIN에 해당합니다. 조인 종류도 선택할 수 있습니다.

VLOOKUP/XLOOKUP으로 일일이 값을 끌어오던 작업을, 병합 한 번으로 안정적이고 빠르게 처리할 수 있습니다.

6. 매개변수 — 유연한 쿼리

매개변수를 쓰면 경로, 날짜, 임계값 같은 값을 쿼리 바깥에서 바꿀 수 있습니다. 파일 경로를 매개변수로 빼 두면, 다른 환경에서도 경로만 바꿔 같은 쿼리를 재사용할 수 있습니다. 임계값을 매개변수로 두면 "기준 금액"을 한 곳에서 조정해 전체 필터에 반영합니다.

매개변수는 "홈 → 매개변수 관리"에서 정의하고, 변환 단계에서 값 대신 매개변수를 참조하도록 지정합니다.

7. M 언어 맛보기

클릭으로 만든 모든 단계는 사실 M 언어 코드로 저장됩니다. "고급 편집기"를 열면 전체 코드를 볼 수 있고, 직접 수정도 가능합니다. 다음은 간단한 예시입니다.

let
    원본 = Csv.Document(File.Contents("C:\data\sales.csv")),
    승격 = Table.PromoteHeaders(원본),
    형식변경 = Table.TransformColumnTypes(승격, {{"매출", Int64.Type}}),
    필터 = Table.SelectRows(형식변경, each [매출] > 1000)
in
    필터

각 줄이 "적용된 단계" 하나에 대응합니다. let ... in 구조 안에서 단계마다 이전 결과를 입력으로 받아 다음 변환을 정의합니다. 평소에는 클릭으로 충분하지만, 반복 로직이나 동적 처리가 필요할 때 M 코드를 직접 다루면 훨씬 강력해집니다.

8. 새로 고침 자동화와 한계

자동 새로 고침 옵션

쿼리 속성에서 다음을 설정할 수 있습니다.

• 파일을 열 때 새로 고침
• 일정 간격(분)마다 백그라운드 새로 고침
• "모두 새로 고침"(Ctrl + Alt + F5)으로 전체 쿼리 일괄 갱신

이렇게 하면 원본만 갱신하고 파일을 열면 리포트가 최신 상태로 유지됩니다.

한계

• 완전 무인 자동화는 어렵다: 데스크톱 엑셀의 새로 고침은 결국 파일을 누군가 열거나 매크로/스케줄러가 실행해야 합니다. 진정한 서버 측 자동 갱신은 Power BI 서비스나 클라우드 환경이 더 적합합니다.
• 성능: 단계가 많거나 데이터가 크면 새로 고침이 느려집니다. 불필요한 열은 일찍 제거하고, 필터를 앞 단계로 옮기는 것이 좋습니다.
• 자격 증명: 데이터베이스·웹 소스는 새로 고침 때마다 인증이 필요할 수 있습니다.

9. 동적 배열, Python in Excel과의 관계

Power Query는 "데이터를 가져와 정제하는" 단계에 강합니다. 가져온 뒤의 계산·요약은 동적 배열 함수(FILTER, GROUPBY 등)가 더 가볍고 즉각적입니다. 둘은 경쟁이 아니라 역할 분담입니다.

• Power Query: 정제·통합·형식 표준화(무거운 ETL)
• 동적 배열: 시트 위에서의 빠른 계산·요약
• Python in Excel: Power Query로 정제한 데이터를 받아 고급 통계·머신러닝·시각화

전형적인 흐름은 "Power Query로 깨끗한 표를 만들고 → 동적 배열로 요약하거나 Python으로 분석"입니다.

10. 실무 사례 — 월간 리포트 자동화

매달 지점별 매출 CSV가 한 폴더에 쌓이는 상황을 가정해 봅시다.

1. 폴더에서 가져와 모든 지점 파일 결합
2. 형식 변경으로 날짜·금액 형식 통일
3. 불필요한 열 제거, 빈 행 필터
4. 조건 열로 매출 등급 분류
5. 고객 마스터 표와 병합해 지역 정보 부가
6. 결과를 워크시트로 적재
7. 동적 배열/피벗으로 요약 대시보드 구성
8. 쿼리 속성에서 "열 때 새로 고침" 설정

다음 달에는 새 CSV를 폴더에 넣고 파일을 여는 것만으로 모든 리포트가 갱신됩니다. 한 번 만든 파이프라인이 매달 일을 대신해 줍니다.

11. 흔한 함정 — 데이터형과 로캘

• 자동 형식 추론 신뢰 금지: Power Query가 앞쪽 몇 행만 보고 형식을 추론하다 뒤쪽에서 깨지는 경우가 있습니다. 형식 변경 단계를 명시적으로 두세요.
• 로캘(지역) 문제: 날짜의 일/월 순서, 소수점·천 단위 구분 기호가 지역마다 다릅니다. "로캘을 사용하여 형식 변경"으로 원본의 지역을 지정해야 1월 3일이 3월 1일로 둔갑하지 않습니다.
• 열 이름 변경 의존성: 원본의 열 이름이 바뀌면 이후 단계가 깨집니다. 가능하면 머리글 승격 직후에 안정적인 이름으로 변경하세요.
• 숨은 공백·특수문자: 보이지 않는 공백 때문에 병합·필터가 어긋날 수 있습니다. "정리"와 "공백 제거" 변환을 초반에 적용하세요.
• 상대 경로 부재: 파일/폴더 경로가 절대 경로로 박혀 있으면 다른 PC에서 깨집니다. 매개변수로 경로를 빼 두면 이식성이 좋아집니다.

마치며

Power Query의 가치는 "한 번 만들면 반복이 사라진다"는 데 있습니다. 손으로 하던 정제 과정을 단계로 기록해 두면, 매달 새로 고침 한 번으로 같은 결과를 얻습니다. 실수가 줄고, 절차가 문서화되며, 인수인계도 쉬워집니다.

처음에는 단일 CSV 정제부터 시작하세요. 익숙해지면 폴더 결합과 병합으로 넓혀 가고, 마지막에 매개변수와 M 코드까지 손대면, 웬만한 반복 리포트는 클릭 한 번으로 끝낼 수 있습니다. 정제는 Power Query, 요약은 동적 배열, 분석은 Python으로 역할을 나누면 엑셀이 강력한 데이터 작업대가 됩니다.

참고 자료

• Power Query 정보 (Microsoft 지원)
• Power Query 시작하기 (Microsoft Learn)
• 폴더에서 파일 가져오기 (Microsoft Learn)
• 쿼리 병합 (Microsoft 지원)
• 쿼리 추가 (Microsoft 지원)
• 열 피벗 및 언피벗 (Microsoft Learn)
• M 언어 사양 (Microsoft Learn)
• Excel의 Python (Microsoft 지원)