엑셀 매크로(VBA) 없이 파워 쿼리(Power Query)로 다중 파일 데이터 원클릭으로 병합하기

엑셀에서 동일한 양식의 파일 수십 개를 하나로 합칠 때, 수작업 복사·붙여넣기나 VBA 매크로에 의존하는 방식은 유지보수가 어렵고 오류 발생 가능성이 높다. 파워 쿼리(Power Query)의 폴더 결합 기능은 코드를 한 줄도 작성하지 않고도 폴더 단위로 다중 파일을 자동 병합하며, 새 파일이 추가되어도 '새로 고침' 한 번으로 결과를 갱신한다. 이 글은 2026년 6월 기준 마이크로소프트 365 엑셀 환경에서 파워 쿼리만으로 다중 파일을 병합하는 원리와 단계별 절차, 실무 적용 시 유의점을 백과사전식으로 서술한다.

1. 기본 개념 및 정의

파워 쿼리는 엑셀의 '데이터(Data)' 탭에 '가져오기 및 변환(Get & Transform Data)' 그룹으로 내장된 ETL(Extract, Transform, Load) 엔진이다. 외부 또는 내부 데이터 원본에 연결하여 데이터를 추출하고, 정제·가공한 뒤, 워크시트나 데이터 모델로 적재하는 일련의 과정을 자동화한다. 2016년 버전부터 엑셀에 기본 탑재되었으며, 2026년 현재 마이크로소프트 365 구독형 엑셀(윈도우 및 맥)과 영구 라이선스 버전 모두에서 표준 기능으로 제공된다. 다만 엑셀 2007 이하의 구형 버전에서는 지원되지 않는다.

다중 파일 병합의 핵심은 파워 쿼리의 '폴더에서(From Folder)' 커넥터이다. 이 커넥터는 개별 파일이 아니라 폴더 경로 자체를 데이터 원본으로 인식한다. 폴더를 지정하면 파워 쿼리는 우선 해당 폴더와 하위 폴더에 존재하는 모든 파일의 메타데이터(파일명, 확장자, 수정 날짜, 폴더 경로, 그리고 파일 본문의 이진 표현인 Content 열)를 하나의 목록 형태로 불러온다. 이후 각 파일의 이진 데이터를 동일한 규칙으로 해석·전개하여 행 단위로 누적함으로써 단일 테이블을 생성한다.

이 방식이 VBA 매크로 대비 우월한 본질적 이유는 '재현 가능한 절차의 저장'에 있다. 파워 쿼리는 사용자가 수행한 모든 변환 단계를 M 언어 기반의 쿼리로 기록한다. 따라서 동일한 구조의 파일이 폴더에 추가되거나 기존 파일이 수정되어도, 쿼리 자체를 다시 작성할 필요 없이 새로 고침만으로 전체 병합 결과가 즉시 갱신된다. 이는 일회성 합치기가 아니라 반복적·주기적 데이터 통합 파이프라인을 구축하는 행위에 해당한다.

병합이 정상적으로 작동하기 위한 전제 조건은 '스키마의 일관성'이다. 결합 대상 파일들은 동일한 열 이름, 동일한 데이터 형식, 동일한 열 구성을 가져야 한다. 열의 물리적 순서는 일치하지 않아도 무방한데, 파워 쿼리는 열 이름을 기준으로 데이터를 정렬·매칭하기 때문이다. 엑셀 통합 문서를 병합할 경우에는 모든 파일에서 동일한 워크시트 이름 또는 동일한 표(Table) 이름이 존재해야 안정적으로 동작한다.

2. 핵심 활용 방법 및 단계별 가이드

2-1. 폴더 연결 및 결합 실행

폴더 병합의 표준 절차는 다음 순서로 진행된다. 먼저 병합 대상 파일을 하나의 전용 폴더에 모은다. 이때 병합과 무관한 파일을 같은 폴더에 두지 않는 것이 원칙이다. 폴더 내 모든 파일과 선택한 하위 폴더가 결합 대상에 포함되므로, 불필요한 파일이 섞이면 오류 또는 의도치 않은 데이터 혼입이 발생한다.

데이터 탭에서 '데이터 가져오기(Get Data)'를 선택한 뒤 '파일에서(From File)' 항목의 '폴더에서(From Folder)'를 클릭한다. 탐색 대화 상자에서 대상 폴더를 지정하면 폴더 내 파일 목록이 표시된 미리 보기 창이 나타난다. 이 단계에서 세 가지 명령을 선택할 수 있으며, 각각의 성격은 다음과 같다.

• '결합 및 로드(Combine & Load)'는 미리 보기에서 곧바로 모든 파일을 병합하여 워크시트로 적재한다. 추가 정제가 필요 없는 단순 통합에 적합하다.
• '결합 및 변환(Combine & Transform)'은 병합을 수행하되 적재 전에 파워 쿼리 편집기를 열어 추가 가공을 가능하게 한다.
• '데이터 변환(Transform Data)'은 병합을 즉시 실행하지 않고 파일 목록 상태로 편집기를 연다. 확장자나 폴더 경로 열을 필터링하여 결합할 파일을 선별한 뒤 결합할 수 있어 가장 정밀한 통제가 가능하다.

실무에서는 '데이터 변환'으로 편집기를 먼저 열고, 확장자(Extension) 열을 소문자로 통일한 다음 원하는 형식만 필터링하는 방식이 권장된다. 파워 쿼리는 대소문자를 구분하므로 '.CSV'와 '.csv'가 서로 다른 값으로 취급될 수 있으며, 소문자 변환 단계는 이러한 불일치를 사전에 차단한다. 선별이 끝나면 Content 열 머리글의 '파일 결합(Combine Files)' 아이콘(두 개의 아래 화살표)을 클릭하여 병합을 실행한다.

2-2. 변환 샘플 파일 활용 및 주의사항

파일 결합을 실행하면 파워 쿼리는 '도우미 쿼리(Helper Queries)' 그룹을 자동으로 생성한다. 이 그룹에는 매개 변수(Parameter), 샘플 파일(Sample File), '샘플 파일 변환(Transform Sample File)' 쿼리, 변환 함수, 그리고 최종 결합 쿼리가 포함된다. 이 구조를 이해하는 것이 안정적 운용의 핵심이다.

가장 중요한 객체는 '샘플 파일 변환' 쿼리이다. 파워 쿼리는 폴더의 첫 번째 파일을 표본으로 삼아 변환 함수를 생성하고, 이 함수를 폴더 내 모든 파일에 동일하게 적용한다. 따라서 머리글 승격, 불필요한 열 제거, 데이터 형식 변경, 잘못된 행 삭제 등 모든 파일에 공통적으로 적용해야 할 정제 작업은 반드시 이 '샘플 파일 변환' 쿼리 안에서 수행해야 한다. 이곳에서 정의된 단계는 결합 시 자동으로 전체 파일에 전파된다.

반대로 최종 결합 쿼리(폴더명이 붙은 쿼리)에서 직접 변환을 가하면, 새 파일 추가 시 구조 불일치로 오류가 발생할 위험이 커진다. 다수 사용자가 겪는 '병합이 깨지거나 데이터가 중복되는 문제'의 상당수는 변환 단계를 잘못된 위치에 적용한 데서 기인한다. 즉, 개별 파일 단위 변환은 샘플 파일 쿼리에, 병합 이후 전체 테이블 단위 변환은 최종 쿼리에 적용한다는 원칙을 준수해야 한다.

추가로 유의할 점은 다음과 같다. 첫째, 표(Table)로 서식이 지정되지 않은 원본의 경우 데이터가 A1 셀의 머리글 행부터 시작하고 하단에 합계나 주석 같은 부가 텍스트가 없어야 한다. 둘째, 파일 출처 추적이 필요하면 Name(파일명) 열을 제거하지 않고 유지하여 각 행이 어느 파일에서 왔는지 식별할 수 있다. 셋째, 병합 결과는 데이터 탭의 '모두 새로 고침(Refresh All)'으로 갱신되며, 폴더에 새 파일을 넣은 뒤 새로 고침하면 추가 데이터가 자동 반영된다.

3. 실무 적용 예시 및 자주 묻는 질문

• 월별 매출 보고서 통합: 매월 동일 양식의 엑셀 파일이 생성되는 환경에서, 해당 파일을 지정 폴더에 누적 저장하고 새로 고침만 반복하면 연간 통합 테이블이 자동으로 확장된다. 12개월치 수작업 통합이 단 한 번의 클릭으로 대체된다.
• 부서별 예산 취합: 여러 부서가 동일 템플릿으로 제출한 파일을 한 폴더에 모으면 수십 개 예산서를 즉시 단일 데이터셋으로 결합할 수 있다.
• CSV와 엑셀의 혼용: 폴더에 형식이 다른 파일이 섞여 있을 경우, 확장자 열 필터링으로 특정 형식만 선별 결합하여 성능을 개선하고 변환을 단순화할 수 있다.
• "표 이름이나 시트 이름이 파일마다 다르면 어떻게 되는가": 결합 시 첫 파일을 기준으로 특정 개체를 지정하므로, 모든 파일에 동일한 이름의 시트나 표가 존재해야 안정적으로 작동한다. 이름이 불일치하면 일부 파일이 누락되거나 오류가 발생한다.
• "단순 누적만 필요하고 변환이 불필요하다면": 마이크로소프트 365 및 엑셀 웹용에서는 VSTACK·HSTACK 함수로 여러 범위를 즉시 세로·가로로 쌓을 수 있다. 다만 이 함수는 폴더 단위 자동 감지와 새로 고침 기반 갱신을 제공하지 않으므로, 반복 통합 파이프라인에는 폴더 결합 방식이 적합하다.
• "코파일럿(Copilot)으로 대체 가능한가": 2026년 현재 엑셀에는 AI 보조 기능이 강화되어 변환 단계 제안 등에 활용할 수 있으나, 폴더 결합 쿼리는 결정론적 ETL 절차로서 재현성과 감사 가능성이 보장된다는 점에서 여전히 핵심 기반 기술로 기능한다.

[결론]

파워 쿼리의 폴더 결합 기능은 VBA 없이도 다중 파일을 단일 테이블로 병합하는 가장 효율적인 비코딩 방식이다. 핵심은 '데이터 가져오기 > 파일에서 > 폴더에서'로 연결하고, '데이터 변환'으로 결합 대상을 선별한 뒤, '샘플 파일 변환' 쿼리에 공통 정제 단계를 정의하는 절차에 있다. 모든 파일의 스키마 일관성이 전제되어야 하며, 변환 단계의 적용 위치를 정확히 구분하는 것이 오류를 방지하는 관건이다. 일단 구축된 쿼리는 새 파일 추가 시 새로 고침만으로 갱신되므로, 일회성 작업이 아니라 지속 가능한 데이터 통합 자동화 체계를 완성한다.