"엑셀은 그냥 표 만드는 거 아니야?" 라고 생각했다면, 오늘 이 글을 읽고 나서 완전히 생각이 바뀔 거야! 😄

사실 많은 직장인들이 엑셀을 단순한 스프레드시트 도구로만 쓰고 있어. 근데 마이크로소프트가 엑셀에 몰래(?) 심어놓은 두 가지 강력한 무기가 있거든. 바로 파워 쿼리(Power Query)와 파워 피벗(Power Pivot)이야.

이 두 녀석을 제대로 활용하면 수십만, 심지어 수백만 행의 데이터도 엑셀에서 처리할 수 있어. 기존 엑셀 시트의 한계인 1,048,576행을 넘어서는 데이터도 파워 피벗의 데이터 모델을 통해 다룰 수 있다는 게 핵심이야!

Python이나 R 같은 프로그래밍 언어를 배우지 않아도, SQL을 몰라도, 엑셀 인터페이스 안에서 GUI 기반으로 빅데이터를 처리할 수 있다는 게 이 두 도구의 가장 큰 매력이지. 🎉

🔍 파워 쿼리가 뭔데?

파워 쿼리는 한마디로 "데이터 수집 + 정제 + 변환"을 자동화해주는 ETL(Extract, Transform, Load) 도구야.

ETL이 뭔지 모르는 사람도 걱정 마! 쉽게 말하면 이런 거야:
E(Extract) = 여기저기 흩어진 데이터를 가져오고
T(Transform) = 내가 원하는 형태로 다듬고
L(Load) = 엑셀에 올려놓는 것!

기존에는 이 과정을 수작업으로 했잖아. 매달 보고서 만들 때마다 복사-붙여넣기, 공백 제거, 형식 통일... 생각만 해도 지치지? 😩
파워 쿼리는 이 모든 과정을 한 번만 설정해두면 버튼 하나로 자동 반복할 수 있게 해줘!

📂 파워 쿼리로 연결할 수 있는 데이터 소스

파워 쿼리가 연결할 수 있는 데이터 소스의 종류가 어마어마해. 한번 살펴볼까?

1️⃣ 쿼리 편집기 인터페이스 이해하기

파워 쿼리를 처음 열면 쿼리 편집기(Query Editor)라는 별도 창이 뜨는데, 여기서 모든 마법이 일어나. 주요 구성 요소를 알아보자:

2️⃣ 데이터 정제 핵심 기법

실무에서 가장 많이 쓰는 데이터 정제 기법들을 정리해봤어:

3️⃣ 쿼리 병합 vs 쿼리 추가

이 두 기능은 SQL의 JOIN과 UNION과 비슷한 개념이야. 헷갈리는 사람이 많으니까 명확하게 구분해줄게!

4️⃣ M 언어 기초 - 알면 10배 강해진다!

파워 쿼리의 모든 작업은 내부적으로 M 언어(Power Query Formula Language)로 기록돼.
GUI로 작업하면 자동으로 M 코드가 생성되지만, M 언어를 직접 쓸 줄 알면 GUI로 할 수 없는 복잡한 작업도 가능해!

// M 언어 기본 구조 예시
let
    // 1. 데이터 소스 연결
    원본 = Excel.CurrentWorkbook(){[Name="판매데이터"]}[Content],
    
    // 2. 데이터 형식 변환
    형식변환 = Table.TransformColumnTypes(원본,{
        {"날짜", type date}, 
        {"금액", type number}
    }),
    
    // 3. 필터링 (2024년 데이터만)
    필터링 = Table.SelectRows(형식변환, 
        each Date.Year([날짜]) = 2024),
    
    // 4. 그룹화 (월별 합계)
    그룹화 = Table.Group(필터링, {"월"}, {
        {"합계", each List.Sum([금액]), type number}
    })
in
    그룹화

5️⃣ 매개변수(Parameter) 활용 - 동적 쿼리 만들기

파워 쿼리의 숨겨진 고수 기능 중 하나가 바로 매개변수야!
예를 들어 "2024년 1월" 데이터만 가져오는 쿼리를 만들었는데, 매달 "2024년 2월", "2024년 3월"로 바꿔야 한다면? 매개변수를 쓰면 셀 값만 바꿔도 쿼리 전체가 동적으로 변해!

🎯 파워 피벗이 필요한 이유

파워 쿼리로 데이터를 깔끔하게 정제했어. 근데 이걸 분석하려면? 일반 피벗 테이블로는 한계가 있어.

파워 피벗은 엑셀 내부에 별도의 인메모리 데이터베이스 엔진(xVelocity)을 탑재해서, 수백만 행의 데이터를 초고속으로 처리할 수 있게 해줘. 그리고 여러 테이블 간의 관계(Relationship)를 설정해서 복잡한 분석을 가능하게 하지.

쉽게 말하면 엑셀 안에 미니 데이터베이스를 만드는 거야! 🗄️

📐 데이터 모델과 관계 설정

파워 피벗의 핵심은 데이터 모델(Data Model)이야. 여러 테이블을 관계로 연결해서 마치 하나의 거대한 테이블처럼 분석할 수 있어.

관계 설정의 기본 원칙:

DAX(Data Analysis Expressions)는 파워 피벗에서 사용하는 수식 언어야.
엑셀 함수와 비슷하게 생겼지만, 훨씬 강력하고 데이터 모델 전체를 대상으로 계산할 수 있어.

DAX에는 두 가지 종류의 계산이 있어:

🔥 실무에서 자주 쓰는 DAX 함수 TOP 10

🌟 CALCULATE - DAX의 왕을 제대로 이해하자!

DAX를 배우면서 가장 중요한 함수가 바로 CALCULATE야. 이걸 이해하면 DAX의 절반은 마스터한 거라고 해도 과언이 아니야!

// CALCULATE 기본 구조
CALCULATE(표현식, 필터1, 필터2, ...)

// 예시 1: 특정 카테고리 매출
전자제품매출 = CALCULATE(
    SUM(판매[금액]),
    제품[카테고리] = "전자제품"
)

// 예시 2: 전년 동기 대비 성장률
YoY성장률 = 
VAR 올해매출 = [총매출]
VAR 작년매출 = CALCULATE([총매출], SAMEPERIODLASTYEAR(날짜[날짜]))
RETURN
    DIVIDE(올해매출 - 작년매출, 작년매출, 0)

// 예시 3: 누적 합계 (YTD)
연누적매출 = CALCULATE(
    [총매출],
    DATESYTD(날짜[날짜])
)

📅 날짜 테이블 - 시계열 분석의 필수품

파워 피벗에서 시계열 분석(전년 대비, 누적 합계 등)을 하려면 반드시 날짜 테이블이 필요해!
날짜 테이블은 분석 기간의 모든 날짜가 빠짐없이 포함된 테이블이야.

// DAX로 날짜 테이블 만들기
날짜테이블 = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "연도", YEAR([Date]),
    "분기", "Q" & QUARTER([Date]),
    "월번호", MONTH([Date]),
    "월명", FORMAT([Date], "MMM"),
    "주차", WEEKNUM([Date]),
    "요일", FORMAT([Date], "DDD"),
    "연월", FORMAT([Date], "YYYY-MM"),
    "회계연도", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date])-1)
)

자, 이제 파워 쿼리와 파워 피벗을 함께 활용해서 실제 빅데이터 처리 파이프라인을 구축하는 방법을 알아보자!
예시 시나리오: 전국 100개 지점의 월별 판매 데이터(CSV 파일) 자동 분석 시스템 구축

🔄 증분 새로 고침 (Incremental Refresh)

데이터가 수백만 행이 되면 매번 전체를 새로 고침하는 게 시간이 오래 걸려.
증분 새로 고침을 설정하면 새로 추가된 데이터만 업데이트해서 처리 시간을 획기적으로 줄일 수 있어!

파워 쿼리에서 날짜 범위 매개변수(RangeStart, RangeEnd)를 설정하고, 파워 피벗에서 증분 새로 고침 정책을 구성하면 돼.

🔗 여러 파워 쿼리 쿼리 간 참조 (쿼리 의존성)

파워 쿼리에서는 하나의 쿼리가 다른 쿼리를 참조할 수 있어. 이를 통해 모듈화된 쿼리 구조를 만들 수 있지.

// 예시: 공통 정제 로직을 별도 함수 쿼리로 만들기
// 함수 쿼리: 데이터정제함수
(입력테이블 as table) as table =>
let
    공백제거 = Table.TransformColumns(입력테이블, 
        {{"이름", Text.Trim}, {"주소", Text.Trim}}),
    오류제거 = Table.RemoveRowsWithErrors(공백제거),
    형식변환 = Table.TransformColumnTypes(오류제거,
        {{"날짜", type date}, {"금액", type number}})
in
    형식변환

// 이 함수를 여러 쿼리에서 재사용!
// 쿼리1: 데이터정제함수(서울지점원본)
// 쿼리2: 데이터정제함수(부산지점원본)

📊 KPI(핵심성과지표) 설정

파워 피벗에서는 측정값을 기반으로 KPI를 설정할 수 있어. KPI를 설정하면 피벗 테이블에서 목표 대비 달성률을 신호등 아이콘으로 시각화할 수 있어!

🔍 고급 DAX 패턴 - 실무 바로 적용!

// 패턴 1: 이동 평균 (3개월 이동 평균)
3개월이동평균 = 
AVERAGEX(
    DATESINPERIOD(날짜[날짜], LASTDATE(날짜[날짜]), -3, MONTH),
    [월매출]
)

// 패턴 2: 상위 N개 필터링
상위10제품매출 = 
CALCULATE(
    [총매출],
    TOPN(10, ALL(제품[제품명]), [총매출])
)

// 패턴 3: 동적 순위 (필터에 반응하는 순위)
동적순위 = 
RANKX(
    ALLSELECTED(제품[제품명]),
    [총매출],
    ,
    DESC,
    DENSE
)

// 패턴 4: 전월 대비 증감률
전월대비증감률 = 
VAR 이번달 = [총매출]
VAR 지난달 = CALCULATE([총매출], PREVIOUSMONTH(날짜[날짜]))
RETURN
    DIVIDE(이번달 - 지난달, 지난달, BLANK())

⚡ 성능 최적화 팁

파워 피벗 모델이 느려진다면 아래 최적화 기법을 적용해봐:

파워 쿼리와 파워 피벗이 실제로 어떤 업무에서 빛을 발하는지 구체적인 사례를 살펴보자!
마치 재능넷에서 엑셀 전문가들이 의뢰받는 프로젝트들처럼, 이런 실무 케이스들이 정말 많아.

처음 배우는 사람들을 위해 단계별 학습 로드맵을 정리해봤어. 이 순서대로 따라가면 효율적으로 마스터할 수 있어!

파워 쿼리와 파워 피벗을 처음 배울 때 많이들 하는 실수들을 정리해봤어. 미리 알면 시행착오를 줄일 수 있어!