Power Query 튜토리얼
Power Query란 무엇이며 왜 모든 것을 바꾸는가
Power Query는 엑셀에 내장된 ETL(추출, 변환, 로드) 엔진입니다. 쉽게 말해: 거의 모든 소스에서 데이터를 가져와 자동으로 정리 및 재구성하고 워크시트에 로드하는 도구이며, 모든 단계가 기록되고 반복 가능합니다. 매주 같은 지저분한 형식으로 도착하는 보고서를 금요일 오후에 수동으로 정리한 적이 있다면, Power Query는 여러분이 기다려온 해결책입니다.
매크로와 달리 Power Query는 코딩이 필요 없습니다. 시각적 인터페이스를 통해 변환 단계를 구축하면 Power Query가 백그라운드에서 M 언어로 기록합니다. 다음 주 파일이 도착하면 클릭 한 번으로 모든 정리 단계가 다시 적용됩니다.
단계별 가이드: 주간 매출 보고서 정리 자동화
시나리오: 매주 월요일 일관되지 않은 날짜 형식, 병합된 제품 카테고리(한 열에 카테고리-하위카테고리), 누락된 매출 금액 행, 하단의 추가 요약 행이 포함된 sales_YYYYMMDD.csv를 받습니다. 이를 자동으로 정리하는 Power Query를 구축하세요.
1단계 — 원시 데이터 가져오기
- 데이터 > 데이터 가져오기 > 파일에서 > 텍스트/CSV에서.
- 매출 CSV 파일을 선택하세요. 탐색기가 데이터를 미리 봅니다. Power Query가 이미 구분 기호와 데이터 유형을 감지한 것을 확인하세요.
- 데이터 변환("로드"가 아님)을 클릭하세요. 그러면 Power Query 편집기가 열리며 모든 정리 작업이 여기서 이루어집니다.
2단계 — 머리글 승격 및 불필요한 행 제거
- 첫 번째 행이 머리글인 경우: 홈 > 첫 행을 머리글로 사용. 항상 이것을 먼저 수행하세요 — 머리글은 이후 단계에서 열 이름 참조를 가능하게 합니다.
- 하단의 요약 행을 제거하세요. 날짜 열을 필터링합니다: 드롭다운을 클릭하고 "Total"과 같은 텍스트나 빈 값이 포함된 행의 선택을 해제하세요. 또는 추가 행 수를 알고 있다면 홈 > 행 제거 > 하위 행 제거를 사용하세요.
- 완전히 빈 행 제거: 홈 > 행 제거 > 빈 행 제거.
3단계 — 제품 카테고리 열 분할
- 제품 열에는 "Electronics-Accessories" — 카테고리-하이픈-하위카테고리가 포함되어 있습니다. 두 개의 열이 필요합니다.
- 제품 열을 선택하세요. 변환 > 열 분할 > 구분 기호로.
- 구분 기호: 사용자 지정,
-입력. 분할 위치: 가장 왼쪽 구분 기호 (중요: 일부 하위카테고리에 "Audio-Visual"과 같이 하이픈이 포함됨). - 확인을 클릭하세요. 이제 Product.1(카테고리)과 Product.2(하위카테고리)가 생깁니다. 이름을 변경하려면: 머리글을 우클릭 > 이름 바꾸기.
4단계 — 날짜 형식 수정 및 누락된 값 처리
- 날짜 열을 선택하세요. 변환 > 데이터 형식 > 날짜. 일부 날짜가 변환되지 않으면(오류 표시), 열 드롭다운 > 오류 바꾸기 > 대체 값으로 오늘 날짜를 입력하거나 해당 행을 검토하도록 필터링하세요.
- 매출 금액 열의 경우: 선택하고 변환 > 값 바꾸기. 찾을 값:
null, 바꿀 값:0. 이렇게 하면 누락된 매출이 빈칸으로 남지 않고 0으로 대체됩니다. - 의미 없는 항목을 나타내는 경우 매출 금액이 0인 행을 제거하세요(선택 사항): 매출 금액 > 숫자 필터 > 다음보다 큼 > 0으로 필터링합니다.
5단계 — 로드 및 자동 새로 고침 설정
- 홈 > 닫기 및 다음으로 로드. "테이블"과 "새 워크시트"를 선택하고 확인을 클릭하세요.
- 정리된 데이터가 엑셀에 나타납니다. 이제 자동화하려면: 데이터 > 쿼리 및 연결(오른쪽 창).
- 쿼리를 우클릭 > 속성. 파일을 열 때 데이터 새로 고침을 체크하세요. 실시간 대시보드의 경우 선택적으로 X분마다 새로 고침을 설정할 수 있습니다.
- 다음 주: 새 CSV를 같은 위치에 같은 이름으로 저장하고, 이 통합 문서를 열고 데이터 > 모두 새로 고침을 클릭하세요. 모든 정리 단계가 자동으로 재생됩니다.
핵심 테크닉
- 분석 준비 데이터를 위한 열 피벗 해제. 데이터에 월이 별도 열(1월, 2월, 3월)로 있는 경우 설명 열을 선택하고 변환 > 다른 열 피벗 해제를 선택하세요. 넓은 테이블이 피벗 테이블 친화적인 긴 테이블이 됩니다.
- VLOOKUP 대신 쿼리 병합. 홈 > 쿼리 병합은 일치하는 열을 기준으로 두 테이블을 결합합니다 — Power Query 버전의 VLOOKUP이지만 수백만 행과 여러 조인 유형(왼쪽, 오른쪽, 완전 외부, 내부, 안티)을 처리합니다.
- 요약을 위한 그룹화. 변환 > 그룹화로 카테고리별로 데이터(합계, 개수, 평균)를 집계합니다 — 데이터가 워크시트에 도달하기 전에 실행되는 피벗 테이블과 같습니다.
- 명확성을 위해 단계 이름 변경. "변경된 유형", "제거된 열", "필터링된 행"은 20단계 후에는 의미가 없어집니다. 단계를 우클릭 > 이름 바꾸기로 "빈 행 제거" 또는 "전체 이름 분할"과 같이 설명을 추가하세요.
흔한 실수
- 불필요하게 수백만 행을 로드. 로드하기 전에 행을 필터링하세요. 개발 중에는 홈 > 행 유지 > 상위 행 유지를 사용하고 전체 데이터 준비가 되면 필터를 제거하세요.
- 데이터 유형을 명시적으로 수정하지 않음. Power Query가 유형을 추측하지만 틀릴 수 있습니다. 각 열을 선택하고 홈 > 데이터 형식을 사용하여 올바르게 설정하세요: ID는 텍스트, 통화는 소수, 날짜는 날짜. 잘못된 유형은 Power Query 오류의 가장 큰 원인입니다.
- Power Query가 대소문자를 구분한다는 것을 잊음. 엑셀 수식과 달리 M 언어와 텍스트 필터는 대소문자를 구분합니다. 먼저 대문자/소문자 변환을 적용하지 않으면 필터나 병합에서 "ABC"가 "abc"와 일치하지 않습니다.
- 단일 단계에 변환을 과도하게 중첩. 각 논리적 변환에 별도의 단계를 사용하세요. 독립적인 단계는 디버그, 재정렬, 동료에게 설명하기가 더 쉽습니다.
고급 팁
- 폴더 내 파일 자동 결합. 데이터 가져오기 > 파일에서 > 폴더에서, 그런 다음 결합 > 결합 및 변환을 클릭하세요. Power Query가 폴더의 모든 파일에 변환을 적용합니다. 새 파일을 넣고 새로 고침하면 — 주간 보고서 병합이 자동화됩니다.
- 동적 쿼리를 위한 매개변수. 홈 > 매개변수 관리로 사용자가 쿼리를 편집하지 않고 변경할 수 있는 명명된 값(파일 경로, 날짜 범위, 임계값)을 만들 수 있습니다. 셀프 서비스 보고를 위해 필터 단계에서 매개변수를 참조하세요.
- Try Otherwise를 사용한 오류 처리. 변환을
try ... otherwise ...로 감싸세요:try Date.FromText([Column]) otherwise null. 하나의 잘못된 셀로 인해 전체 쿼리가 실패하는 것을 방지합니다.