Power Query 파이프라인: CSV→모델→리포트 (5 Shots)
이 문서는 월별 CSV를 자동 병합해 하나의 표준 테이블로 만들고, 데이터 모델에 적재해 피벗 리포트를 배포하는 5 Shots 흐름입니다. Power Query는 Excel의 Get & Transform 기술로 다양한 소스(CSV/JSON/PDF/SQL 등)를 가져와 변환할 수 있습니다. :contentReference[oaicite:5]{index=5}
요약 정리
| 단계 | 핵심 작업 | 근거/참조 |
|---|---|---|
| 흡수 | Data ▶ Get Data ▶ From Folder로 다중 파일 가져오기 | :contentReference[oaicite:6]{index=6} |
| 파싱 | CSV 구분자/인코딩/형식(Using Locale) 지정 | :contentReference[oaicite:7]{index=7} |
| 정형 | 열 정리·형 변환·머지·오류 행 처리(Keep/Remove/Replace) | :contentReference[oaicite:8]{index=8} |
| 적재 | 데이터 모델로 로드 후 피벗 작성 | :contentReference[oaicite:9]{index=9} |
| 운영 | 새로 고침 간격·의존성 순서·웹/온라인 한계 확인 | :contentReference[oaicite:10]{index=10} |
Shot 1 — 파이프라인 개요·요건
- 소스: 월별 CSV/ERP 덤프/REST 응답(추후 From Web로 확장). Power Query는 CSV·JSON·PDF·SQL·SharePoint 등 다수의 소스를 지원합니다. :contentReference[oaicite:11]{index=11}
- 흐름: From Folder ▶ Combine으로 스키마 동일 파일을 자동 결합. :contentReference[oaicite:12]{index=12}
- 새로 고침: “연결 속성▶사용”에서 분 단위 자동 새로 고침·백그라운드 새로 고침 설정 가능. :contentReference[oaicite:13]{index=13}
팁: 폴더 안에 있는 월별 파일(예: 2025-07_orders.csv, 2025-08_orders.csv)은 동일한 열 구조를 유지하세요. Power Query의 Combine 마법사는 동일 스키마에서 가장 안정적입니다. :contentReference[oaicite:14]{index=14}
Shot 2 — 데이터 흡수(From Folder→CSV 파서)
- 데이터 ▶ Get Data ▶ From File ▶ From Folder ▶ 폴더 선택 ▶ Combine & Transform. :contentReference[oaicite:15]{index=15}
- CSV 파서에서 Delimiter(구분자)·File Origin(인코딩)·Type Detection을 명시합니다. 콜론/세미콜론/탭/사용자 지정 등 선택 가능. :contentReference[oaicite:16]{index=16}
- 날짜/숫자 로캘 차이는 Change Type ▶ Using Locale로 안전 변환. (예: yyyy-mm-dd vs dd/mm/yyyy) :contentReference[oaicite:17]{index=17}
- 인코딩/따옴표 처리 등 세부 제어가 필요하면
Csv.Document함수 매개변수를 사용합니다(기본 UTF-8). :contentReference[oaicite:18]{index=18}
// 예시: Csv.Document로 구분자·인코딩 지정
Csv.Document(File.Contents(Path),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])Shot 3 — 변환·정형(표준화 스텝)
- 불필요 열 제거·이름 표준화: 헤더 이름 변경은 새로 고침 시 오류의 주요 원인입니다 → 머리글을 고정된 규칙으로 표준화. :contentReference[oaicite:19]{index=19}
- 데이터 형식 지정: 텍스트/정수/날짜·시간을 명시해 품질 바를 녹색으로 유지.
- 매핑 테이블 머지: 예) 택배사명 표준화(머지 후 확장).
- 오류 처리: Keep/Remove errors·Replace values로 가드레일 구성. :contentReference[oaicite:20]{index=20}
// 예시: 오류 행 제거 후 빈값으로 대체
try Table.TransformColumns(#"Changed Type", {{"Qty", each Number.From(_), Int64.Type}})
otherwise Table.ReplaceErrorValues(#"Changed Type", {{"Qty", 0}})Shot 4 — 모델 적재·새로 고침
- 데이터 모델로 로드: 마침 ▶ Load To… ▶ Add this data to the Data Model 체크(또는 Power Pivot ▶ Add to Data Model). 이후 피벗을 모델 기준으로 생성. :contentReference[oaicite:21]{index=21}
- 새로 고침 전략: 연결 속성의 “매분 새로 고침/백그라운드” 설정으로 자동화. :contentReference[oaicite:22]{index=22}
- 순서: Power Query는 의존 관계에 따라 자동으로 순서를 계산해 상위→하위 쿼리 순으로 새로 고침합니다. :contentReference[oaicite:23]{index=23}
Shot 5 — 리포트 배포·운영
- 피벗 리포트: 모델 기준 피벗(주문수/반품률/OTD 등)을 만들어 “주/월간” 시트에 배치.
- 매개변수(Parameters): 폴더 경로/연도 등 변경값은 Manage Parameters로 변수화해 배포 간 편리하게 전환. :contentReference[oaicite:24]{index=24}
- 웹/온라인 주의: Excel for the web의 Power Query는 소스 제약이 있어 같은 통합문서 내부 테이블/범위가 가장 안정적으로 새로 고침됩니다. :contentReference[oaicite:25]{index=25}
예제 데이터(물류/EC)
폴더 구조: \\EC\orders\monthly\ 안에 2025-07_orders.csv, 2025-08_orders.csv…
CSV 열: Order_ID, Date(yyyy-mm-dd), SKU, Qty, Ship_Date, Carrier, Return_Reason
매핑 테이블(Excel 시트 Mapping): Carrier_Raw → Carrier_Std (예: CJ/대한통운 → CJ Logistics)
관련 학습(Exceljump 내부 링크)
현업 배포용 1페이지
- 폴더 규칙: 파일명/열 스키마 고정(월간 추가만).
- 파서: 구분자·인코딩·Locale 지정 후 테스트 10행.
- 정형: 머리글 표준화, 매핑 머지, 오류 행 규칙(Keep/Remove/Replace).
- 적재: 데이터 모델 로드, 피벗 템플릿 저장.
- 운영: 새로 고침 주기, 의존성 확인, 웹/온라인 한계 메모.