데이터 정제의 마법사: Get & Transform (파워쿼리)로 외부 CSV/웹 데이터 완벽 정복!

안녕하세요, 데이터 활용에 관심 있는 여러분! 🙋‍♀️ 혹시 엑셀로 데이터를 분석하다가 지저분하고 제각각인 데이터 때문에 고생하신 적은 없으신가요? 🤯 수많은 웹페이지에서 필요한 정보를 일일이 복사해서 붙여넣고, 복잡한 CSV 파일을 매번 수작업으로 정리하느라 밤샘 작업을 하셨다면, 이 글이 여러분의 구세주가 될 것입니다!

오늘 소개해 드릴 주인공은 바로 엑셀의 강력한 기능, Get & Transform (파워쿼리)입니다. 이 마법 같은 도구를 사용하면 외부 CSV 파일이나 웹사이트의 데이터를 손쉽게 가져와 깨끗하게 정제하고, 원하는 형태로 변환할 수 있습니다. 한 번 설정해두면 다음부터는 클릭 몇 번으로 자동으로 데이터가 갱신되는 마법까지 경험할 수 있죠! ✨

1. Get & Transform (파워쿼리)는 무엇인가요? 🤔

Get & Transform (이하 파워쿼리)는 마이크로소프트 엑셀에 내장된(또는 추가 기능으로 설치 가능한) 데이터 연결 및 변환 도구입니다. 데이터 분야에서는 ETL(Extract, Transform, Load) 도구의 일종으로 분류되기도 합니다.

• Extract (추출): 다양한 외부 소스(웹, CSV, 데이터베이스, SharePoint, 다른 엑셀 파일 등)에서 데이터를 가져옵니다.
• Transform (변환): 가져온 데이터를 분석 목적에 맞게 정제하고 가공합니다. 예를 들어, 불필요한 열 제거, 데이터 형식 변경, 값 바꾸기, 열 분할, 여러 테이블 병합 등이 가능합니다.
• Load (로드): 변환이 완료된 데이터를 엑셀 시트나 데이터 모델(파워 피벗)로 불러옵니다.

파워쿼리는 복잡한 코딩 없이 직관적인 사용자 인터페이스를 통해 데이터를 조작할 수 있게 해주며, 모든 변환 과정이 ‘단계’로 기록되어 언제든지 수정하거나 재사용할 수 있다는 강력한 장점을 가지고 있습니다.

2. 왜 Get & Transform (파워쿼리)가 필수적일까요? 🚀

데이터 분석의 첫걸음은 바로 ‘깨끗한 데이터’입니다. 파워쿼리는 이 첫걸음을 훨씬 쉽고 효율적으로 만들어 줍니다.

• 시간 절약 및 자동화 ⏱️: 수동으로 데이터를 정리하는 데 드는 엄청난 시간을 절약할 수 있습니다. 한 번 쿼리를 만들어두면, 원본 데이터가 변경되어도 새로고침 버튼 클릭 한 번으로 모든 정제 과정이 자동으로 반복됩니다.
• 정확성 및 일관성 ✅: 수동 작업 시 발생할 수 있는 오타나 휴먼 에러를 줄여줍니다. 정해진 규칙에 따라 일관된 방식으로 데이터를 처리할 수 있습니다.
• 대용량 데이터 처리 능력 📈: 엑셀의 행 제한(약 100만 행)을 넘는 대용량 데이터도 효율적으로 처리하고, 필요한 부분만 엑셀로 로드할 수 있습니다.
• 다양한 데이터 원본 연결 🔗: CSV, 텍스트, 웹, 데이터베이스, JSON, PDF 등 상상 이상의 다양한 데이터 원본에 연결할 수 있습니다.
• 반복 작업의 효율화 🔄: 매주, 매월 반복되는 데이터 정리 작업을 획기적으로 줄여줍니다.

3. 주요 기능 둘러보기 (쿼리 편집기) 🛠️

파워쿼리의 핵심 작업 공간은 바로 ‘쿼리 편집기’입니다. 여기에서 데이터 변환의 모든 마법이 일어납니다.

• 데이터 원본 연결: “데이터” 탭 → “데이터 가져오기” 에서 원하는 원본을 선택합니다.
• 쿼리 편집기 인터페이스:
  • 리본 메뉴: 다양한 변환 도구들이 카테고리별로 정돈되어 있습니다.
  • 쿼리 설정 창: 현재 적용된 변환 ‘단계’들이 순서대로 기록됩니다. 각 단계를 클릭하여 이전 상태로 돌아가거나, 수정, 삭제할 수 있습니다.
  • 미리보기 창: 데이터가 각 변환 단계별로 어떻게 변하는지 실시간으로 보여줍니다.
• 주요 변환 기능:
  • 열 관리: 열 제거, 열 이름 바꾸기, 열 복제, 열 순서 변경
  • 행 관리: 중복 제거, 빈 행 제거, 오류 제거, 행 필터링
  • 데이터 형식 변환: 텍스트, 숫자, 날짜/시간, 논리 등 데이터 형식을 정확하게 지정
  • 열 분할/병합: 특정 구분 기호를 기준으로 열을 나누거나, 여러 열을 하나로 합치기
  • 값 바꾸기: 특정 값을 다른 값으로 일괄 변경
  • 피벗/피벗 해제: 데이터를 행/열 기준으로 요약하거나, 요약된 데이터를 상세 데이터로 다시 펼치기 (매우 강력한 기능!)
  • 사용자 지정 열 추가: 기존 열을 기반으로 새로운 계산 열 생성
  • 쿼리 병합/추가: 여러 쿼리를 수평으로 합치거나(JOIN), 수직으로 합치기(UNION)

4. 실습 예제 1: 지저분한 CSV 데이터 정제하기 🧹

우리가 온라인 쇼핑몰의 판매 데이터를 분석해야 한다고 가정해 봅시다. 그런데 CSV 파일이 아래와 같이 엉망진창입니다.

원본 CSV 예시 (sales_raw.csv):

"","상품코드_사이즈","상품명","구매일자","판매가격","수량","할인율","배송지","비고"
"1","A100-S","티셔츠","2023-01-01","15,000","2","0.05","서울","빠른 배송 요청"
"2","B201-M","바지","2023-01-02","25000","1","0.1","경기","N/A"
"3","C302-L","모자","2023-01-03","12,500원","3","0","부산","재고 부족"
"4","A100-XL","티셔츠","2023-01-04","15,000","1","0.05","인천",""
"5","D403-S","신발","2023-01-05","","2","0.15","제주","반품 예정"
"6","B201-XXL","바지","2023-01-06","30,000","0","0.1","대구","재고 없음"

목표:

• 첫 번째 빈 열 제거
• ‘상품코드_사이즈’를 ‘상품코드’와 ‘사이즈’로 분할
• ‘판매가격’의 통화 기호 및 쉼표 제거, 숫자 형식으로 변환
• ‘수량’이 0인 행 제거
• ‘비고’ 열의 “N/A”를 빈칸으로 변경
• 불필요한 ‘비고’ 열 제거

단계별 파워쿼리 사용법:

1. 데이터 가져오기:

   • 엑셀에서 “데이터” 탭 → “데이터 가져오기” → “파일에서” → “텍스트/CSV에서”를 클릭합니다.
   • sales_raw.csv 파일을 선택하고 “가져오기”를 누릅니다.
   • 미리보기 창에서 “데이터 변환” 버튼을 클릭하여 쿼리 편집기를 엽니다.

2. 첫 행을 머리글로 사용:

   • 쿼리 편집기에서 기본적으로 첫 행이 머리글로 인식되지 않는다면, “홈” 탭 → “첫 행을 머리글로 사용”을 클릭합니다. ✨

3. 불필요한 첫 번째 열 제거:

   • 미리보기 창에서 첫 번째 빈 열을 마우스 우클릭 → “열 제거”를 클릭합니다. 🗑️

4. ‘상품코드_사이즈’ 열 분할:

   • ‘상품코드_사이즈’ 열을 선택하고 “홈” 탭 → “열 분할” → “구분 기호 기준”을 클릭합니다.
   • 구분 기호로 -를 선택하고, “가장 왼쪽 구분 기호”를 선택한 뒤 “확인”을 누릅니다.
   • 새로 생성된 열의 이름을 각각 ‘상품코드’와 ‘사이즈’로 변경합니다. ✂️

5. ‘판매가격’ 열 정제 및 형식 변환:

   • ‘판매가격’ 열을 선택하고 “변환” 탭 → “값 바꾸기”를 클릭합니다.
   • “찾을 값”에 “원”을 입력하고 “바꿀 값”은 비워둔 채 “확인”합니다.
   • 다시 “찾을 값”에 “,”를 입력하고 “바꿀 값”은 비워둔 채 “확인”합니다.
   • ‘판매가격’ 열 머리글의 데이터 형식 아이콘(abc 또는 123으로 표시됨)을 클릭하여 “정수” 또는 “소수”로 변경합니다. 🔢

6. ‘수량’이 0인 행 제거:

   • ‘수량’ 열 머리글 옆의 필터 아이콘을 클릭하고, “0”을 체크 해제하거나, “숫자 필터” → “보다 큼” → “0”을 입력합니다. 🧹

7. ‘비고’ 열 값 변경 및 제거:

   • ‘비고’ 열을 선택하고 “변환” 탭 → “값 바꾸기”를 클릭합니다.
   • “찾을 값”에 “N/A”를 입력하고 “바꿀 값”은 비워둔 채 “확인”합니다.
   • ‘비고’ 열을 마우스 우클릭 → “열 제거”를 클릭합니다. 🗑️

8. 데이터 로드:

   • 모든 변환이 끝났다면, “홈” 탭 → “닫기 및 로드”를 클릭합니다.
   • 변환된 데이터가 새로운 엑셀 시트에 로드됩니다. 💾

이제 지저분했던 CSV 데이터가 깔끔하게 정리되어 분석 준비가 완료되었습니다! 🎉

5. 실습 예제 2: 웹 데이터 가져와 정제하기 🌐

이번에는 특정 웹사이트의 테이블 데이터를 가져와 정제하는 과정을 살펴봅시다. (예시 웹사이트는 실제와 다를 수 있습니다.)

가져올 웹 페이지 주소 (예시): https://www.example.com/products (가상의 상품 목록 페이지)

목표:

• 웹페이지 내의 상품 목록 테이블 가져오기
• ‘가격’ 열에서 통화 기호와 쉼표 제거 및 숫자 형식으로 변환
• ‘재고’ 열이 0인 상품 제외

단계별 파워쿼리 사용법:

1. 웹 데이터 가져오기:

   • 엑셀에서 “데이터” 탭 → “데이터 가져오기” → “기타 원본에서” → “웹에서”를 클릭합니다.
   • URL 입력 창에 https://www.example.com/products (또는 실제 가져올 웹 주소)를 입력하고 “확인”을 누릅니다.

2. 테이블 선택 (탐색기):

   • “탐색기” 창이 나타나면, 웹페이지에서 파워쿼리가 감지한 테이블 목록이 표시됩니다.
   • 일반적으로 “Table0” 또는 웹페이지 구조에 따라 이름이 지정된 테이블이 있습니다. 미리보기 창에서 원하는 테이블을 클릭하여 내용을 확인합니다.
   • 목록에서 원하는 테이블을 선택하고 “데이터 변환” 버튼을 클릭하여 쿼리 편집기를 엽니다. 🔍

3. ‘가격’ 열 정제 및 형식 변환:

   • ‘가격’ 열을 선택하고 “변환” 탭 → “값 바꾸기”를 클릭합니다.
   • “찾을 값”에 “₩”를 입력하고 “바꿀 값”은 비워둔 채 “확인”합니다. (또는 ‘$’, ‘원’ 등 웹페이지에 있는 통화 기호를 입력)
   • 다시 “찾을 값”에 “,”를 입력하고 “바꿀 값”은 비워둔 채 “확인”합니다.
   • ‘가격’ 열 머리글의 데이터 형식 아이콘을 클릭하여 “소수”로 변경합니다. 🔢

4. ‘재고’ 열이 0인 행 제거:

   • ‘재고’ 열 머리글 옆의 필터 아이콘을 클릭하고, “숫자 필터” → “보다 큼” → “0”을 입력합니다. 📊

5. 데이터 로드:

   • 모든 변환이 끝났다면, “홈” 탭 → “닫기 및 로드”를 클릭합니다.
   • 웹에서 가져와 변환된 상품 목록이 엑셀 시트에 나타납니다. 💾

이처럼 파워쿼리를 사용하면 복잡해 보이는 웹 데이터도 몇 번의 클릭만으로 깔끔하게 가져와 활용할 수 있습니다. 웹 데이터는 구조가 바뀌는 경우가 많으므로, 나중에 쿼리가 깨지면 해당 웹페이지의 HTML 구조를 다시 확인해야 할 수 있습니다.

6. 파워쿼리 고급 팁 ✨

• M 언어 활용: 쿼리 편집기 상단의 “고급 편집기”를 클릭하면 파워쿼리의 백엔드 언어인 “M 언어” 코드를 볼 수 있습니다. 이 언어를 익히면 더욱 복잡하고 정교한 데이터 변환 및 사용자 지정 함수 생성이 가능합니다. 💡
• 매개 변수화: 쿼리에 매개 변수를 설정하여, 매번 URL이나 파일 경로를 수동으로 변경하지 않고도 동적으로 데이터를 가져올 수 있습니다.
• 오류 처리: 데이터 원본에 오류가 포함된 경우, 파워쿼리에서 오류를 무시하거나, 오류가 발생한 행을 제거하는 등의 옵션을 제공합니다.

맺음말 🌈

Get & Transform (파워쿼리)는 단순한 엑셀 기능이 아니라, 데이터를 다루는 모든 이들에게 필수적인 강력한 도구입니다. 복잡한 데이터 정제 작업을 자동화하고, 수많은 외부 데이터를 효율적으로 통합하여 분석의 기반을 닦아줍니다.

이제 더 이상 지저분한 데이터와 씨름하며 밤샘하지 마세요! 파워쿼리의 마법을 익혀 여러분의 데이터 작업 효율을 획기적으로 높이고, 진정한 데이터 분석가로 거듭나세요. 오늘부터 바로 엑셀을 열고 Get & Transform 기능을 탐색해 보세요! 분명 새로운 데이터 세상이 펼쳐질 것입니다. 행복한 데이터 분석 되세요! 😊 D