엑셀 파워 쿼리에서 예제에서 열(Column From Examples) 사용 방법

우리 모두 그런 경험이 있습니다: 데이터가 이동할 때 깨지는 중첩된 엑셀 수식으로 어려움을 겪거나, 플래시 채우기를 사용했지만 절반의 행이 놓치는 경우를 말이죠. 저는 그런 취약한 해결 방법에 의존하는 것을 멈추고, 보다 신뢰할 수 있는 자동화를 위해 파워 쿼리의 예시로부터 열(Column From Examples)을 사용하기로 바꿨습니다.

예제에서 열 만들기가 더 나은 이유

불안정한 도구를 안정적인 자동화로 교환하세요

수년간 데이터를 정리하는 가장 일반적인 방법은 문제 해결이 악몽인 중첩된 MID, LEFT, FIND 수식을 만드는 것이었습니다. Excel이 Flash Fill을 도입했을 때, 그것은 마치 기적처럼 느껴졌습니다. 왜냐하면 그 수식들을 단순한 패턴 매칭으로 대체했기 때문입니다. 그러나 Flash Fill은 데이터와 연결된 상태를 유지하지 않으며, 복잡한 패턴을 종종 경고 없이 잘못 해석합니다.

Power Query의 예제에서 열(Column From Examples)은 두 가지 방법보다 더 강력하고 유연한 대안입니다. 이는 플래시 채우기(Flash Fill)의 편리함과 수식의 구조적 완전성을 제공합니다. 일회성 추측 대신 재사용 가능한 변환을 구축합니다. 원본 데이터가 업데이트되더라도 수식을 다시 작성하거나 열을 '재플래시(re-flash)'할 필요 없이 새로 고침을 클릭하기만 하면 됩니다.

데이터를 준비하고 편집기를 실행하세요

Power Query는 데이터가 단일 개체에 포함되어 행을 추가할 때 확장되므로 Excel 표(Ctrl+T) 형식으로 되어 있을 때 가장 잘 작동합니다.

시작하려면 테이블 안의 아무 곳이나 클릭한 다음 리본에서 데이터 탭으로 이동하고 테이블/범위에서를 선택하세요. 그러면 새 창에서 Power Query 편집기가 열립니다.

사용 사례 1: 복잡한 문자열에서 중첩된 ID 추출

문자 수 세기를 멈추고 도구가 패턴을 찾게 하세요

예제에서 열(Column From Examples)은 긴 문자열에서 패턴을 추론하고 시작 문자나 끝 문자 위치를 정의할 필요 없이 이를 분리합니다.

시나리오: T_MasterLogs 테이블의 첫 번째 열에는 다섯 자리 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: 엉망인 연락처 목록과 이름 표준화

혼란스러운 열을 전문적인 데이터로 변환

예제에서 열(Column From Examples) 도구는 원하는 결과를 관찰하여 여러 열을 결합하거나 대소문자를 수정할 수 있는 서식 엔진 역할을 합니다.

시나리오: 동일한 T_MasterLogs 테이블에서, 대소문자가 일정하지 않은 "First Name"과 "Last Name" 열이 있습니다. 또한 몇 가지 까다로운 항목—"o'connor"와 "d'angelo"—이 있는데, 첫 번째 아포스트로피 뒤에 대문자가 필요합니다. 목표는 하나의 "Full Name" 열을 생성하는 것입니다.

이것은 복잡하게 들리지만, 작업 흐름은 간단합니다:

1. Power Query 편집기에서 Ctrl를 누른 상태로 First Name과 Last Name 열을 모두 선택합니다.
2. 클릭 샘플에서 열 > 선택에서.
3. 새 열의 첫 번째 행에 John Smith를 입력하고 Enter 키를 누릅니다.

이제 아래 행에 대한 제안을 살펴보세요. Power Query는 아마 병합 및 올바른 대문자 처리 논리를 즉시 식별할 것입니다. 제 경우에는 O'Connor의 'C'와 D'Angelo의 'A'를 별도로 지시하지 않아도 올바르게 대문자로 표기했습니다.

즉, 특정 이름(예: 하이픈이 들어간 성)이 잘못 보이면, 해당 셀을 클릭하고 올바른 버전을 입력하여 도구의 논리를 미세 조정하면 됩니다.

모든 행에 대한 제안이 올바르게 보이면, 확인을 클릭하여 새 열을 추가합니다. 새 열의 이름을 바꾼 다음, 오래되고 정리가 안 된 기존 열을 마우스 오른쪽 버튼으로 클릭하여 제거합니다.

큰 보상: 데이터 새로 고침

마법이 일어나는 것을 지켜보세요

예제를 마친 후에야 Power Query의 장점을 진정으로 알 수 있습니다. 새 데이터를 추가할 때마다 수동으로 다시 실행해야 하는 Flash Fill과 달리, Power Query는 방금 만든 단계의 순서를 저장합니다.

새 열에 만족하면:

1. 홈 탭에서, 닫기 및 로드 버튼의 상단 절반을 클릭하여 정리된 데이터를 새 워크시트에 넣으세요.
2. 원본 T_MasterLogs 테이블에 새로운, 지저분한 행을 추가해 보세요(예: 모든 소문자로 된 이름이나 새로운 ID 문자열).
3. 정리된 결과 테이블로 돌아가서, 마우스 오른쪽 버튼을 클릭한 다음, 새로 고침을 클릭하세요.

엑셀은 이후 새 데이터를 이전에 생성한 M 논리를 통해 실행하여 ID를 추출하고 새 행의 대소문자를 자동으로 수정합니다. 이는 일관되게 구조화되어 있지만 불규칙하거나 정리되지 않은 형식으로 되어 있는 데이터를 가져올 때 특히 유용합니다.

앞서 언급한 황금률을 기억하세요: 원본 데이터가 크게 변경되고 새로 고침이 실패하더라도 당황하지 마세요. 편집기로 돌아가 적용된 단계 창에서 해당 단계를 삭제한 후, 새 패턴에 맞게 엔진을 다시 학습시키기 위해 과정을 다시 시작하면 됩니다.

웹용 Excel에서 작업 중이신가요? 다른 옵션이 있습니다

Power Query의 전체 기능은 주로 데스크톱 버전의 Excel을 사용하는 사람들을 위해 제공됩니다. 웹용 Excel에서 실행하면 더 제한된 기능 세트를 확인할 수 있습니다. 하지만 걱정하지 마세요—유사한 결과를 얻을 수 있는 다른 방법이 있습니다. '예제 기반 수식'(Formula by Example)은 웹용으로 특별히 만들어진 강력한 대안이며, (작성 시점 기준으로) Copilot 구독이 필요하지 않습니다. 단, 데이터가 Excel 표에 있는지 확인하기만 하면 됩니다.

어느 쪽이든, Power Query에서 중첩된 ID를 분리하든지 아니면 Formula by Example을 사용하든지 간에, 이는 깨지기 쉬운 수식과 신뢰할 수 없는 Flash Fill의 시대를 넘어서는 중요한 단계입니다. 한 번 시도해 보면, 아마 다시 돌아가지 않을 것입니다.