이 글의 목적은 엑셀 파워쿼리(Power Query) 새로고침 실패 문제를 에러 유형별로 진단하고, 현장에서 즉시 적용 가능한 실무 해결책과 표준 운영 절차(SOP)를 제공하는 것이다.
1. 새로고침 실패를 재현·기록·분류하는 표준 절차
문제 해결의 80%는 재현과 기록의 정확도에서 결정되므로 다음 순서로 진단을 시작해야 한다.
- 원본 파일·DB 접속 확인 : 파일 경로, 네트워크 공유, DB 포트 및 로그인 권한을 독립적으로 검증한다.
- 단일 쿼리 단독 실행 데이터 → 쿼리 및 연결 창에서 오류 쿼리만 우클릭 후 미리 보기 새로 고침 을 실행한다.
- 파워쿼리 편집기 로그 확보 : 편집기에서 도구 → 진단 → 진단 시작 으로 Query Diagnostics를 켠 다음 새로고침을 재현하고 진단 중지 후 결과 테이블을 저장한다.
- 단계별 적용 취소 적용된 단계 를 뒤에서 앞으로 한 단계씩 제거하며 실패 지점을 특정한다.
- 에러 메시지 표준 분류 : 연결/인증, 스키마 변경, 드라이버/버전, 시간초과·메모리, 파일·경로, 프록시·TLS 보안, 기타로 분류한다.
주의 :
여러 쿼리를 병렬 새로고침하면 원인 식별이 어렵다. 실패 쿼리를 단독으로 직렬 실행하여 원인을 분리해야 한다.
2. 에러 유형별 원인과 1차 조치
| 유형 | 주요 증상 | 즉시 조치 |
|---|---|---|
| 연결·인증 오류 | “다음에 연결할 수 없음”, “자격 증명 필요”, Formula.Firewall 경고 | 데이터 원본 설정 → 사용 안 함 자격 증명 지우기 후 다시 로그인한다. 원본별 개인정보 수준 을 동일하게 맞춘다. |
| 스키마 변경 | “키가 어떤 행과도 일치하지 않음”, “열을 찾을 수 없음” | 누락 열을 허용하는 변환으로 교체한다. Table.SelectColumns 의 MissingField.UseNull 사용을 검토한다. |
| 드라이버·비트수 | ODBC/OLE DB 제공자 없음, 32/64비트 불일치 | 엑셀 비트수와 동일한 드라이버를 설치하고 ODBC 데이터 원본 관리자 에서 DSN을 재등록한다. |
| 시간초과·대용량 | 길게 멈춤 후 실패, 부분 로드 | 원본 커맨드 타임아웃을 늘리고, 필요 시 페이지네이션·필터·쿼리 폴딩을 적용한다. |
| 파일·경로 문제 | “파일을 사용할 수 없음”, “경로를 찾을 수 없음” | 경로 길이, 잠금, 동기화 상태를 점검한다. 상대경로 또는 SharePoint.Contents 로 전환한다. |
| 프록시·TLS·방화벽 | 웹·SharePoint 새로고침 실패, 인증 반복 | 시스템 프록시와 TLS 1.2 이상 사용을 확인한다. 회사 방화벽 예외에 도메인을 등록한다. |
| 추가기능·캐시 충돌 | 엑셀 멈춤, 간헐 실패 | COM 추가 기능을 최소화하고 Power Query 캐시를 정리한다. |
3. 연결·인증 문제 해결(로컬·클라우드 공통)
3.1 데이터 원본 설정 초기화
- 데이터 → 데이터 원본 설정 을 열어 모든 관련 원본을 선택한다.
- 권한 지우기 후 엑셀을 재시작하고 쿼리 새로고침 시 자격 증명을 다시 저장한다.
- 개인정보 수준 을 조직 또는 없음 으로 통일해 Formula.Firewall 경고를 방지한다.
주의 :
서로 다른 개인정보 수준의 원본을 병합하면 방화벽 규칙으로 인해 새로고침이 차단된다. 동일 수준으로 통일하거나 단계 분리를 적용해야 한다.
3.2 SharePoint·OneDrive·웹 인증 안정화
- 조직 계정 사용 시 브라우저 기반 로그인이 요구되므로 기본 브라우저의 SSO 상태를 점검한다.
- 변동 토큰 오류가 잦으면 파일 → 옵션 → 신뢰 센터 에서 보호된 보기와 파일 차단 설정을 확인한다.
- 프록시 환경에서는 시스템 프록시 자동 감지를 사용하고 고정 프록시는 예외 목록에 대상 도메인을 추가한다.
4. 스키마 변경 내성 설계
4.1 누락 열 허용 선택
// 기존: 존재하지 않는 열이 있으면 실패함 = Table.SelectColumns(Source,{"ID","Name","Amount"})
// 개선: 누락 열을 Null로 채워 새로고침 지속
= Table.SelectColumns(Source,{"ID","Name","Amount"}, MissingField.UseNull)
4.2 안전한 병합 키 설계
// 키 불일치 방지: 트림·대소문자 통일·널 처리 let A = Table.TransformColumns(TableA, {{"Key", each Text.Upper(Text.Trim(_)), type text}}), B = Table.TransformColumns(TableB, {{"Key", each Text.Upper(Text.Trim(_)), type text}}), M = Table.NestedJoin(A, {"Key"}, B, {"Key"}, "B", JoinKind.LeftOuter), R = Table.ExpandTableColumn(M, "B", {"Col1","Col2"}) in R
4.3 동적 컬럼 확장
// 새로 추가되거나 순서가 바뀐 열을 자동 확장 = Table.ExpandTableColumn(Nested, "Details", Table.ColumnNames(DetailsSample))
5. 시간초과·대용량 처리
5.1 SQL 원본 타임아웃 확장
// SQL Server 연결의 커맨드 타임아웃 30분 = Sql.Database("SERVERNAME", "DBNAME", [CommandTimeout=#duration(0,0,30,0)])
5.2 웹 호출 타임아웃·헤더 지정
// 10분 타임아웃 + 사용자 에이전트 지정 = Web.Contents("https://example.com/api", [Timeout=#duration(0,0,10,0), Headers=[Accept="application/json", #"User-Agent"="ExcelPQ/1.0"]])
5.3 페이지네이션으로 대량 API 안정화
List.Generate( ()=> [i=1, url="https://api.example.com/items?page=1"], each [i] <= 100, each [ i = [i]+1, url = "https://api.example.com/items?page=" & Text.From([i]) ], each Json.Document(Web.Contents([url]))[items] )
5.4 쿼리 폴딩 유지 전략
- 가능한 한 원본 초기 단계에서 필터·열 선택을 수행한다.
- 사용자 정의 함수, 인덱스 추가, 행 컨텍스트 연산은 폴딩을 중단할 수 있으므로 시점을 뒤로 미룬다.
- 편집기에서 단계 우클릭 → 쿼리 폴딩 보기 로 서버 사이드 실행 여부를 확인한다.
6. 드라이버·비트수 불일치 해결
- 엑셀 비트수(32/64)를 계정 → 엑셀 정보 에서 확인한다.
- 동일 비트수의 ODBC/OLE DB 드라이버를 설치한다.
- ODBC 데이터 원본 관리자 (32비트/64비트 각각)에서 시스템 DSN을 재구성하고 테스트한다.
주의 :
64비트 엑셀에서 32비트 ODBC DSN은 표시되지 않는다. 비트수를 반드시 일치시켜야 한다.
7. 파일·경로·잠금 이슈
- 경로 길이 : 총 218자 제한에 근접하면 실패할 수 있다. 폴더 깊이를 줄이고 파일명을 단순화한다.
- 잠금 : 공유 네트워크나 클라우드 동기화 중에는 잠금으로 읽기 실패가 발생한다. 동기화를 일시 중지하고 로컬 복사본으로 검증한다.
- 상대 경로 : 이동식 경로나 사용자별 경로 차이를 줄이기 위해 쿼리 내 경로를 상대값으로 관리한다.
// Windows 환경변수로 사용자 경로 차이 흡수 = Excel.Workbook(File.Contents(Environment["USERPROFILE"] & "\Data\sales.xlsx"))
8. 프록시·TLS·방화벽
- 회사 프록시 사용 시 인터넷 옵션 → 연결 → LAN 설정 의 자동 감지 또는 프록시 스크립트를 적용한다.
- TLS 1.2 이상을 사용하도록 시스템 보안 설정을 확인한다.
- 방화벽에서 SharePoint·OneDrive·대상 API 도메인을 허용 목록에 추가한다.
9. 추가 기능·캐시 충돌 최소화
- 파일 → 옵션 → 추가 기능 에서 COM 추가 기능을 모두 비활성 후 문제 재현으로 충돌 여부를 확인한다.
- Power Query 캐시 폴더를 정리한다.
REM Power Query 캐시 폴더 열기(사용자별 경로) %LOCALAPPDATA%\Microsoft\Power Query\Cache
10. 자동화 새로고침 안전화(VBA·매크로)
대용량·장시간 새로고침을 워크북 이벤트로 자동화할 때는 예외 처리와 진행 상황 표시가 필요하다.
' ThisWorkbook 모듈 Private Sub Workbook_Open() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False On Error GoTo EH ThisWorkbook.RefreshAll Application.StatusBar = "Power Query refresh completed" GoTo Clean EH: MsgBox "새로고침 실패: " & Err.Description, vbExclamation Clean: Application.ScreenUpdating = True Application.StatusBar = False End Sub
11. 품질 보증을 위한 SOP 체크리스트
| 단계 | 점검 항목 | 합격 기준 | 빈도 |
|---|---|---|---|
| 연결 | 자격 증명·개인정보 수준 통일 | 경고 없음 | 매 배포 |
| 스키마 | 누락 열 Null 허용, 키 표준화 | 스키마 변경에도 성공 | 월 1회 |
| 성능 | 타임아웃 값, 폴딩 유지 | 새로고침 < 목표시간 | 분기 1회 |
| 드라이버 | 비트수·버전 일치 | 테스트 연결 통과 | 매 설치 |
| 배포 | 상대경로·동기화 정책 | 이동 후에도 성공 | 매 배포 |
12. 자주 발생하는 에러와 처방
Expression.Error: The key didn't match any rows
원인: 조인 키 불일치 또는 참조 테이블에 키가 누락되었기 때문이다. 처방: 키 정규화(Text.Trim, Text.Upper) 후 LeftOuter 조인을 유지하고, 필요 시 JoinKind.LeftOuter 로 누락 행을 보존한다.
We couldn't authenticate with the credentials provided
원인: 토큰 만료 또는 저장된 자격 증명 형식 불일치이다. 처방: 데이터 원본 설정에서 권한 지우기 후 재로그인하고, 조직 계정의 경우 브라우저 SSO 상태를 점검한다.
DataSource.Error: ODBC: Data source name not found
원인: DSN 미설정 또는 비트수 불일치이다. 처방: 엑셀과 동일 비트수의 ODBC 관리자에서 시스템 DSN을 생성한다.
Timeout expired / 작업 시간이 초과되었습니다
원인: 서버 응답 지연 또는 클라이언트 제한이다. 처방: CommandTimeout 을 확대하고, 서버측 인덱스·필터·페이지네이션으로 데이터량을 줄인다.
The file is locked / 다른 프로세스에서 사용 중
원인: 동기화·공유 잠금이다. 처방: 동기화를 일시 중지하고 로컬 경로에서 새로고침을 검증한다.
13. 성능 최적화 팁
- 초기 필터링 : 날짜·상태 등 필수 조건을 첫 단계에서 적용한다.
- 컬럼 최소화 : 필요한 열만 유지해 메모리 압력을 줄인다.
- 캐시 전략 : 변경 빈도가 낮은 참조 테이블은 별도 쿼리로 분리해 재사용한다.
- 단계 병합 : 연속 변환은 Table.Buffer 남용 없이 합리적으로 묶는다.
14. 진단 결과 보고서 템플릿
문제 요약: <에러 메시지 원문> 발생 빈도/영향: <사용자 수, 업무 영향, 주기> 재현 절차: <단계 목록> 근본 원인(RCA): <연결/스키마/드라이버/시간초과/경로/보안/기타> 영구 조치(Permanent Fix): <쿼리 수정, 인프라, 정책> 임시 조치(Containment): <수동 로드, 부분 새로고침> 검증 결과: <성공/실패, 소요시간> 추적 링크: <파일 경로 또는 문서 ID>
15. 배포 전 최종 점검(요약)
- 자격 증명·개인정보 수준 통일 확인한다.
- 누락 열·키 불일치 내성을 설계한다.
- 타임아웃·폴딩·페이지네이션을 적용한다.
- 드라이버 비트수·버전을 일치시킨다.
- 경로 길이·동기화 잠금을 제거한다.
- 캐시 정리 및 추가 기능 최소화한다.
- 진단 로그를 보관하고 SOP 체크리스트를 갱신한다.
FAQ
새로고침 중간에 일부 쿼리만 실패한다. 병렬 실행을 끌 수 있나?
파워쿼리는 엑셀에서 쿼리를 병렬로 실행할 수 있다. 충돌을 의심하면 실패 쿼리만 단독으로 새로고침하고, 자동화 시에는 VBA에서 순차 실행 흐름을 구현한다.
CSV 인코딩 문제로 한글이 깨진다. 어떻게 지정하나?
Csv.Document 에서 인코딩을 명시한다. 예: 65001(UTF-8) 또는 949(ANSI/Korean) 을 지정한다.
쿼리 편집 없이 타임아웃만 늘리고 싶다. 가능하나?
원본 함수 옵션에 CommandTimeout 또는 Timeout 을 추가해야 하므로 최소한의 단계 편집은 필요하다.
공유 통합문서로 배포 시 사용자마다 경로가 달라 실패한다.
상대 경로, 환경변수, SharePoint 라이브러리 경로 함수( SharePoint.Contents )를 사용해 사용자별 차이를 흡수한다.
대용량 API에서 메모리 부족이 발생한다.
페이지네이션으로 청크 처리하고, 중간 단계에서 열·행을 최소화한다. 불필요한 Table.Buffer 사용을 피한다.