파워 쿼리: 전문가처럼 데이터 가져오기 및 변환하기

매주 CSV 파일을 다운로드하고, 빈 행을 삭제하고, 날짜 서식을 지정하고, 분석을 위한 데이터 준비를 위해 복잡한 중첩 수식을 작성하는 데 몇 시간씩 소비하고 있다면 필요 이상으로 힘들게 일하고 있는 것입니다. Microsoft Excel에 내장된 가장 강력한 단일 데이터 자동화 도구인 파워 쿼리(Power Query)를 소개합니다.

흔히 "데이터 가져오기 및 변환"으로 불리는 파워 쿼리를 사용하면 거의 모든 데이터 소스에 연결하여 정보를 정리하고 형태를 변환한 다음 스프레드시트로 로드할 수 있습니다. 가장 큰 장점은 작업 단계를 기록한다는 것입니다. 다음에 새로운 데이터를 받을 때 수동 작업을 반복할 필요 없이 새로 고침만 클릭하면 됩니다.

이 종합적인 가이드에서는 파워 쿼리가 무엇인지, 인터페이스를 탐색하는 방법을 살펴보고, 지저분한 데이터 세트를 분석 준비가 완료된 깔끔한 정보로 변환하는 실용적인 예제를 단계별로 알아보겠습니다.

파워 쿼리란 무엇인가요?

파워 쿼리는 데이터 연결 및 준비 엔진입니다. 데이터베이스 관리 분야에서는 이 과정을 추출(Extract), 변환(Transform), 로드(Load)의 약자인 ETL이라고 부릅니다.

• 추출: 엑셀 통합 문서, CSV 및 텍스트 파일, 웹 페이지, SQL 데이터베이스, 심지어 클라우드 서비스 등 다양한 소스에서 데이터를 가져옵니다.
• 변환: 데이터를 정리하고 형태를 변환합니다. 여기에는 행 필터링, 데이터 형식 변경, 텍스트 분할, 테이블 병합, 계산된 열 만들기 등이 포함됩니다.
• 로드: 다듬어진 데이터를 엑셀 표(Table), 피벗 테이블 또는 파워 피벗 데이터 모델로 직접 출력합니다.

과거에는 엑셀 사용자들이 이러한 작업을 처리하기 위해 TRIM, PROPER, SUBSTITUTE, VLOOKUP과 같은 함수를 조합하고 수동으로 복사 및 붙여넣기에 의존해야 했습니다. 파워 쿼리는 이렇게 지루한 워크플로우를 시각적이고 사용자 친화적인 인터페이스로 대체합니다.

파워 쿼리를 배워야 하는 이유

새로운 엑셀 도구를 배우는 것에 대해 아직 망설이고 있다면, 파워 쿼리를 마스터하는 것이 생산성을 획기적으로 높여주는 이유는 다음과 같습니다.

• 코딩 불필요: 파워 쿼리는 "M"이라는 수식 언어로 실행되지만, 리본 메뉴 버튼과 마우스 오른쪽 버튼 클릭 메뉴만 사용하여 데이터 변환 작업의 95%를 수행할 수 있습니다.
• VBA 없는 자동화: 반복적인 작업을 자동화하기 위해 매크로 작성법을 알 필요가 없습니다. 파워 쿼리는 "적용된 단계"를 기억하고 새로운 데이터에 자동으로 적용합니다.
• 대용량 데이터 처리: 엑셀의 물리적 한계는 1,048,576행입니다. 하지만 파워 쿼리는 수백만 행의 데이터 세트에 연결하고, 백그라운드에서 데이터를 집계하여 요약된 결과만 스프레드시트에 로드할 수 있습니다.
• 비파괴적 편집: 파워 쿼리는 원본 데이터 소스를 절대 변경하지 않습니다. 연결을 생성하고 데이터를 읽어 들여 깔끔하게 정리된 복사본을 출력합니다.

시작하기: 파워 쿼 편집기

파워 쿼리에 접근하려면 빈 엑셀 통합 문서를 열고 리본의 데이터 탭으로 이동합니다. 가장 왼쪽에 있는 데이터 가져오기 및 변환 그룹을 찾으세요.

여기서 데이터 가져오기를 클릭하면 사용 가능한 데이터 소스의 드롭다운 메뉴를 볼 수 있습니다. 파일을 선택하고 "데이터 변환"을 클릭하면 엑셀이 새 창에서 파워 쿼리 편집기를 엽니다. 이 인터페이스는 4개의 주요 영역으로 구성됩니다.

1. 리본 메뉴: 상단에 위치하며 홈, 변환, 열 추가, 보기와 같은 탭이 포함되어 있습니다. 모든 도구가 모여있는 곳입니다.
2. 쿼리 창: 왼쪽에 위치하며, 통합 문서에서 활성화된 모든 데이터 연결 목록을 보여줍니다.
3. 데이터 미리 보기: 중앙의 격자로, 현재 선택된 단계에서 데이터가 어떻게 보이는지 실시간 미리 보기를 제공합니다.
4. 쿼리 설정 창 (적용된 단계): 오른쪽에 위치하며 편집기에서 가장 중요한 부분입니다. 무언가를 변경할 때마다 파워 쿼리는 이를 순차적인 단계로 이곳에 기록합니다. 언제든지 이 단계를 삭제하거나, 순서를 바꾸거나, 편집할 수 있습니다.

단계별 워크스루: 지저분한 판매 데이터 정리하기

실제 사용 사례를 살펴보겠습니다. 회사의 CRM에서 주간 판매 보고서를 내보낸다고 가정해 보세요. 원본 추출 데이터는 불필요한 머리글, 결합된 텍스트 문자열, 일관성 없는 서식이 포함되어 지저분한 상태입니다.

다음은 지저분한 원본 데이터의 예시입니다.

시스템 내보내기: 3분기 판매 보고서	열2	열3
생성일: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

기존 수식을 사용했다면 LEFT, RIGHT, FIND, VALUE 함수를 사용해 담당자 이름을 추출하고 숫자를 수정해야 했을 것입니다. 대신 파워 쿼리를 사용해 봅시다.

1단계: 데이터 가져오기

지저분한 데이터를 CSV 또는 엑셀 파일로 저장합니다. 새 엑셀 통합 문서를 열고 데이터 > 데이터 가져오기 > 파일에서로 이동하여 파일을 선택합니다. 미리 보기 창이 나타나면 데이터 변환을 클릭합니다. 파워 쿼리 편집기가 열릴 것입니다.

2단계: 상위 행 제거 및 머리글 승격

데이터의 처음 두 행은 실제 데이터 레코드가 아닌 시스템 내보내기 메타데이터입니다. 이들을 제거해야 합니다.

• 홈 탭에서 행 제거 > 상위 행 제거를 클릭합니다. "2"를 입력하고 확인을 클릭합니다.
• 이제 실제 열 머리글이 1행에 위치해 있습니다. 홈 탭에서 첫 행을 머리글로 사용 버튼을 클릭합니다. 즉시 "Rep_ID_Name", "Order_Date", "Revenue"가 공식 열 제목이 됩니다.

3단계: 담당자 열 분할

"Rep_ID_Name" 열에는 ID 번호와 직원 이름이 하이픈(-)으로 구분되어 함께 포함되어 있습니다.

• "Rep_ID_Name" 열 머리글을 마우스 오른쪽 버튼으로 클릭합니다.
• 열 분할 > 기호로를 선택합니다.
• "사용자 지정"을 선택하고 하이픈(-)을 입력합니다. 확인을 클릭합니다.
• 파워 쿼리가 데이터를 두 개의 열로 분할합니다. 이제 새 열 머리글을 두 번 클릭하여 각각 "Rep_ID"와 "Rep_Name"으로 이름을 바꿀 수 있습니다.

Bob의 이름에 있는 밑줄(Bob_Jones)을 지우려면 Rep_Name 열을 마우스 오른쪽 버튼으로 클릭하고 값 바꾸기를 선택한 후, "찾을 값" 입력란에 밑줄(_)을 입력하고 "바꿀 값"은 비워 두거나 공백을 추가합니다. 확인을 클릭합니다.

4단계: 데이터 형식 표준화

날짜와 수익이 완전히 다른 서식으로 되어 있는 것을 확인하셨나요? 파워 쿼리를 사용하면 이를 쉽게 표준화할 수 있습니다.

• "Order_Date" 열 머리글 왼쪽의 작은 아이콘("ABC" 또는 "임의"로 표시됨)을 클릭합니다. 이를 날짜로 변경합니다. 파워 쿼리가 자동으로 각기 다른 날짜 형식을 통합된 표준 날짜로 변환합니다.
• "Revenue" 옆의 아이콘을 클릭하고 통화(또는 10진수)로 변경합니다. 달러 기호가 제거되고 모든 것이 깔끔하게 서식화됩니다.

5단계: 조건부 열 추가

1,000달러가 넘는 매출을 "High Value(고가치)"로 분류하고 싶다고 가정해 봅시다. 엑셀에서 =IF(C2>=1000, "High Value", "Standard")와 같은 복잡한 IF 함수를 작성하는 대신, 파워 쿼리 UI를 사용할 수 있습니다.

열 추가 탭으로 이동하여 조건부 열을 클릭합니다. [Revenue]가 1000보다 크거나 같으면 "High Value"를 출력하고, 그렇지 않으면 "Standard"를 출력하도록 규칙을 설정합니다. 그러면 파워 쿼리는 보이지 않는 곳에서 이 단계를 위해 다음과 같은 M 코드를 생성합니다.

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

쿼리 병합: VLOOKUP의 현대적 대안

데이터 분석에서 가장 일반적인 작업 중 하나는 테이블을 결합하는 것입니다. 각 영업 담당자의 지역이 포함된 별도의 테이블이 있다면, 데이터를 가져오기 위해 보통 당사의 VLOOKUP 완벽 가이드를 참고하실 것입니다.

하지만 수천 개의 VLOOKUP 또는 INDEX 및 MATCH 수식을 실행하면 통합 문서가 눈에 띄게 느려질 수 있습니다. 파워 쿼리에서는 쿼리 병합 기능을 사용합니다.

간단히 두 테이블을 파워 쿼리로 가져온 다음 메인 판매 테이블을 선택하고 홈 탭에서 쿼리 병합을 클릭합니다. 두 번째 테이블(지역 테이블)을 선택하고, 두 테이블에서 일치하는 열(예: "Rep_ID")을 클릭한 뒤 확인을 누르세요. 데이터가 10행이든 1,000만 행이든 상관없이 파워 쿼리가 초고속 VLOOKUP과 동일한 작업을 단 몇 초 만에 수행합니다.

데이터 피벗 해제: 파워 쿼리의 비밀 무기

종종 피벗 형식과 유사한 구조로 이미 그룹화된 데이터를 받을 때가 있습니다(예: 열 방향으로 나열된 1월, 2월, 3월, 4월). 이는 사람이 읽기에는 쉽지만 차트나 피벗 테이블을 만드는 데는 최악의 형태입니다.

식별자 열(예: 담당자 이름)을 선택하고 머리글을 마우스 오른쪽 버튼으로 클릭한 후 다른 열 피벗 해제를 선택합니다. 파워 쿼리가 넓은 교차 테이블 형태의 데이터를 새로운 "특성"(월) 및 "값"(매출) 열이 있는 평면적인 테이블 형식으로 즉시 변환합니다. 일반적인 엑셀 수식으로는 이를 수행하는 것이 거의 불가능하므로, 피벗 해제는 파워 쿼리에서 가장 인기 있는 기능 중 하나입니다.

변환된 데이터 로드하기

데이터가 완벽하게 정리되었다면, 이제 다시 엑셀로 보낼 차례입니다.

홈 탭에서 닫기 및 로드를 클릭합니다. 기본적으로 이 기능은 변환된 데이터를 새 워크시트의 새로운 녹색 엑셀 표(Table)로 로드합니다. 데이터를 분석 단계로 바로 보내고 싶다면, 드롭다운 화살표를 클릭하고 닫기 및 다음으로 로드...를 선택한 뒤 피벗 테이블 보고서를 선택할 수 있습니다. 이러한 요약 보고서를 작성하는 방법이 기억나지 않는다면 초보자를 위한 피벗 테이블 만들기 튜토리얼을 확인해 보세요.

"새로 고침" 버튼의 마법

파워 쿼리의 진정한 위력은 다음 주에 새로운 원본 판매 데이터를 받을 때 확연히 드러납니다. 위의 단계를 반복하지 마세요!

단순히 새 CSV 파일을 기존 파일 위에 덮어쓰기만 하면 됩니다(정확히 같은 파일명과 폴더 위치를 유지해야 합니다). 그런 다음 엑셀 통합 문서를 열고 깔끔하게 정리된 데이터 테이블 안의 아무 곳이나 마우스 오른쪽 버튼으로 클릭한 후 새로 고침을 누르세요.

파워 쿼리가 해당 파일에 접근하여 행 제거, 머리글 승격, 열 분할, 텍스트 바꾸기, 조건 확인, 테이블 병합 등 모든 단계를 다시 적용하고 최종 출력물을 순식간에 업데이트합니다. 이는 엑셀 자동화 워크플로우의 핵심 요소입니다.

AI로 데이터 작업 여정 간소화하기

파워 쿼리가 구조적인 변환을 훌륭하게 처리하지만, 때로는 고급 엑셀 수식이나 사용자 지정 M 코드가 필요한 특정 조건 논리 또는 복잡한 텍스트 구문 분석이 필요할 수 있습니다. 정답을 찾기 위해 포럼을 뒤지는 대신 인공지능을 활용해 보세요.

완벽한 사용자 지정 열 계산식을 작성하는 데 어려움을 겪고 있다면 GPTExcel가 완벽한 동반자가 되어줄 것입니다. 일상적인 언어로 "혼합된 텍스트 문자열에서 숫자만 추출하는 수식이 필요해"와 같이 달성하려는 목표를 설명하기만 하면 GPTExcel가 즉시 올바른 수식이나 M 코드를 생성해 줍니다. 파워 쿼리와 데이터 정리를 위한 AI를 결합하면 데이터 분석을 위한 강력한 툴킷을 갖게 됩니다.

자주 묻는 질문

파워 쿼리를 사용하면 원본 데이터 파일이 변경되나요?

아니요. 파워 쿼리는 소스 데이터에 대한 단방향 연결을 생성합니다. 데이터를 읽고, 메모리 내에서 변환을 적용한 다음, 새로운 결과를 엑셀에 출력합니다. 원본 CSV, 데이터베이스 또는 통합 문서는 완전히 손상되지 않고 안전하게 유지됩니다.

Mac에서도 파워 쿼리를 사용할 수 있나요?

네, Microsoft는 Mac용 엑셀의 파워 쿼리 지원을 크게 개선했습니다. 기존 Mac 버전에서는 Windows에서 사용할 수 있는 일부 고급 커넥터와 UI 기능이 부족했지만, 이제는 최신 버전의 Microsoft 365에서 로컬 파일, 데이터베이스에 연결하고 기존 쿼리를 원활하게 새로 고칠 수 있습니다.

파워 쿼리에서 병합(Merge)과 추가(Append)의 차이점은 무엇인가요?

병합은 VLOOKUP 또는 INDEX/MATCH와 동일한 역할입니다. 두 테이블 간의 공통 ID를 일치시켜 데이터의 새로운 열을 추가하는 데 사용합니다. 추가는 시트 맨 아래에 데이터를 복사하여 붙여넣는 것과 같습니다. 테이블을 위아래로 쌓아 새로운 행을 추가하는 데 사용합니다(예: 1월 판매량과 2월 판매량 합치기).

파워 쿼리 새로 고침이 실패하는 이유는 무엇인가요?

쿼리 새로 고침이 실패하는 가장 흔한 이유는 소스 파일이 이동, 이름 변경 또는 삭제되었기 때문입니다. 또 다른 빈번한 문제는 원본 데이터의 열 머리글이 변경된 경우입니다(예: 시스템에서 "Revenue"를 "Total Revenue"로 변경한 경우). 파워 쿼리 편집기를 열고 '적용된 단계' 창으로 이동하여 원본 단계를 업데이트하거나 단계 논리에서 열 이름을 변경하여 이 문제를 해결할 수 있습니다.