엑셀 파워 쿼리, 왜 써야 할까요? 데이터 작업의 혁신

혹시 매번 비슷한 데이터를 엑셀로 가져와서 일일이 손으로 정리하고 계신가요? 웹사이트 테이블, CSV 파일, 데이터베이스 등 다양한 곳에서 데이터를 가져와 합치고, 불필요한 부분을 제거하고, 형식을 맞추는 작업, 정말 번거롭죠. 이런 반복적인 작업 때문에 소중한 시간을 낭비하고 있지는 않으신가요?

여기, 여러분의 데이터 작업을 완전히 바꿔줄 엑셀의 숨겨진 보석, 파워 쿼리(Power Query)가 있습니다. 파워 쿼리는 단순히 데이터를 불러오는 것을 넘어, 외부 데이터를 자동으로 수집하고 복잡한 정제 과정을 자동화하는 강력한 도구입니다. 마치 나만의 데이터 처리 비서가 생긴 것처럼 말이죠.

이 글을 통해 여러분은 파워 쿼리가 무엇인지, 왜 필요한지, 그리고 어떻게 활용하여 데이터 작업의 효율성을 극대화할 수 있는지 자세히 배우게 될 것입니다. 이제 지루한 반복 작업에서 벗어나, 데이터 분석에 더 집중할 수 있는 시간을 확보해 보세요!

파워 쿼리, 똑똑한 데이터 비서의 모든 것

파워 쿼리는 마이크로소프트 엑셀과 파워 BI에 내장된 데이터 가져오기 및 변환 도구입니다. 이름에서 알 수 있듯이, 데이터를 ‘쿼리(Query)’, 즉 질의하여 가져오고 원하는 형태로 ‘변환(Transform)’하는 기능을 제공합니다.

1. 파워 쿼리의 핵심 기능: 데이터 가져오기 & 변환

파워 쿼리의 가장 큰 매력은 바로 다양한 데이터 원본에 연결하여 데이터를 가져올 수 있다는 점입니다.

• 파일: 엑셀 파일(.xlsx, .xls), CSV(.csv), 텍스트 파일(.txt), PDF 파일 등

• 데이터베이스: SQL Server, Access, Oracle, MySQL 등

• 웹: 웹사이트 테이블, 웹 페이지 등

• 클라우드 서비스: SharePoint, Dynamics 365, Azure 등

• 기타: 폴더, OData 피드 등

데이터를 가져온 후에는 강력한 변환 기능을 통해 원하는 형태로 만들 수 있습니다.

• 행/열 제거 또는 필터링: 불필요한 행이나 열을 쉽게 삭제하고 특정 조건에 맞는 데이터만 남길 수 있습니다.

• 데이터 형식 변경: 텍스트, 숫자, 날짜 등 데이터 형식을 일관되게 통일할 수 있습니다.

• 열 분할/병합: 하나의 열을 여러 개로 나누거나, 여러 열을 하나로 합칠 수 있습니다.

• 피벗/언피벗: 데이터를 재구성하여 분석에 용이한 형태로 바꿀 수 있습니다.

• 그룹화/집계: 특정 기준으로 데이터를 그룹화하고 합계, 평균 등을 계산할 수 있습니다.

• 열 추가/계산: 기존 데이터를 기반으로 새로운 계산 열을 만들 수 있습니다.

2. 파워 쿼리가 필요한 이유: 효율성 & 정확성

파워 쿼리를 사용하면 다음과 같은 놀라운 변화를 경험할 수 있습니다.

• 시간 절약: 수작업으로 몇 시간씩 걸리던 데이터 정제 작업이 몇 분, 혹은 몇 초 안에 완료됩니다.

• 오류 감소: 반복적인 수작업에서 발생할 수 있는 실수를 원천적으로 차단하여 데이터의 정확성을 높입니다.

• 업무 자동화: 한 번 설정해두면 데이터 원본만 업데이트하면 자동으로 최신 데이터를 불러오고 정제까지 마쳐줍니다.

• 데이터 분석 심화: 복잡하고 지저분한 데이터 때문에 분석을 포기했던 경험이 있다면, 파워 쿼리로 깔끔하게 정리된 데이터를 바탕으로 더 깊이 있는 분석이 가능해집니다.

• 쉬운 사용법: 코딩 지식이 없어도 그래픽 인터페이스(GUI)를 통해 직관적으로 사용할 수 있습니다.

엑셀 파워 쿼리, 시작하기: 단계별 실전 가이드

이제 파워 쿼리의 매력을 충분히 느끼셨다면, 직접 사용해 볼 차례입니다. 엑셀에서 파워 쿼리를 실행하고 데이터를 가져오는 기본적인 과정을 따라 해 보겠습니다.

1. 파워 쿼리 편집기 열기

파워 쿼리 편집기는 데이터를 가져오고 변환하는 모든 작업이 이루어지는 핵심 공간입니다.

1. 엑셀을 실행합니다.

2. [데이터] 탭으로 이동합니다.

3. [데이터 가져오기 및 변환] 그룹에서 [데이터 가져오기]를 클릭합니다.

4. 데이터 원본을 선택합니다. 예를 들어, [파일에서] > [Excel 통합 문서에서]를 선택합니다.

5. 가져올 파일을 선택하고 [가져오기]를 클릭합니다.

6. 파일 안에 여러 시트나 테이블이 있다면, 원하는 것을 선택하고 [데이터 변환]을 클릭합니다. (만약 바로 ‘로드’를 선택하면 정제 과정 없이 엑셀로 가져와집니다.)

이제 파워 쿼리 편집기 창이 나타납니다. 이곳에서 여러분의 데이터 마법이 시작됩니다!

2. 첫 번째 데이터 가져오기: 웹 테이블 예시

가장 흔하게 사용되는 웹사이트 테이블 데이터를 가져오는 예시를 살펴보겠습니다.

1. [데이터] 탭 > [데이터 가져오기] > [웹에서]를 선택합니다.

2. 가져올 웹 페이지의 URL을 입력하고 [확인]을 클릭합니다.

3. 파워 쿼리가 해당 웹 페이지에서 테이블 형식의 데이터를 감지하여 보여줍니다.

4. 가져오고 싶은 테이블을 선택하고 [데이터 변환]을 클릭합니다.

3. 데이터 변환의 기본: 열 제거 및 필터링

이제 파워 쿼리 편집기에서 데이터를 정리해 봅시다.

H3_3-1: 불필요한 열 제거하기

• 열 헤더(제목)를 마우스 오른쪽 버튼으로 클릭합니다.

• [열 제거]를 선택합니다.

• 여러 열을 한 번에 제거하고 싶다면, Ctrl 키를 누른 상태로 제거할 열들의 헤더를 클릭하여 선택한 후, 아무 열 헤더나 마우스 오른쪽 버튼을 클릭하여 [열 제거]를 선택합니다.

H3_3-2: 특정 조건으로 행 필터링하기

• 필터링하고 싶은 열의 헤더 옆에 있는 드롭다운 화살표를 클릭합니다.

• 원하는 조건을 선택합니다. 예를 들어, ‘성별’ 열에서 ‘남성’만 보고 싶다면 [텍스트 필터] > [같음]을 선택하고 ‘남성’을 입력합니다.

• 또는, 특정 값을 체크 해제하여 제외할 수도 있습니다. (예: ‘null’ 값 제외)

4. 데이터 형식 변경하기

데이터의 일관성은 분석의 기본입니다.

• 데이터 형식을 변경할 열의 헤더를 클릭합니다.

• [홈] 탭의 [변환] 그룹에서 [데이터 형식] 드롭다운 메뉴를 클릭합니다.

• 원하는 형식(예: ‘텍스트’, ‘정수’, ‘십진수’, ‘날짜’)을 선택합니다.

• 주의: 숫자가 포함된 텍스트 열을 숫자로 바로 변환하려 할 때 오류가 발생할 수 있습니다. 이 경우, 먼저 숫자 형식이 아닌 문자를 제거하는 단계를 추가해야 할 수 있습니다.

5. 데이터 로드하기

데이터 변환이 완료되었다면, 이제 엑셀 시트로 가져올 차례입니다.

• [홈] 탭에서 [닫기 및 로드] 또는 [닫기 및 다음으로 로드]를 클릭합니다.

• [닫기 및 로드]를 선택하면 현재 워크시트의 새 시트에 데이터가 표 형태로 로드됩니다.

• [닫기 및 다음으로 로드]를 선택하면 데이터를 표로 로드할지, 피벗 테이블로 만들지, 아니면 연결만 만들지 등을 선택할 수 있습니다.

파워 쿼리 고급 활용: 자동화와 복잡한 정제

기본적인 데이터 가져오기와 정제 방법을 익혔다면, 이제 파워 쿼리의 진정한 힘을 경험할 차례입니다.

1. 여러 파일/폴더에서 데이터 자동 가져오기

매일 혹은 매주 업데이트되는 여러 개의 엑셀 파일이 있다고 가정해 봅시다. 파워 쿼리를 사용하면 이 모든 파일을 한 번에 처리할 수 있습니다.

1. [데이터] 탭 > [데이터 가져오기] > [파일에서] > [폴더에서]를 선택합니다.

2. 데이터 파일이 저장된 폴더 경로를 지정하고 [확인]을 클릭합니다.

3. 파워 쿼리가 폴더 내의 모든 파일 목록을 보여줍니다. 여기서 [데이터 변환]을 클릭합니다.

4. 파워 쿼리 편집기에서 [열 추가] 탭 > [사용자 지정 열]을 클릭합니다.

5. 새 열 이름을 ‘Content’라고 하고, 수식에 Excel.Workbook([Content])를 입력합니다. (파일이 엑셀 파일일 경우)

6. 새로 생긴 ‘Content’ 열의 테이블 아이콘을 클릭하여 확장합니다. 이때 ‘원본 열 사용’ 옵션을 선택 해제하여 중복된 파일 이름을 제거합니다.

7. 이제 모든 파일의 데이터가 하나로 합쳐진 것을 볼 수 있습니다. 이후 필요한 열을 선택하고 필터링, 형식 변경 등의 작업을 수행합니다.

이 과정을 한 번 설정해두면, 해당 폴더에 새로운 파일을 추가하거나 기존 파일을 업데이트한 후, [데이터] 탭의 [모두 새로 고침]만 클릭하면 자동으로 최신 데이터를 불러와 정제된 결과까지 얻을 수 있습니다.

2. 중복 데이터 제거 및 표준화

데이터에 중복된 항목이 많거나, 같은 내용을 다른 방식으로 표기한 경우가 있습니다.

• 중복된 행 제거: [홈] 탭 > [행 제거] > [중복 행 제거]를 선택합니다.

• 특정 열 기준으로 중복 제거: 중복을 판단할 기준이 되는 열을 선택한 후, [홈] 탭 > [행 제거] > [선택한 열에서 중복 제거]를 선택합니다.

• 오탈자 및 불규칙한 표기 표준화:

• ‘서울 특별시’, ‘서울시’, ‘Seoul’ 등 다양한 표기를 하나로 통일하고 싶다면, 해당 열을 선택하고 [변환] 탭 > [열 바꾸기]를 사용합니다.

• [홈] 탭 > [변환] 그룹에서 [값 바꾸기] 기능을 사용하여 특정 텍스트를 다른 텍스트로 일괄 변경할 수 있습니다. (예: ‘서울 시’를 ‘서울특별시’로)

3. 조건부 열 추가: 데이터 분류 및 라벨링

특정 조건에 따라 새로운 열에 값을 자동으로 채우고 싶을 때 유용합니다.

1. [열 추가] 탭 > [조건부 열]을 클릭합니다.

2. [새 열 이름]을 지정합니다. (예: ‘등급’)

3. [열 이름]에서 조건을 적용할 열을 선택합니다.

4. [연산자]를 선택하고 [비교 값]을 입력합니다. (예: ‘점수’ 열 > ‘크거나 같음’ > 90)

5. 해당 조건이 참일 경우 [출력]할 값을 입력합니다. (예: ‘A’)

6. [+ 사용자 지정 조건 추가]를 클릭하여 여러 조건을 설정할 수 있습니다. (예: 80점 이상이면 ‘B’, 70점 이상이면 ‘C’ 등)

7. 모든 조건이 해당되지 않을 경우의 기본값도 설정할 수 있습니다.

4. 분할 열과 병합 열: 데이터 구조 재구성

하나의 열에 여러 정보가 섞여 있거나, 여러 열의 정보를 합쳐야 할 때 사용합니다.

• 분할 열:

• 분할할 열을 선택합니다.

• [홈] 탭 > [변환] 그룹 > [열 분할]을 클릭합니다.

• [구분 기호 사용] (예: 쉼표, 하이픈), [고정 길이], [문자 수] 등 원하는 기준으로 분할할 수 있습니다.

• 열 병합:

• 병합할 두 개 이상의 열을 Ctrl 키로 선택합니다.

• [홈] 탭 > [변환] 그룹 > [열 병합]을 클릭합니다.

• [구분 기호]를 선택하고 새 열의 이름을 지정한 후 [확인]을 클릭합니다.

파워 쿼리 활용 시 흔한 실수와 주의사항

파워 쿼리는 강력하지만, 몇 가지 주의사항을 알아두면 더 원활하게 사용할 수 있습니다.

• 기록된 단계 확인: 파워 쿼리는 여러분이 수행한 모든 변환 단계를 ‘적용된 단계’ 창에 기록합니다. 어떤 단계에서 문제가 발생했는지 추적하기 용이하지만, 너무 많은 단계를 쌓으면 복잡해질 수 있습니다.

• 데이터 형식 오류: 숫자가 포함된 텍스트나, 날짜 형식이 불규칙한 경우 데이터 형식 변경 시 오류가 발생할 수 있습니다. 오류가 발생한 셀은 보통 ‘Error’로 표시되는데, 해당 셀을 마우스 오른쪽 버튼으로 클릭하여 오류를 확인하고 해결하거나, 오류가 있는 행을 제거하는 방법을 사용해야 합니다.

• 정렬 순서: 데이터를 가져오거나 필터링할 때, 원본 데이터의 정렬 순서가 유지되지 않을 수 있습니다. 필요하다면 파워 쿼리 내에서 다시 정렬하는 단계를 추가해야 합니다.

• 새로 고침: 파워 쿼리로 가져온 데이터는 [데이터] 탭의 [모두 새로 고침]을 눌러야만 최신 상태로 업데이트됩니다. 엑셀을 닫았다 다시 열 때 자동으로 새로 고침되도록 설정할 수도 있습니다.

• 원본 데이터 변경: 파워 쿼리는 원본 데이터를 직접 수정하지 않습니다. 가져온 데이터를 변환하는 과정이므로, 원본 데이터는 그대로 유지됩니다.

결론: 파워 쿼리로 데이터 작업의 미래를 열다

엑셀 파워 쿼리는 단순한 데이터 가져오기 도구를 넘어, 복잡한 데이터 정제 과정을 자동화하고 업무 효율성을 극대화하는 강력한 솔루션입니다. 오늘 배운 내용을 바탕으로 여러분의 데이터 작업 방식을 한 단계 업그레이드해 보세요.

• 지금 바로 시작하세요: 엑셀에서 [데이터] 탭으로 이동하여 파워 쿼리 기능을 직접 실행해 보세요. 간단한 웹 테이블이나 CSV 파일부터 시작하여 감을 익히는 것이 중요합니다.

• 반복 작업을 자동화하세요: 매일, 매주 반복하는 데이터 수집 및 정제 작업이 있다면 파워 쿼리로 자동화하는 방법을 고민해 보세요.

• 꾸준히 배우고 활용하세요: 파워 쿼리는 더 많은 기능과 고급 기법을 제공합니다. 필요에 따라 추가 학습을 통해 데이터 분석 역량을 강화할 수 있습니다.

파워 쿼리를 마스터하는 것은 여러분의 데이터 활용 능력을 비약적으로 향상시키고, 더 가치 있는 업무에 집중할 수 있는 시간을 확보해 줄 것입니다.