4. 데이터 대변신! 핵심 변환 기능 TOP 10 ✨

자, 이제 재료(데이터)를 주방(파워 쿼리 편집기)으로 모두 가져왔습니다. 지금부터는 본격적으로 재료를 손질하는 방법을 배워볼 시간입니다. 파워 쿼리에는 수십 가지의 변환 기능이 있지만, 이 10가지 핵심 기능만 마스터해도 웬만한 데이터 전처리 작업의 90% 이상을 해결할 수 있습니다.

각 기능은 주로 [홈], [변환], [열 추가] 탭에 나뉘어 있습니다.

• [변환] 탭: 선택한 열 자체를 변경합니다. (예: '텍스트' 열을 '숫자' 열로 바꿈)
• [열 추가] 탭: 기존 열은 그대로 두고, 변환 결과를 새로운 열로 추가합니다. (예: '텍스트' 열을 기반으로 한 '숫자' 열을 새로 만듦)

상황에 따라 원본을 유지해야 할 때는 [열 추가]를, 필요 없을 때는 [변환]을 사용하면 됩니다.

1. 열 제거 / 열 선택 (필요한 것만 남기기)

가장 기본적이면서도 중요한 작업입니다. 원본 데이터에는 분석에 불필요한 열들이 많이 포함되어 있는 경우가 많습니다. 이런 열들을 미리 제거해주면 데이터가 한결 깔끔해지고 처리 속도도 빨라집니다.

• 방법 1 (열 제거): 제거하고 싶은 열의 머리글을 클릭합니다. (여러 개를 선택하려면 Ctrl 키를 누른 채 클릭) > 마우스 오른쪽 버튼 클릭 > [열 제거] 또는 [홈] 탭 > [열 관리] > [열 제거]
• 방법 2 (열 선택): 남기고 싶은 열의 머리글만 선택합니다. > 마우스 오른쪽 버튼 클릭 > [다른 열 제거] 또는 [홈] 탭 > [열 관리] > [열 선택]

실무 팁: 보통은 '열 선택' 방법이 더 안전하고 효율적입니다. 왜냐하면 나중에 원본 데이터에 새로운 열이 추가되더라도, 내가 선택한 열만 가져오도록 설정되어 있기 때문에 쿼리가 깨질 위험이 적습니다.

2. 행 필터링 (원하는 조건의 데이터만 보기)

엑셀의 필터 기능과 동일합니다. 특정 조건을 만족하는 행만 남기거나 제거할 수 있습니다.

• 방법: 필터링하고 싶은 열 머리글의 드롭다운 화살표(▼)를 클릭합니다. > 엑셀 필터처럼 원하는 값을 체크/해제하거나, [텍스트 필터], [숫자 필터], [날짜/시간 필터] 등에서 다양한 조건을 선택할 수 있습니다. (예: '같음', '같지 않음', '시작 문자', '보다 큼' 등)
• Null 값 제거: 데이터에 빈 값(null)이 있는 경우, 필터 메뉴에서 '(null)'을 체크 해제하면 해당 행을 쉽게 제거할 수 있습니다. 데이터 정제 시 가장 많이 사용하는 기능 중 하나입니다.

3. 데이터 형식 변경 (가장 중요! ⭐)

파워 쿼리에서 발생하는 오류의 80%는 데이터 형식 불일치 때문에 발생합니다. 예를 들어, 숫자처럼 보이지만 실제로는 '텍스트' 형식으로 저장된 데이터는 합계를 구하거나 평균을 낼 수 없습니다.

열 머리글 왼쪽에 있는 아이콘을 통해 현재 데이터 형식을 알 수 있습니다.

• ABC: 텍스트
• 123: 정수
• 1.2: 10진수 (소수)
• 📅: 날짜
• 🕒: 시간
• 📅🕒: 날짜/시간
• $: 통화
• %: 백분율
• True/False: 논리값

방법:
1. 형식을 변경할 열의 머리글을 클릭합니다.
2. 열 머리글 왼쪽의 아이콘을 클릭하고 원하는 형식을 선택합니다.
또는, [홈] 탭이나 [변환] 탭의 [데이터 형식] 드롭다운 메뉴에서 변경할 수도 있습니다.

4. 값 바꾸기 (오타 수정 및 데이터 표준화)

엑셀의 '찾기 및 바꾸기(Ctrl+H)'와 유사한 기능입니다. 특정 값을 다른 값으로 일괄 변경할 수 있습니다. 오타를 수정하거나, 여러 가지로 표현된 값을 하나로 통일할 때(예: '서울', '서울특별시' -> '서울'로 통일) 매우 유용합니다.

• 방법: 변경하고 싶은 열 선택 > 마우스 오른쪽 버튼 클릭 > [값 바꾸기] 또는 [변환] 탭 > [값 바꾸기]
• '찾을 값'에 원래 값을, '바꿀 값'에 새로운 값을 입력하고 '확인'을 누릅니다.

5. 열 분할 (하나를 여러 개로 쪼개기)

하나의 열에 여러 정보가 합쳐져 있을 때, 이를 특정 기준으로 쪼개어 여러 열로 만드는 기능입니다.

예를 들어 '서울시 강남구'라는 주소 데이터가 있을 때, 이를 '시'와 '구'로 나누고 싶을 때 사용합니다.

• 방법: 분할할 열 선택 > [홈] 또는 [변환] 탭 > [열 분할] > 원하는 기준 선택
• 주요 분할 기준:
  • 구분 기호 기준: 쉼표(,), 공백( ), 하이픈(-) 등 특정 문자를 기준으로 분할합니다. 가장 많이 사용됩니다. '각 구분 기호에서', '왼쪽 구분 기호에서' 등 세부 옵션을 선택할 수 있습니다.
  • 문자 수 기준: 앞에서부터 특정 개수의 문자를 기준으로 분할합니다. (예: '12345-ABC'를 5글자 기준으로 분할)
  • 대문자에서 소문자로, 소문자에서 대문자로: 'PowerQuery' 같은 텍스트를 'Power'와 'Query'로 분할할 수 있습니다.

6. 열 병합 (여러 개를 하나로 합치기)

열 분할의 반대 개념입니다. 여러 열의 값을 합쳐서 하나의 새로운 열을 만듭니다.

예를 들어 '연도', '월', '일' 열이 따로 있을 때, 이를 'YYYY-MM-DD' 형식의 날짜 텍스트로 합칠 수 있습니다.

• 방법: 병합할 열들을 순서대로 선택합니다. (Ctrl 키 사용) > [열 추가] 탭 > [열 병합] (보통 새로운 열로 만드므로 '열 추가' 탭에 있습니다. '변환' 탭에도 있긴 합니다.)
• '구분 기호'를 선택하거나 사용자 지정 문자를 입력할 수 있습니다. (예: 하이픈, 공백 등)
• '새 열 이름'을 지정하고 '확인'을 누릅니다.

7. 사용자 지정 열 추가 (나만의 공식으로 열 만들기)

엑셀에서 수식을 입력하여 새로운 열을 만드는 것과 비슷합니다. 파워 쿼리에서는 'M 언어'라는 자체 수식 언어를 사용하지만, 아주 간단한 형태는 엑셀 수식처럼 쉽게 사용할 수 있습니다.

예를 들어 '수량' 열과 '단가' 열을 곱해서 '매출액' 열을 만들고 싶을 때 사용합니다.

• 방법: [열 추가] 탭 > [사용자 지정 열]
• '새 열 이름'을 입력합니다. (예: 매출액)
• '사용자 지정 열 수식' 입력 창에 수식을 작성합니다. 오른쪽에 있는 '사용 가능한 열' 목록에서 열 이름을 더블클릭하면 `[열이름]` 형태로 자동으로 입력됩니다.
  예: `[수량] * [단가]`
• 구문 오류가 없는지 확인하고 '확인'을 누릅니다.

간단한 사칙연산 외에도 `if...then...else` 같은 조건문이나 다양한 내장 함수를 사용할 수 있어 활용도가 무궁무진합니다.

8. 조건부 열 추가 (IF 함수의 파워 쿼리 버전)

'사용자 지정 열'에서 `if`문을 직접 쓰는 것보다 훨씬 직관적이고 쉽게 조건에 따른 열을 추가할 수 있는 기능입니다. 엑셀의 `IF` 함수와 똑같다고 생각하시면 됩니다.

예를 들어 '판매 실적' 열의 값이 100 이상이면 '우수', 그렇지 않으면 '보통'으로 표시하는 '등급' 열을 만들고 싶을 때 사용합니다.

• 방법: [열 추가] 탭 > [조건부 열]
• 대화상자가 나타나면, 마치 문장을 만들 듯이 조건을 설정합니다.
  • 새 열 이름: 등급
  • If [열 이름] (판매 실적) [연산자] (다음보다 크거나 같음) [값] (100)
  • Then 출력 (우수)
  • Else (보통)
• [절 추가] 버튼을 눌러 `IF...ELSEIF...ELSE`처럼 여러 조건을 중첩해서 만들 수도 있습니다.

9. 피벗 / 언피벗 (데이터 구조를 뒤집는 마법)

데이터 분석가들에게는 성배와도 같은 기능입니다. 데이터의 구조를 '넓은 형식(Wide Format)'에서 '좁은 형식(Long Format)'으로, 또는 그 반대로 자유자재로 변경할 수 있습니다.

• 언피벗 (Unpivot): 가장 중요하고 많이 사용됩니다. '넓은 형식'의 데이터를 분석하기 좋은 '좁은 형식'으로 바꿔줍니다.
  예시 (넓은 형식):
  이름 | 1월 | 2월 | 3월
  --- | --- | --- | ---
  김사원 | 100 | 120 | 150
  
  이 데이터를 '언피벗'하면 분석에 용이한 아래와 같은 형태로 바뀝니다.
  결과 (좁은 형식):
  이름 | 월 | 매출
  --- | --- | ---
  김사원 | 1월 | 100
  김사원 | 2월 | 120
  김사원 | 3월 | 150
  
  방법: 기준이 되는 열(예: '이름')을 선택 > [변환] 탭 > [열 언피벗] 드롭다운 > [다른 열 언피벗]
• 피벗 (Pivot): 언피벗의 반대 작업입니다. '좁은 형식'을 '넓은 형식'으로 바꿉니다. 보고서 양식을 만들 때 주로 사용됩니다.
  방법: 항목 이름이 될 열(예: '월')을 선택 > [변환] 탭 > [피벗 열] > '값 열'에 실제 값이 들어있는 열(예: '매출')을 선택하고, '고급 옵션'에서 집계 함수(합계, 평균 등)를 지정합니다.

10. 그룹화 (데이터 요약하기)

엑셀의 피벗 테이블과 유사한 기능이지만, 데이터를 시트로 로드하기 전에 미리 요약(집계)하는 기능입니다. 대용량 데이터를 다룰 때, 원본 데이터를 모두 로드하지 않고 요약된 결과만 가져올 수 있어 매우 효율적입니다.

예를 들어, 수백만 건의 판매 기록에서 '제품 카테고리별 총매출액과 평균 판매 단가'를 계산하고 싶을 때 사용합니다.

• 방법: [홈] 또는 [변환] 탭 > [그룹화]
• '그룹화' 대화상자에서 기준이 될 열을 선택합니다. (예: 제품 카테고리)
• '새 열 이름' (예: 총매출액), '연산' (예: 합계), '열' (예: 매출액)을 설정합니다.
• ['집계 추가'] 버튼을 눌러 여러 개의 요약 값을 동시에 계산할 수 있습니다. (예: 평균 판매 단가 추가)

5. 두 개의 데이터를 하나로! 쿼리 병합 & 추가 🤝

실무에서는 여러 개의 데이터 테이블을 합쳐서 분석해야 하는 경우가 비일비재합니다. 파워 쿼리는 이렇게 분리된 데이터를 합치는 강력한 두 가지 무기를 제공합니다: 추가(Append)와 병합(Merge).

이 두 가지 개념의 차이를 이해하는 것이 매우 중요합니다.

쿼리 추가 (Append): 데이터를 아래로 쌓기 (UNION)

'추가'는 구조가 동일한(또는 유사한) 여러 개의 테이블을 위아래로 이어 붙여 하나의 긴 테이블로 만드는 작업입니다. SQL의 `UNION ALL`과 같은 개념입니다.

언제 사용할까?
✅ 1월 매출, 2월 매출, 3월 매출 데이터를 하나의 연간 매출 데이터로 합칠 때
✅ 서울 지점, 부산 지점, 광주 지점의 고객 목록을 전국 고객 목록으로 합칠 때

방법:
1. [홈] 탭 > [쿼리 추가]
2. '두 개의 테이블' 또는 '세 개 이상의 테이블'을 선택합니다.
3. 대화상자에서 합치고 싶은 쿼리(테이블)들을 선택하고 '확인'을 누릅니다.

쿼리 병합 (Merge): 데이터를 옆으로 붙이기 (VLOOKUP/JOIN)

'병합'은 엑셀의 `VLOOKUP`이나 `INDEX/MATCH`, 데이터베이스의 `JOIN`과 같이, 공통된 열(Key)을 기준으로 두 개의 테이블을 좌우로 합치는 작업입니다.

언제 사용할까?
✅ '주문 내역' 테이블에 있는 '제품 ID'를 기준으로, '제품 정보' 테이블에서 '제품명'과 '카테고리'를 가져와 붙일 때
✅ '인사 정보' 테이블에 있는 '사원 번호'를 기준으로, '급여 정보' 테이블에서 '연봉' 정보를 가져올 때

방법:
1. 기준이 될 첫 번째 쿼리(테이블)를 선택합니다. (예: 주문 내역)
2. [홈] 탭 > [쿼리 병합]
3. '병합' 대화상자가 나타납니다.
    a. 첫 번째 테이블(주문 내역)에서 기준이 될 공통 열(제품 ID)을 클릭합니다.
    b. 두 번째 드롭다운 메뉴에서 합치고 싶은 다른 테이블(제품 정보)을 선택합니다.
    c. 두 번째 테이블에서도 기준이 될 공통 열(제품 ID)을 클릭합니다. (두 열의 이름이 달라도 상관없습니다.)
    d. '조인 종류(Join Kind)'를 선택합니다. (가장 중요!)
    e. '확인'을 누릅니다.
4. 병합 결과로 새로운 열이 추가되는데, 값은 'Table'이라고 표시됩니다. 이 열의 머리글 오른쪽에 있는 확장 버튼(↔)을 클릭합니다.
5. 가져오고 싶은 열(예: 제품명, 카테고리)을 선택하고 '확인'을 누르면, 해당 열들이 옆으로 펼쳐지며 데이터가 합쳐집니다.

조인 종류(Join Kind) 간단히 이해하기

'병합' 시 가장 헷갈리는 부분이 바로 '조인 종류'입니다. 6가지나 있지만, 실무에서는 주로 2~3가지를 사용합니다.

• 왼쪽 외부 (Left Outer): (기본값, 가장 많이 사용) 첫 번째 테이블(왼쪽)의 모든 행을 기준으로, 두 번째 테이블에서 일치하는 값을 가져옵니다. 일치하는 값이 없으면 null로 표시됩니다. `VLOOKUP`과 가장 유사한 방식입니다.
• 오른쪽 외부 (Right Outer): 왼쪽 외부의 반대. 두 번째 테이블(오른쪽)의 모든 행이 기준이 됩니다.
• 내부 (Inner): 두 테이블 모두에 일치하는 데이터가 있는 행만 남깁니다. 교집합을 구하는 것과 같습니다.
• 완전 외부 (Full Outer): 두 테이블의 모든 데이터를 가져옵니다. 합집합과 같습니다.
• 왼쪽 안티 (Left Anti): 첫 번째 테이블에는 있지만 두 번째 테이블에는 없는 데이터만 남깁니다. (예: 주문은 했는데 제품 목록에는 없는 유령 제품 찾기)
• 오른쪽 안티 (Right Anti): 왼쪽 안티의 반대입니다.

6. M 언어? 그거 꼭 배워야 해? 🤔

파워 쿼리 편집기에서 [보기] 탭 > [고급 편집기]를 클릭해보세요. 아마 외계어처럼 보이는 코드들이 가득할 겁니다. 이것이 바로 파워 쿼리의 모든 작업을 관장하는 'M 언어(Power Query Formula Language)'입니다.

let
    원본 = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sales_Sheet = 원본{[Item="Sales",Kind="Sheet"]}[Data],
    #"승격된 헤더" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
    #"변경된 유형" = Table.TransformColumnTypes(#"승격된 헤더",{{"Date", type date}, {"Amount", type number}}),
    #"필터링된 행" = Table.SelectRows(#"변경된 유형", each [Amount] > 100)
in
    #"필터링된 행"
    

"헐, 결국 코딩을 해야 하는 거였어?" 라고 생각하며 창을 닫으려 하셨다면, 잠시만요! ✋

결론부터 말하자면, 초중급 수준에서는 M 언어를 한 줄도 몰라도 파워 쿼리를 사용하는 데 전혀 문제가 없습니다.

왜냐하면 우리가 지금까지 배운 모든 UI(사용자 인터페이스) 기반의 클릭 작업들(열 제거, 필터링, 값 바꾸기 등)이 사실은 M 코드를 자동으로 생성해주는 과정이었기 때문입니다. '적용된 단계'에 기록되는 내용 하나하나가 M 코드 한 줄에 해당합니다.

즉, 파워 쿼리는 코딩을 모르는 사용자도 마우스 클릭만으로 데이터 변환을 할 수 있도록 도와주는 아주 친절한 번역가인 셈이죠.

그럼에도 M 언어를 알면 좋은 점

물론, M 언어를 조금이라도 이해하면 파워 쿼리의 활용 범위가 비약적으로 넓어집니다.

• UI로 불가능한 세밀한 작업: UI에서 제공하지 않는 미세한 옵션을 조정하거나, 더 복잡한 논리를 구현할 수 있습니다.
• 에러 핸들링: 코드를 직접 보며 어디서 오류가 발생했는지 더 쉽게 파악하고 수정할 수 있습니다.
• 재사용 가능한 함수 제작: 반복적으로 사용하는 변환 로직이 있다면, 나만의 함수로 만들어두고 필요할 때마다 불러서 사용할 수 있습니다. (고급 기능)
• 성능 최적화: 비효율적으로 생성된 코드를 수정하여 데이터 처리 속도를 개선할 수 있습니다.

7. 마무리! 데이터 로드 & 새로고침 🚀

자, 길고 긴 데이터 변환 여정이 끝났습니다. 이제 잘 손질된 데이터를 우리의 최종 목적지인 엑셀 시트로 가져올 시간입니다.

파워 쿼리 편집기 왼쪽 상단에 있는 [홈] 탭 > [닫기 및 로드] 버튼을 주목해주세요.

• 닫기 및 로드: 클릭하면, 변환된 데이터를 엑셀의 '새 워크시트'에 '표(Table)' 형태로 바로 가져오고 편집기 창을 닫습니다. 가장 일반적인 방법입니다.
• 닫기 및 다음으로 로드...: 클릭하면, 데이터를 어디에 어떤 형태로 로드할지 선택할 수 있는 대화상자가 나타납니다.

'다음으로 로드' 옵션 완전 정복

'데이터 가져오기' 대화상자에서는 다음과 같은 옵션을 선택할 수 있습니다.

1. 데이터를 표시할 방법 선택:
   • 표 (Table): 가장 기본 옵션. 데이터를 엑셀의 표 서식으로 가져옵니다. 필터링, 정렬이 쉽고 보기에도 깔끔합니다.
   • 피벗 테이블 보고서: 변환된 데이터를 기반으로 즉시 피벗 테이블을 만듭니다. 요약/분석이 최종 목표일 때 가장 효율적인 방법입니다.
   • 피벗 차트: 피벗 테이블과 차트를 동시에 만듭니다.
   • 연결만 만들기: (매우 중요!) 데이터를 시트에 직접 로드하지 않고, 파워 쿼리 연결 정보만 유지합니다. 중간 단계의 쿼리(예: 다른 쿼리를 병합하기 위한 재료 쿼리)나, 최종 결과물이 피벗 테이블인 경우 원본 데이터를 굳이 시트에 뿌릴 필요가 없을 때 사용합니다. 파일 용량을 줄이고 성능을 향상시키는 데 큰 도움이 됩니다.
2. 이 데이터가 추가될 위치 선택:
   • 새 워크시트: 새로운 시트를 만들어 데이터를 로드합니다.
   • 기존 워크시트: 현재 시트의 특정 셀 위치를 지정하여 데이터를 로드합니다.

자동화의 화룡점정: 새로고침! 🔄

파워 쿼리의 진정한 위대함은 바로 '새로고침' 기능에서 드러납니다.

여러분이 지금까지 설정한 모든 과정 - 데이터를 가져오고, 자르고, 붙이고, 계산하고, 합치는 이 모든 단계 - 은 '쿼리'라는 레시피로 저장되었습니다.

이제 원본 데이터가 변경되거나 추가되었을 때, 우리는 이 모든 과정을 다시 반복할 필요가 없습니다.

그저 다음 중 하나의 행동만 하면 됩니다.

• 결과 테이블 안을 마우스 오른쪽 버튼으로 클릭 > [새로 고침]
• [데이터] 탭 > [모두 새로 고침]

이 버튼을 누르는 순간, 파워 쿼리는 저장된 레시피(적용된 단계)에 따라 처음부터 끝까지 모든 과정을 1~2초 만에 다시 실행하고, 최종 결과물을 업데이트합니다. 매달 초, 전월 실적 보고서를 만들던 끔찍한 기억은 이제 안녕입니다. 👋

마치며: 당신은 이제 데이터 마법사 🧙‍♂️

축하합니다! 여러분은 이제 파워 쿼리라는 강력한 마법 지팡이를 손에 넣었습니다. 처음에는 낯설고 복잡해 보일 수 있지만, 오늘 배운 내용들을 바탕으로 작은 데이터부터 하나씩 다루다 보면 어느새 지긋지긋한 '데이터 정제' 작업이 즐거운 '데이터 요리' 시간으로 바뀌어 있을 겁니다.

더 이상 단순 반복 작업에 당신의 소중한 시간과 에너지를 낭비하지 마세요. 파워 쿼리를 통해 확보한 시간으로, 우리는 더 깊이 있는 데이터 분석과 창의적인 문제 해결에 집중할 수 있습니다. 그것이 바로 데이터 시대에 우리가 갖춰야 할 진정한 경쟁력입니다.

물론, 모든 것을 혼자서 해결할 필요는 없습니다. 업무에 파워 쿼리를 적용하다가 막히는 부분이 있다면, **재능넷**과 같은 지식 공유 플랫폼에서 데이터 전문가들의 도움을 받아보세요. 때로는 전문가의 작은 조언 하나가 몇 시간 동안의 고민을 단번에 해결해주기도 하니까요.

이제 엑셀을 열고, [데이터] 탭을 자신 있게 클릭하세요. 당신의 칼퇴를 보장하고, 업무 생산성을 극적으로 높여줄 새로운 세계가 펼쳐질 것입니다.

당신은 더 이상 단순한 엑셀 사용자가 아닙니다. 당신은 데이터를 지배하는 데이터 연금술사입니다.