파워쿼리(Power Query)로 데이터의 숨겨진 보물을 찾아라! CSV & 웹 데이터 정제 완벽 가이드

안녕하세요, 데이터와 씨름하는 모든 분들! 📊✨

오늘날 데이터는 비즈니스의 핵심이자 성장의 동력입니다. 하지만 현실의 데이터는 깔끔하게 정돈된 경우가 거의 없죠. 😅 여기저기 흩어져 있거나, 뒤죽박죽이거나, 심지어는 웹사이트에 숨어 있기도 합니다. 수많은 시간을 수동으로 데이터를 복사하고 붙여넣고, 오타를 수정하는 데 소비하고 계신가요? 더 이상 그럴 필요 없습니다!

이 글에서는 엑셀과 파워 BI의 강력한 기능인 Get & Transform (파워쿼리, Power Query)을 활용하여 외부 CSV 파일과 웹사이트 데이터를 효과적으로 정제하고 분석 가능한 형태로 만드는 방법을 상세히 알려드리겠습니다. 데이터 정제의 ‘마법 지팡이’ 파워쿼리와 함께 데이터 전문가로 거듭나세요! 🚀

1. 파워쿼리(Power Query)란 무엇인가요? 🧐

파워쿼리는 마이크로소프트 엑셀(Excel)과 파워 BI(Power BI)에 내장된 ETL(Extract, Transform, Load) 도구입니다.

• Extract (추출): 다양한 원본(CSV, Excel, Database, Web, Cloud Service 등)에서 데이터를 가져옵니다.
• Transform (변환): 가져온 데이터를 분석 목적에 맞게 정제하고 변형합니다. (예: 데이터 형식 변경, 불필요한 열 제거, 값 바꾸기, 열 분할, 여러 테이블 병합 등)
• Load (로드): 변환된 데이터를 엑셀 시트나 파워 BI 데이터 모델로 로드합니다.

가장 큰 장점은 한 번 설정해두면 나중에 원본 데이터가 변경될 때 새로고침(Refresh) 한 번으로 모든 정제 과정을 자동으로 반복할 수 있다는 것입니다. 즉, 반복적인 데이터 수작업을 자동화하여 시간과 노력을 획기적으로 절감할 수 있습니다! 🕰️➡️✨

2. 파워쿼리의 핵심 기능 및 워크플로우 🛠️

파워쿼리의 작업 흐름은 크게 다음과 같습니다.

1. 데이터 가져오기 (Get Data):

   • 엑셀 데이터 탭의 데이터 가져오기 및 변환 그룹에서 시작합니다.
   • 데이터 가져오기(Get Data) 버튼을 클릭하여 원하는 데이터 원본을 선택합니다. (예: 텍스트/CSV에서, 웹에서)

2. 쿼리 편집기 (Query Editor):

   • 데이터를 가져오면 ‘쿼리 편집기’ 창이 열립니다. 이곳에서 모든 데이터 변환 작업이 이루어집니다.
   • 왼쪽에는 쿼리 목록, 중앙에는 데이터 미리보기, 오른쪽에는 쿼리 설정(Query Settings) 패널이 있습니다.
   • 적용된 단계(Applied Steps)는 현재까지 데이터에 적용된 모든 변환 작업의 기록입니다. 이 기록을 통해 언제든지 특정 단계로 돌아가 수정하거나 삭제할 수 있습니다. 🔄

3. 데이터 변환 (Transform Data):

   • 홈(Home), 변환(Transform), 열 추가(Add Column) 탭에 있는 다양한 기능을 사용하여 데이터를 정제합니다.
   • 주요 작업:
     • 데이터 형식 변경 (Change Data Type): 텍스트를 숫자로, 날짜로 등. (예: Home 탭 -> Data Type 드롭다운)
     • 열 제거 (Remove Columns): 불필요한 열을 삭제합니다. (예: 열 선택 후 Home 탭 -> Remove Columns)
     • 행 제거/보존 (Remove Rows/Keep Rows): 상위/하위 행, 빈 행, 중복 행 등을 제거하거나 특정 조건의 행만 남깁니다. (예: Home 탭 -> Remove Rows)
     • 값 바꾸기 (Replace Values): 특정 값을 다른 값으로 대체합니다. (예: 열 선택 후 Home 탭 -> Replace Values)
     • 열 분할 (Split Column): 구분 기호, 문자 수 등을 기준으로 열을 나눕니다. (예: 열 선택 후 Home 탭 -> Split Column)
     • 쿼리 병합/추가 (Merge Queries/Append Queries): 여러 테이블을 조인(Join)하거나 위아래로 쌓습니다. (예: Home 탭 -> Merge Queries 또는 Append Queries)
     • 행/열 전치 (Transpose): 행과 열의 위치를 바꿉니다. (예: Transform 탭 -> Transpose)

4. 데이터 로드 (Load Data):

   • 모든 변환이 완료되면 홈(Home) 탭에서 닫기 및 로드(Close & Load)를 클릭하여 데이터를 엑셀 워크시트나 엑셀 데이터 모델로 가져옵니다. 📥

3. 예시 1: 외부 CSV 데이터 정제하기 🧹

가장 흔하게 접하는 데이터 원본인 CSV 파일 정제를 예시로 들어보겠습니다. 당신이 매달 여러 부서에서 취합하는 CSV 형식의 영업 보고서를 정리해야 한다고 가정해봅시다. 이 파일들은 일정한 양식이 없어 매번 수동으로 수정해야 했습니다.

시나리오: monthly_sales_raw.csv 파일은 다음과 같은 문제점을 가지고 있습니다.

• 첫 행에 불필요한 타이틀이 있고, 실제 헤더는 두 번째 행에 있습니다.
• ‘판매일자’ 열이 텍스트 형식으로 되어 있어 날짜 계산이 어렵습니다.
• ‘제품 코드’와 ‘제품명’이 하나의 열에 합쳐져 있습니다.
• ‘수량’ 열에 음수 값이 포함되어 있는데, 이는 오류 데이터입니다.
• 불필요한 ‘비고’ 열이 있습니다.

단계별 정제 과정:

1. CSV 파일 가져오기:

   • 엑셀을 열고 데이터 탭 -> 데이터 가져오기 및 변환 그룹 -> 텍스트/CSV에서를 클릭합니다.
   • monthly_sales_raw.csv 파일을 선택하고 가져오기(Import)를 클릭합니다.
   • 미리보기 창에서 인코딩을 UTF-8 등으로 조정하고 데이터 변환(Transform Data)을 클릭하여 쿼리 편집기를 엽니다.

   (예시 CSV 내용)
   "월간 판매 보고서 - 2023년 10월"
   "판매일자","제품","판매수량","단가","비고"
   "2023-10-01","P001-노트북","10","1200000","할인 적용"
   "2023-10-01","P002-마우스","-2","20000","데이터 오류"
   "2023-10-02","P003-키보드","5","75000",""
   ...

2. 불필요한 첫 행 제거:

   • 쿼리 편집기에서 홈(Home) 탭 -> 행 제거(Remove Rows) 그룹 -> 상위 행 제거(Remove Top Rows)를 클릭합니다.
   • 제거할 행 수에 1을 입력하고 확인을 클릭합니다. (적용된 단계에 ‘상위 행 제거됨’이 추가됩니다.)

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

   • 홈(Home) 탭 -> 첫 행을 머리글로 사용(Use First Row as Headers)을 클릭합니다. (적용된 단계에 ‘승격된 머리글’이 추가됩니다.)

4. 데이터 형식 변경:

   • 판매일자 열을 선택하고, 열 이름 옆의 아이콘(현재 ‘ABC’ 또는 ‘123’으로 표시될 수 있음)을 클릭하여 날짜(Date)로 변경합니다.
   • 판매수량, 단가 열을 선택하고 정수(Whole Number) 또는 십진수(Decimal Number)로 변경합니다. (Ctrl 키를 눌러 다중 선택 가능)

5. ‘비고’ 열 제거:

   • 비고 열을 선택하고 홈(Home) 탭 -> 열 제거(Remove Columns)를 클릭합니다.

6. ‘판매수량’ 열 필터링 (음수 제거):

   • 판매수량 열의 필터 아이콘(▼)을 클릭하고 숫자 필터(Number Filters) -> 보다 큼(Greater Than)을 선택합니다.
   • 1을 입력하여 0보다 큰 값만 남깁니다.

7. ‘제품’ 열 분할:

   • 제품 열을 선택하고 홈(Home) 탭 -> 열 분할(Split Column) -> 구분 기호 기준(By Delimiter)을 클릭합니다.
   • 구분 기호 선택 또는 입력에 -(하이픈)을 입력하고 가장 왼쪽 구분 기호(Left-most delimiter)를 선택합니다. 확인을 클릭합니다.
   • 분할된 제품.1 열의 이름을 제품 코드로, 제품.2 열의 이름을 제품명으로 변경합니다. (열 머리글을 더블 클릭하여 편집)

8. 로드하기:

   • 모든 정제 과정이 완료되었으면 홈(Home) 탭 -> 닫기 및 로드(Close & Load)를 클릭합니다.
   • 이제 정제된 데이터가 엑셀 시트의 새 테이블로 나타납니다! ✨

4. 예시 2: 웹 데이터 가져오기 및 정제하기 🌐

웹사이트에 공개된 테이블 형식의 데이터를 가져와 정제하는 것은 파워쿼리의 강력한 기능 중 하나입니다. 예를 들어, 특정 국가의 인구 통계나 주식 시장 데이터를 가져와 분석하고 싶을 때 유용합니다.

시나리오: 위키피디아에서 제공하는 ‘대한민국의 인구 통계’ 페이지에서 테이블 데이터를 가져와 정리하고 싶습니다. (예시 URL: https://ko.wikipedia.org/wiki/%EB%8C%80%ED%95%9C%EB%AF%BC%EA%B5%AD%EC%9D%98_%EC%9D%B8%EA%B5%AC)

단계별 정제 과정:

1. 웹 데이터 가져오기:

   • 엑셀 데이터 탭 -> 데이터 가져오기 및 변환 그룹 -> 데이터 가져오기(Get Data) -> 기타 원본(From Other Sources) -> 웹에서(From Web)를 클릭합니다.
   • 위키피디아 URL을 붙여넣고 확인을 클릭합니다.

2. 데이터 탐색 및 선택 (Navigator):

   • 탐색기(Navigator) 창이 나타나면, 웹페이지에 있는 테이블 목록이 보입니다.
   • 원하는 테이블(대한민국 인구통계)을 선택하고 오른쪽에 미리보기가 나타나는지 확인한 뒤 데이터 변환(Transform Data)을 클릭하여 쿼리 편집기를 엽니다.

3. 불필요한 열 제거 및 이름 변경:

   • 비고 등 분석에 필요 없는 열을 선택하고 열 제거(Remove Columns)를 클릭합니다.
   • 열 머리글을 더블클릭하여 연도, 총인구, 남자인구, 여자인구 등으로 명확하게 변경합니다.

4. 데이터 형식 변경 및 오류 처리:

   • 총인구, 남자인구, 여자인구 열은 텍스트(Text) 형식일 가능성이 높습니다. 이들을 정수(Whole Number)로 변경해야 합니다.
   • 변경 시 #Error가 발생하는 경우가 있는데, 이는 원본 데이터에 숫자 외의 문자(예: *, [, ])가 포함되어 있기 때문입니다.
   • 오류가 발생한 셀을 선택하고 마우스 오른쪽 버튼을 눌러 오류 제거(Remove Errors)를 선택하거나, 값 바꾸기(Replace Values)를 사용하여 불필요한 문자를 제거한 후 다시 데이터 형식을 변경합니다.
     • 팁: 값 바꾸기를 사용하기 전에 추출 기능을 통해 특정 패턴의 문자를 제거하는 방법도 유용합니다. (예: Transform 탭 -> Extract -> Text After Delimiter 등을 활용)

5. 텍스트 다듬기 (Trim):

   • 간혹 텍스트 열에 불필요한 공백이 포함되어 있을 수 있습니다. 해당 열을 선택하고 변환(Transform) 탭 -> 텍스트 열(Text Column) 그룹 -> 다듬기(Trim)를 클릭하여 공백을 제거합니다.

6. 로드하기:

   • 모든 정제 과정이 완료되었으면 홈(Home) 탭 -> 닫기 및 로드(Close & Load)를 클릭합니다.
   • 이제 깔끔하게 정돈된 인구 통계 데이터가 엑셀에 로드됩니다! 📈

5. 파워쿼리 고급 팁 & 활용 💡

• M 언어: 쿼리 편집기 상단의 수식 입력줄을 보면 let으로 시작하는 코드가 보일 겁니다. 이것이 파워쿼리의 고유한 언어인 M 언어(M language)입니다. 직접 M 언어를 작성하거나 기존 단계를 수정하여 더욱 복잡하고 정교한 데이터 변환을 수행할 수 있습니다. (초보자에게는 필수는 아니지만, 파워쿼리의 이해도를 높이는 데 도움이 됩니다.)
• 쿼리 병합(Merge Queries) 및 추가(Append Queries): 여러 개의 테이블을 가로(JOIN) 또는 세로(UNION)로 연결하여 하나의 통합된 데이터셋을 만들 수 있습니다. 서로 다른 원본의 데이터를 결합할 때 매우 유용합니다.
• 피벗(Pivot) 및 피벗 해제(Unpivot): 데이터 구조를 변경하는 강력한 기능입니다.
  • 피벗 해제(Unpivot): 여러 열에 분산된 동일한 종류의 데이터를 ‘속성(Attribute)’과 ‘값(Value)’ 두 개의 열로 통합합니다. (예: 월별 판매 데이터를 한 열로 모으는 등)
  • 피벗(Pivot): 특정 열의 고유 값을 새로운 열 머리글로 만들고, 다른 열의 값을 그 아래에 집계합니다.
• 사용자 지정 함수(Custom Function): 반복적인 변환 작업을 함수로 만들어 재활용할 수 있습니다.
• 매개 변수(Parameters): 쿼리에 변수를 주어 동적으로 데이터 원본(예: 파일 경로, 웹 URL의 특정 부분)을 변경할 수 있습니다.

6. 왜 파워쿼리를 마스터해야 할까요? 💪

파워쿼리는 단순히 데이터를 가져오는 도구를 넘어, 여러분의 데이터 작업 방식에 혁명적인 변화를 가져다줄 수 있습니다.

• 시간 절약: 반복적인 수작업을 자동화하여 업무 효율을 극대화합니다.
• 데이터 일관성: 수동 작업에서 발생할 수 있는 오류를 줄이고 데이터의 정확성을 높입니다.
• 다양한 데이터 원본 처리: 텍스트 파일, 웹, 데이터베이스, 클라우드 서비스 등 수많은 데이터 원본을 유연하게 다룰 수 있습니다.
• 분석 역량 강화: 데이터를 정리하는 데 썼던 시간을 실제 데이터 분석과 인사이트 도출에 집중할 수 있게 해줍니다.
• 파워 BI의 기초: 파워쿼리는 파워 BI의 핵심 구성 요소입니다. 파워쿼리에 능숙해지면 파워 BI를 활용한 대시보드 및 보고서 작성 능력도 크게 향상됩니다.

결론 🏁

Get & Transform (파워쿼리)는 지저분한 데이터를 깨끗하게 정제하고, 반복적인 작업을 자동화하여 여러분의 데이터 라이프를 더욱 스마트하게 만들어 줄 강력한 도구입니다. CSV 파일부터 웹 데이터에 이르기까지, 이제 어떤 형태의 데이터든 자신감을 가지고 다룰 수 있을 것입니다.

오늘부터 파워쿼리를 통해 데이터 정제의 고통에서 벗어나, 데이터의 숨겨진 가치를 찾아내는 여정을 시작해보세요! 🚀 당신의 데이터 분석 능력이 한 단계 더 발전할 것입니다. 궁금한 점이 있다면 언제든 댓글로 질문해주세요! 감사합니다. 😊 G