엑셀 파워 쿼리 오류 해결법 : 데이터 유형 불일치 및 로드 실패 디버깅

수십 개의 현업 분산 파일을 하나로 취합하거나 외부 전산 시스템의 대용량 로 데이터를 가공하기 위해 엑셀의 최강 자동화 도구인 '파워 쿼리(Power Query)'를 구축해 두었다가, 갑자기 [DataFormat.Error]나 [DataSource.Error] 레이블이 뜨며 새로 고침이 마비된 적이 있습니다. 엔터프라이즈 데이터 파이프라인 구축 과정에서 마주하는 이 오류는 단순한 수식 오타의 문제가 아닙니다. 파워 쿼리 연산 엔진의 '엄격한 정적 타이핑(Strict Typing)' 체제와 데이터 소스의 '구조적 변경(Schema Change)'이 충돌하면서 일어나는 컴파일 결함입니다. 이 글에서는 파워 쿼리가 데이터를 처리하는 백그라운드 매커니즘을 분석하고, 한 번 세팅하면 절대 깨지지 않는 무결점 자동화 쿼리를 정비하는 법을 공유합니다.

엑셀 파워 쿼리 변환 과정에서 발생하는 데이터 유형 불일치와 소스 로드 오류는 쿼리 편집기의 단계별 디버깅을 통해 최적화할 수 있습니다.

파워 쿼리 변환 엔진의 작동 원리와 오류 발생의 구조적 배경

파워 쿼리는 엑셀 시트 위에서 실시간으로 한 셀씩 연산하는 일반 함수와 완전히 다릅니다. 백그라운드에서 'M 코드(M-Formula Language)'라는 강력한 데이터 아키텍처 언어를 기반으로 작동하며, 데이터 소스로부터 데이터를 '추출(Extract)', '변환(Transform)', '적재(Load)'하는 B2B 표준 ETL 서브시스템입니다.

파워 쿼리 엔진이 가진 가장 큰 특징은 각 단계(Steps)가 순차적으로 맞물려 돌아가는 '체인형 구조'를 뗸다는 점입니다. 이전 단계에서 정의된 데이터의 구조(열의 이름, 열의 개수, 데이터의 포맷)가 다음 단계의 입력값으로 칼같이 전달됩니다. 이 과정에서 원본 텍스트 파일에 공백이 섞여 들어와 숫자로 지정된 열에 문자열이 박히거나, 원본 파일의 컬럼 명칭이 바뀔 경우 파워 쿼리 컴파일러는 데이터의 오염을 방지하기 위해 그 즉시 연산을 전면 중단하고 에러 행을 뱉어냅니다.

실무자를 괴롭히는 2대 파워 쿼리 적재 오류와 디버깅 솔루션

실무 보고서 자동화 환경에서 가장 높은 빈도로 쿼리를 깨뜨리는 두 가지 핵심 에러 패턴의 원인을 해부하고, 이를 우회하는 마스터 M 코드 디버깅 솔루션을 제안합니다.

1. 데이터 유형 불일치 오류 (DataFormat.Error)

쿼리를 새로 고침 했을 때 "대상을 숫자로 변환할 수 없습니다" 또는 "형식이 일치하지 않습니다"라는 메시지와 함께 특정 셀들이 [Error]로 채워지는 현상입니다. 이를 예방적으로 방어하는 핵심 M 코드를 공유합니다.

let
    소스 = Excel.CurrentWorkbook(){[Name="매출원장"]}[Content],
    공백치환 = Table.ReplaceValue(소스, "", null, Replacer.ReplaceValue, {"매출액", "수량"}),
    에러제거 = Table.RemoveRowsWithErrors(공백치환, {"매출액"}),
    유형변경 = Table.TransformColumnTypes(에러제거, {{"매출액", Int64.Type}, {"품명", type text}})
in
    유형변경

• 원인: 외부 시스템에서 추출한 데이터 중 빈 셀에 스페이스바(공백)가 들어가 있거나, 금액 데이터 사이에 텍스트 문자가 끼어들어 컴파일러가 형변환을 실패할 때 일어납니다.
• 해결책: 유형을 변경하는 단계 바로 전으로 이동하여 해당 열을 선택한 뒤, 파워 쿼리 홈 메뉴의 [값 바꾸기] 또는 위 스크립트 가이드처럼 Table.ReplaceValue 와 Table.RemoveRowsWithErrors를 선행 배치하여 에러 유발 인자를 완전히 청소한 뒤 적재해야 파이프라인 마비를 막을 수 있습니다.

2. 원본 소스 경로실종 및 컬럼 스키마 변경 오류

"테이블의 '열'을 찾을 수 없습니다" 혹은 "지정된 경로의 파일에 액세스할 수 없습니다"라며 쿼리 작동이 완전히 멈추는 치명적인 결함입니다.

• 원인: 협업 부서에서 원본 파일의 주소나 이름을 수정했거나, 서식을 보기 좋게 고친다며 공유 시트의 특정 열 이름을 바꾸어 버렸을 때 하드코딩된 변수 경로가 길을 잃는 현상입니다.
• 해결책: 고급 편집기(Advanced Editor)를 열어 소스 단계의 주소를 유동적인 동적 경로로 변경하거나, 열 이름을 기준으로 데이터를 고정 호출하는 하드코딩 구문을 지우고 인덱스 위치 기준 호출 방식으로 M 코드를 방어적으로 튜닝해야 합니다.

유지보수가 필요 없는 완벽한 자동화 쿼리를 위한 최적화 가이드

파워 쿼리 시스템의 신뢰성을 극대화하고 새로 고침 시 구글 클라우드 서버나 내부 리소스의 연산 과부하를 막기 위해서는 '작업 단계의 경량화(Query Folding)'를 실현해야 합니다.

가장 정석적인 방안은 데이터 정제 단계를 최소화하고 형변환은 맨 마지막에 일괄적으로 처리하는 것입니다. 쿼리 편집 창 우측의 [적용된 단계] 패널에 수십 개의 자잘한 단계가 나열되어 있으면, 새로 고침을 누를 때마다 백그라운드 엔진이 불필요한 가상 테이블을 메모리에 반복해서 생성하므로 연산 속도가 급격히 저하됩니다. 데이터를 가져온 직후 [행 필터링]과 [불필요한 열 제거]를 가장 먼저 배치하여 전송 데이터의 체급을 최소로 줄인 다음, 데이터 정제와 병합 단계를 유기적으로 결합하는 콤팩트한 설계를 유지해야 쿼리가 영구적으로 견고하게 구동됩니다.

요약 및 지속 가능한 데이터 자동화를 위한 당부

엑셀 파워 쿼리의 데이터 로드 오류는 단순한 시스템 결함이 아니라, 유입되는 로 데이터의 규격이 통제되지 않고 있음을 경고하는 시스템의 비상벨입니다. 에러가 날 때마다 수식을 임시방편으로 수정하는 미봉책에서 벗어나, 데이터가 흐르는 통로 자체에 견고한 거름망을 빌드해 두는 정비사의 관점을 가져야 합니다. 오늘 정비해 드린 타입 무결성 원칙과 M 코드 방어 설계 가이드를 여러분의 마스터 쿼리에 이식하여, 수많은 양의 파일이 쏟아져 들어와도 단 1초 만에 완벽한 보고서로 적재되는 강력한 오피스 자동화 파이프라인을 구축해 보시길 바랍니다.