Power Query는 데이터 정리 및 변환 작업을 자동화할 수 있는 기능을 제공합니다. 한 번 작성한 쿼리를 저장하고 재사용하면 반복적인 작업을 간소화할 수 있습니다. 이 문서에서는 반복 작업 자동화에 대한 상세한 설명과 실무 예제를 제공합니다.
1. 반복 작업 자동화란?
정의
- Power Query에서 데이터 정리 및 변환 단계를 저장하여 동일한 작업을 반복적으로 수행할 때 자동으로 적용되도록 설정하는 기능.
- 데이터 소스가 업데이트되거나 새로운 데이터가 추가될 때, 동일한 쿼리를 실행하여 시간을 절약하고 일관성을 유지.
장점
- 시간 절약: 매달 또는 매주 동일한 데이터를 정리할 필요 없음.
- 일관성: 동일한 변환 규칙을 적용하여 데이터 품질 유지.
- 효율성: 대량의 데이터를 처리할 때 오류 발생 가능성을 줄임.
2. Power Query 자동화의 핵심 요소
2.1 단계 저장
- Power Query에서 수행한 모든 작업은 단계별로 기록되며, 사용자는 언제든지 해당 단계를 수정하거나 삭제할 수 있습니다.
- 각 단계는 “적용된 단계” 목록에서 확인 가능.
2.2 쿼리 재사용
- 저장된 쿼리는 동일한 구조의 다른 데이터 소스에 재사용 가능.
- 쿼리를 복제하거나 참조하여 유사한 작업을 수행.
2.3 새로 고침
- 데이터를 새로 고침하면 저장된 쿼리가 자동으로 실행되어 최신 데이터를 반영.
3. 반복 작업 자동화 예제
3.1 월별 판매 데이터 자동 처리
문제
매달 새로운 판매 데이터 파일을 가져와 정리하고 통합해야 함.
작업 목표
- 매달 파일을 지정된 폴더에 추가하면 Power Query가 자동으로 데이터를 통합하고 변환.
단계
- 폴더 데이터 가져오기:
- Power Query에서 데이터 > 폴더에서 데이터 가져오기 선택.
- 판매 데이터 파일이 저장된 폴더를 선택.
- 파일 통합:
- 모든 파일의 데이터를 통합.
- “변환 데이터” 옵션 선택 후 필요 없는 열 제거 및 데이터 형식 정리.
- 쿼리 저장:
- 변환 작업을 저장하여 매달 새 데이터를 추가할 때 자동으로 반영되도록 설정.
- 새로 고침:
- 파일이 추가될 때마다 “새로 고침” 버튼 클릭.
결과
| 월 | 제품명 | 매출 |
|---|---|---|
| 1월 | 노트북 | 1,000,000 |
| 1월 | 스마트폰 | 800,000 |
| 2월 | 노트북 | 1,200,000 |
| 2월 | 스마트폰 | 900,000 |
3.2 웹 데이터 자동 업데이트
문제
실시간 환율 데이터를 웹사이트에서 주기적으로 가져와 최신 정보를 유지해야 함.
작업 목표
- 웹 데이터를 매일 새로 고침하여 엑셀 보고서에 자동 반영.
단계
- 웹 데이터 가져오기:
- Power Query에서 데이터 > 웹에서를 선택.
- 환율 데이터가 포함된 웹 페이지 URL 입력.
- 필요 데이터 선택:
- 테이블 형식으로 제공되는 환율 데이터를 선택.
- 데이터 변환:
- 필요한 열만 남기고 데이터 형식을 숫자 또는 텍스트로 변경.
- 새로 고침 설정:
- 데이터 새로 고침 주기를 설정하여 매일 자동으로 업데이트.
결과
| 통화 | 환율 |
|---|---|
| USD | 1,300.50 |
| EUR | 1,450.75 |
| JPY | 9.85 |
4. 고급 기능
4.1 쿼리 복제와 참조
- 쿼리 복제:
- 기존 쿼리를 복사하여 동일한 작업을 다른 데이터에 적용.
- 쿼리 참조:
- 기존 쿼리를 기반으로 새로운 작업을 추가로 수행.
사용 사례
- 복제: 동일한 데이터 변환 작업을 여러 데이터 소스에 적용.
- 참조: 기존 데이터 정리에 추가 계산 작업을 수행.
4.2 사용자 정의 함수
- 반복적인 변환 작업을 사용자 정의 함수로 만들어 여러 쿼리에 재사용.
- 예: 날짜 형식을 “yyyy-mm-dd”로 변환하는 사용자 정의 함수 생성.
단계
- Power Query 편집기에서 새 쿼리 추가.
- 함수 코드 작성 (M 언어 사용).
(inputDate as date) => Date.ToText(inputDate, "yyyy-MM-dd") - 함수 저장 후 다른 쿼리에서 호출.
5. 주의사항
- 파일 구조 일관성 유지:
- 데이터 소스의 구조가 변경되면 저장된 쿼리가 작동하지 않을 수 있음.
- 새로 고침 주기 관리:
- 데이터 새로 고침 시 서버나 네트워크 연결 문제가 없는지 확인.
- 백업:
- 쿼리를 수정하기 전에 백업본을 저장하여 원본을 보호.