들어가기 전에
파워 쿼리의 '예제의 열' 기능은 원하는 결과를 ‘예시로 입력’하면, 복잡한 변환 과정을 자동으로 만들어줍니다. 새 열에 원하는 값을 몇 개만 입력하면, 파워 쿼리가 패턴을 분석해 나머지 데이터도 자동으로 채워줍니다. 텍스트 결합, 구간 분류, 형식 변환 등 다양한 데이터 가공을 빠르게 처리해 수동 작업을 크게 줄여줄 수 있습니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: I stopped using Flash Fill in Excel: This tool is much more reliable
- URL: https://www.howtogeek.com/microsoft-excel-power-query-column-from-examples/
'예제의 열' 기능을 사용하는 것이 더 나은 방법인 이유
수년간 데이터를 정리하는 가장 일반적인 방법은 중첩된 MID, LEFT, FIND 함수를 사용하는 것이었는데, 이는 문제 해결에 있어 악몽과도 같았습니다. 엑셀에서 '빠른 채우기' 기능을 도입했을 때, 이러한 복잡한 함수를 간단한 패턴 일치로 대체해 주었기에 마치 기적처럼 느껴졌습니다. 하지만 플래시 채우기는 데이터와의 연결을 유지하지 않으며, 경고 없이 복잡한 패턴을 잘못 해석하는 경우가 종종 있습니다.
Power Query의 '예제의 열' 기능은 기존 방식보다 더 강력하고 유연한 대안입니다. 빠른 채우기의 편리함과 수식의 구조적 안정성을 모두 제공합니다. 한 번만 추측하는 방식이 아니라 재사용 가능한 변환을 생성합니다. 원본 데이터가 업데이트되더라도 수식을 다시 작성하거나 열을 다시 플래시할 필요 없이 '새로 고침'을 클릭하기만 하면 됩니다.
Power Query는 데이터가 Excel 표 형식(Ctrl + T)으로 되어 있을 때 가장 잘 작동합니다. 이렇게 하면 행을 추가할 때마다 데이터가 단일 개체로 확장되기 때문입니다.
아래 예시에서는 T_MasterLogs 라는 테이블을 사용하겠습니다. 예제 파일은 [여기]에서 무료로 다운로드할 수 있습니다.
시작하려면 표 내부 아무 곳이나 클릭한 다음 리본 메뉴의 데이터 탭으로 이동하여 표/범위에서 가져오기를 선택합니다 . 그러면 새 창에서 Power Query 편집기가 열립니다.
사용 사례 1: 복잡한 문자열에서 중첩된 ID 추출
'예시에서 열 추출' 기능은 긴 텍스트 문자열에서 패턴을 추론하고 시작 또는 끝 문자 위치를 정의할 필요 없이 해당 패턴을 분리합니다.
시나리오: T_MasterLogs 테이블 의 첫 번째 열에 는 5자리 ID가 포함된 긴 문자열이 있습니다. 이 ID를 추출해야 하는데, 월 이름의 길이에 따라 ID의 위치가 바뀝니다.
(1) Power Query 편집기에서 열 추가 탭을 열고 예제에서 열을 클릭합니다.
(2) 새로 생성된 빈 열에 첫 번째 행의 ID(88392)를 입력한 다음 Enter 키를 누릅니다.
(3) Power Query에서 아래 행에 정확한 제안을 표시하면 확인을 클릭하여 확정합니다.
(4) 새 열의 이름을 바꾸고 위치를 조정하세요.
Power Query가 나머지 ID를 정확하게 추측하더라도, 도구를 과도하게 학습시키는 것이 좋습니다. 데이터 세트가 큰 경우, 구조가 다른 행을 찾아 해당 행의 ID를 수동으로 입력해 보세요. 이렇게 하면 Power Query가 데이터 구조 변화에 따른 오류 발생 가능성이 낮은 보다 유연한 변환을 추론할 수 있게 됩니다.
이 단계를 완료하면 Power Query는 M이라는 언어로 백그라운드 스크립트를 작성합니다. 처음 시도할 때는 하이픈(-)과 같은 특정 구분 기호를 찾았을 수도 있습니다. 나중에 데이터 형식이 달라지면 오류가 발생할 수 있습니다. 하지만 걱정하지 마세요. 빠르게 수정할 수 있습니다.
(1) 다시 시작하기 전에, 적용된 단계 창의 설정 톱니바퀴 아이콘을 통해 단계를 조정해 보세요.
(2) 그래도 문제가 해결되지 않으면 해당 단계를 삭제 하고 더 다양한 예제를 사용하여 다시 학습시키세요.
사용 사례 2: 정리되지 않은 연락처 목록과 이름 표준화
'예제의 열' 도구는 여러 열을 결합하거나 원하는 결과를 관찰하여 대소문자를 수정하는 서식 엔진 역할을 합니다.
상황 설명: T_MasterLogs 테이블 에 "First Name"과 "Last Name" 열이 있는데, 대소문자가 뒤죽박죽입니다. 또한 "o'connor"와 "d'angelo"처럼 첫 번째 아포스트로피 뒤에 대문자가 되어야 하는 까다로운 항목도 있습니다. 목표는 모든 항목을 하나의 "Full Name" 열로 통합하는 것입니다.
복잡해 보이지만 실제 작업 과정은 간단합니다.
(1) 파워 쿼리 편집기에서 Ctrl 키를 누른 상태로 '이름' 과 '성' 열을 모두 선택합니다.
(2) 예제에서 열을 클릭하고 선택 항목에서 선택합니다.
(3) 새 열의 첫 번째 행에 John Smith를 입력하고 Enter 키를 누릅니다.
이제 아래 행에 대한 제안 사항을 살펴보세요. Power Query는 병합 및 대소문자 변환 논리를 즉시 식별할 것입니다. 제 경우에는 별도의 지시 없이도 O'Connor의 "C"와 D'Angelo의 "A"를 정확하게 대문자로 변환했습니다.
하지만 특정 이름(예: 하이픈으로 연결된 성)이 잘못된 것처럼 보이는 경우 해당 셀을 클릭하고 올바른 버전을 입력하여 도구의 논리를 미세 조정할 수 있습니다.
모든 행에 대한 제안이 올바른지 확인한 후 확인을 클릭하여 새 열을 추가합니다. 새 열의 이름을 변경한 다음 기존의 지저분한 열을 우클릭하여 제거합니다.
가장 큰 장점: 데이터 새로 고침
예제를 모두 마치고 나면 파워 쿼리의 장점을 확실히 알 수 있습니다. 새 데이터를 추가할 때마다 수동으로 다시 실행해야 하는 플래시 필과 달리, 파워 쿼리는 방금 구성한 단계 순서를 저장합니다.
새로운 열이 마음에 든다면 다음 단계를 따릅니다.
(1) 홈 탭 에서 닫기 및 불러오기 버튼 의 윗부분을 클릭하여 정리된 데이터를 새 워크시트에 붙여넣습니다.
(2) 기존 T_MasterLogs 테이블 에 새롭고 불규칙적인 행 (예: 모두 소문자인 이름이나 새로운 ID 문자열)을 추가해 보세요.
(3) 정리된 결과 표로 돌아가서 아무 곳이나 마우스 오른쪽 버튼으로 클릭 하고 새로 고침을 클릭하세요.
Excel은 이전에 생성한 M 논리를 사용하여 새 데이터를 처리하고 ID를 추출하며 새 행의 대소문자를 자동으로 수정합니다. 이는 구조는 일관적이지만 형식이 일관되지 않거나 정돈되지 않은 데이터를 가져올 때 특히 유용합니다.
앞서 언급했던 황금률을 기억하세요. 소스 데이터가 크게 변경되어 새로 고침이 실패하더라도 당황하지 마세요. 편집기로 돌아가서 ' 적용된 단계' 창에서 해당 단계를 삭제하고 프로세스를 다시 시작하여 새 패턴으로 엔진을 재학습시키면 됩니다.
마치며
Power Query의 모든 기능은 주로 데스크톱 Excel에서 사용할 수 있고, 웹용 Excel에서는 기능이 제한됩니다. 대신 웹에서는 Formula by Example을 활용해 비슷한 작업을 할 수 있으며, 별도의 Copilot 구독 없이도 사용 가능합니다. 데이터만 표 형태로 준비하면 됩니다. Power Query든 Formula by Example이든 이를 활용하면 불안정한 수식이나 빠른 채우기에 의존하던 방식에서 벗어날 수 있으며, 한 번 사용하면 이전 방식으로 돌아가기 어려울 정도로 유용합니다.
'Excel' 카테고리의 다른 글
| 코딩 몰라도 OK! 지금 바로 써볼 수 있는 Python in Excel 활용법 4가지 (0) | 2026.08.20 |
|---|---|
| 이걸 안 쓴다면 엑셀에서 가장 쉽고도 똑똑한 시각화 도구를 놓치고 있는 것 (0) | 2026.08.18 |
| 데이터를 일일이 찾지 않고 SEARCH 함수로 더 똑똑한 자동화 시트 만들기 (0) | 2026.08.10 |
| 알아볼 수 없는 엑셀 수식을 작성하는 것을 멈추고 이렇게 하세요 (0) | 2026.08.06 |
| 엑셀 실력을 향상시키기 위해 버려야 하는 6가지 습관 (0) | 2026.08.04 |
