이 글의 목적은 엑셀 Power Query에서 JSON 파일 불러오기가 실패할 때 발생 원인을 체계적으로 진단하고, M 코드 수정·데이터 정리·연결 설정 변경을 통해 문제를 재현 없이 해결하도록 돕는 것이다.
1. 문제 현상 빠른 진단 체크리스트
Power Query에서 JSON을 불러올 때 가장 흔한 실패 패턴을 표로 정리한다.
| 증상 메시지(예시) | 주요 원인 | 즉시 점검 항목 |
|---|---|---|
| Expression.Error: We couldn't parse the input이다. | JSON 문법 오류 또는 파일 인코딩 문제이다. | UTF-8 유효성, 중괄호·대괄호 짝, 문자열 따옴표를 확인한다. |
| The document root must not be followed by other values이다. | 루트에 여러 JSON 오브젝트가 연속 배치되었다. | JSON Lines 형식인지 확인하고 변환 규칙을 적용한다. |
| We couldn't convert to Table이다. | 레코드/리스트 계층이 표로 바로 변환 불가하다. | Record.ToTable 또는 Table.FromList로 단계적 변환을 수행한다. |
| Unexpected character at position n이다. | 제어문자, BOM, 인용부호 누락, 이스케이프 미적용이다. | 불량 문자 제거 또는 올바른 이스케이프를 적용한다. |
| DataFormat.Error: Invalid character in the given encoding이다. | 파일 인코딩 선언 불일치이다. | Json.Document의 인코딩 파라미터 또는 BinaryEncoding을 명시한다. |
주의 : 동일 메시지라도 원인은 복수일 수 있으므로, 입력 데이터 구조와 M 코드 단계별 미리보기를 함께 확인해야 한다.
2. 표준 절차: 단계별 원인 진단
2.1 파일 전처리 점검
- 파일 인코딩을 UTF-8 또는 UTF-8 with BOM으로 일관화한다.
- CRLF 줄바꿈을 권장하며 혼합 개행이 있으면 통일한다.
- 탭·NULL 같은 제어문자를 제거한다.
- 루트가 단일 객체 또는 단일 배열인지 확인한다.
// PowerShell 예: UTF-8 재인코딩 샘플 Get-Content .\raw.json -Raw | Set-Content .\clean.json -Encoding utf8 주의 : JSON Lines(ndjson)는 각 줄이 독립 JSON 객체이므로, 표준 Json.Document로는 바로 테이블화가 되지 않는다.
2.2 Power Query 커넥터 선택 재검토
| 사용 상황 | 권장 커넥터 | 비고 |
|---|---|---|
| 로컬 또는 네트워크 공유 JSON 단일 파일 | 데이터 가져오기 > 파일 > JSON이다. | 단일 루트 구조에 적합하다. |
| 폴더 내 다수 JSON 일괄 결합 | 데이터 가져오기 > 폴더이다. | 샘플 파일 변환 쿼리 수정이 핵심이다. |
| REST API 응답(JSON) | 데이터 가져오기 > 웹이다. | Web.Contents 옵션과 인증 처리가 중요하다. |
3. M 코드 핵심 패턴으로 해결하기
3.1 로컬 JSON 단일 파일 안정 파싱
인코딩·BOM 이슈를 피하기 위해 BinaryEncoding을 명시한다.
let Source = Json.Document( File.Contents("C:\data\sample.json"), 65001 // UTF-8 코드 페이지 ), ToTable = if Value.Is(Source, type list) then Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) else if Value.Is(Source, type record) then Record.ToTable(Source) else error "지원하지 않는 루트 타입이다.", Expanded = if Table.HasColumns(ToTable, {"Value"}) then try Table.ExpandRecordColumn(ToTable, "Value", Record.FieldNames(ToTable{0}[Value])) otherwise ToTable else ToTable in Expanded 주의 : 루트가 리스트일 때와 레코드일 때 처리 로직이 다르므로 분기 처리를 명시하는 것이 안전하다.
3.2 JSON Lines(ndjson) 처리
각 줄을 개별 JSON으로 파싱한 뒤 테이블로 병합한다.
let Bin = File.Contents("C:\data\events.ndjson"), Txt = Text.FromBinary(Bin, BinaryEncoding.Base64, 65001), Lines = Lines.FromBinary(Bin, null, null, 65001), Parsed = List.Transform(Lines, each try Json.Document(Text.ToBinary(_, 65001)) otherwise null), Clean = List.RemoveNulls(Parsed), TableFromList = Table.FromList(Clean, Splitter.SplitByNothing(), {"json"}), Expand = let first = if Table.RowCount(TableFromList) > 0 then TableFromList{0}[json] else [], cols = if Value.Is(first, type record) then Record.FieldNames(first) else {"Value"} in if Value.Is(first, type record) then Table.ExpandRecordColumn(TableFromList, "json", cols) else Table.RenameColumns(TableFromList, {{"json","Value"}}) in Expand 주의 : Text.FromBinary 사용 시 인코딩을 생략하면 제어문자 오해석으로 파싱 실패가 발생할 수 있다.
3.3 폴더 내 다수 JSON 결합
샘플 파일 변환 쿼리를 일반화해야 형태가 다른 파일에서도 실패하지 않는다.
let Folder = Folder.Files("C:\data\json"), KeepJson = Table.SelectRows(Folder, each Text.EndsWith([Extension], ".json")), AddContent = Table.AddColumn(KeepJson, "Parsed", each Json.Document([Content], 65001)), ToRows = Table.TransformColumns(AddContent, {"Parsed", each if Value.Is(_, type record) then Record.ToTable(_) else _}), Expand = Table.ExpandTableColumn(ToRows, "Parsed", {"Name","Value"}, {"Key","Value"}), // 스키마 유연화: Value가 레코드면 확장, 리스트면 테이블화 Normalize = Table.TransformColumns(Expand, {"Value", each if Value.Is(_, type record) then Record.ToTable(_) else if Value.Is(_, type list) then Table.FromList(_, Splitter.SplitByNothing()) else _ }), Flatten = let cols = List.RemoveItems(Table.ColumnNames(Normalize), {"Content"}) in Table.SelectColumns(Normalize, cols) in Flatten 주의 : 폴더 결합 마법사에서 자동 생성된 샘플 변환에 특정 필드명을 하드코딩하면 일부 파일에서 확장 단계가 실패한다.
3.4 REST API JSON 안정 수집
쿼리 매개변수, 헤더, 페이징을 명시하고 형식 불일치를 방지한다.
let BaseUrl = "https://api.example.com/v1/items", Query = [page="1", size="100"], Headers = [Authorization="Bearer <TOKEN>", Accept="application/json"], Source = Json.Document( Web.Contents( BaseUrl, [ Query=Query, Headers=Headers, ManualStatusHandling={400,401,403,404,429,500}, Timeout=#duration(0,0,30,0) ] ) ), // 루트가 { data = list, next = text } 형태라고 가정 Data = Source[data], Tbl = Table.FromList(Data, Splitter.SplitByNothing(), {"row"}), Expand = Table.ExpandRecordColumn(Tbl, "row", Record.FieldNames(Tbl{0}[row])) in Expand 주의 : 429 또는 5xx 응답이 간헐적으로 반환되면 재시도 로직을 별도 함수로 감싸고 호출 간 대기 시간을 삽입한다.
4. 구조 불일치와 스키마 진화 대응
JSON 필드의 등장·소멸·형식 변경은 Power Query 단계에서 자주 실패를 유발한다.
- 필드 존재 여부를 먼저 검사하고 조건부 확장을 적용한다.
- 형식 변환은 테이블 최종 단계에서 일괄 수행한다.
- 스키마가 자주 바뀌면 ColumnProfiles를 사용하여 자동 필드 리스트를 생성한다.
let src = Json.Document(File.Contents("C:\data\variant.json"), 65001), toTbl = if Value.Is(src, type list) then Table.FromList(src, Splitter.SplitByNothing(), {"rec"}) else Record.ToTable(src), recTbl = if Table.HasColumns(toTbl, {"rec"}) then Table.ExpandRecordColumn(toTbl, "rec", Record.FieldNames(toTbl{0}[rec])) else toTbl, ensure = (t as table, c as text) as table => if List.Contains(Table.ColumnNames(t), c) then t else Table.AddColumn(t, c, each null), cols = {"id","name","price","tags"}, padded = List.Accumulate(cols, recTbl, (state, col) => @ensure(state, col)), types = Table.TransformColumnTypes(padded, {{"id", Int64.Type}, {"name", type text}, {"price", type number}, {"tags", type any}}) in types 주의 : 필수 열이 누락된 파일이 섞여 있으면, 열 추가 후 null을 채워 결합해야 오류를 방지한다.
5. 특수 오류 유형별 처방
| 오류 유형 | 원인 | 해결 절차 |
|---|---|---|
| BOM 관련 파싱 오류이다. | UTF-8 BOM 존재 또는 미존재로 인한 해석 차이이다. | Text.FromBinary에 65001을 명시하고, 필요 시 BOM 제거 파일로 교체한다. |
| 숫자·날짜 형식 혼재이다. | 동일 필드에 문자열과 숫자가 교차한다. | Table.TransformColumns로 try..otherwise를 적용하여 안전 변환한다. |
| 대용량 파일 메모리 부족이다. | 한 번에 전체 파싱으로 메모리 초과이다. | 폴더 분할, 스트리밍 라인 파싱, 필요한 필드만 선택을 수행한다. |
| 권한 또는 프록시 오류이다. | 사내 프록시, 인증 미설정이다. | 웹 커넥터 자격 증명과 프록시 정책을 확인하고 Web.Contents에 헤더를 추가한다. |
6. 안전한 형식 변환 템플릿
let src = ..., // 숫자 변환 numCols = {"qty","price"}, toNumber = (v) => try Number.From(v) otherwise null, step1 = Table.TransformColumns(src, List.Transform(numCols, each {_, toNumber, type number})), // 날짜 변환 toDate = (v) => try Date.From(v) otherwise null, step2 = Table.TransformColumns(step1, {{"order_date", toDate, type date}}) in step2 주의 : 형식 변환을 초기에 수행하면 이후 확장 단계에서 실패가 연쇄 발생한다. 마지막에 일괄 적용하는 것이 안전하다.
7. 중첩 구조(배열·레코드) 테이블화 전략
JSON의 계층을 한 단계씩 표로 평탄화한다.
// 1) 루트 리스트 Table.FromList(Source, Splitter.SplitByNothing(), {"col"}) // 2) 레코드 확장 Table.ExpandRecordColumn(tbl, "col", {"a","b","c"}) // 3) 리스트 내부 다시 테이블화 Table.ExpandListColumn(tbl, "items") 필드 이름이 동적으로 바뀌면 Record.FieldNames로 컬럼 목록을 계산한다.
let cols = Record.FieldNames(Tbl{0}[col]), expanded = Table.ExpandRecordColumn(Tbl, "col", cols) in expanded 8. 성능 최적화 핵심
- 단계별 미리보기는 비활성화하지 않지만, 쿼리 병합 전 필드 축소로 전송량을 줄인다.
- 필요 없는 열 제거를 상단 단계에 배치한다.
- Web.Contents는 상대경로와 Query를 사용하여 캐싱 효율을 높인다.
- 폴더 결합 시 샘플 변환 쿼리를 경량화한다.
// Web.Contents 캐시 친화 패턴 Web.Contents(BaseUrl, [RelativePath="v1/items", Query=[page="1", size="100"]]) 9. 재사용 가능한 함수 패턴
// JSON Lines를 테이블로 변환하는 함수 let FnJsonLinesToTable = (bin as binary, optional codepage as nullable number) as table => let cp = if codepage = null then 65001 else codepage, lines = Lines.FromBinary(bin, null, null, cp), parsed = List.Transform(lines, each try Json.Document(Text.ToBinary(_, cp)) otherwise null), clean = List.RemoveNulls(parsed), tbl = Table.FromList(clean, Splitter.SplitByNothing(), {"rec"}), cols = if Table.RowCount(tbl) > 0 and Value.Is(tbl{0}[rec], type record) then Record.FieldNames(tbl{0}[rec]) else {"Value"}, out = if List.Contains(cols, "Value") and List.Count(cols)=1 then Table.RenameColumns(tbl, {{"rec","Value"}}) else Table.ExpandRecordColumn(tbl, "rec", cols) in out in FnJsonLinesToTable 10. 실제 시나리오별 해법 레시피
10.1 루트에 여러 객체가 연속된 파일
중괄호로 묶인 단일 배열로 감싸거나, 라인 단위 파싱으로 해결한다.
// 파일 전처리 없이 라인 파싱 let bin = File.Contents("C:\data\multi.json"), result = @FnJsonLinesToTable(bin, 65001) in result 10.2 배열 안에 배열이 있는 중첩 케이스
let src = Json.Document(File.Contents("C:\data\nested.json"), 65001), toTbl = Table.FromList(src, Splitter.SplitByNothing(), {"lvl1"}), expand1 = Table.ExpandRecordColumn(toTbl, "lvl1", {"id","items"}), expandList = Table.ExpandListColumn(expand1, "items"), expand2 = Table.ExpandRecordColumn(expandList, "items", {"sku","qty","attrs"}), expand3 = Table.ExpandRecordColumn(expand2, "attrs", {"color","size"}) in expand3 10.3 필드 이름에 공백·특수문자 포함
// #"필드 이름" 구문을 사용한다. Table.RenameColumns(Source, {{"#" & "Total Price", "Total_Price"}}) 주의 : 공백·점·대괄호가 포함된 필드는 UI에서 자동으로 #""를 적용하나, 수동 편집 시 누락하면 단계가 실패한다.
11. 데이터 품질 규칙 세트
- 필수 키 누락 시 레코드를 폐기하지 말고 null로 보존한다.
- 숫자 문자열에 통화 기호·콤마가 섞이면 Number.FromText 대신 정규화 후 변환한다.
- 타임스탬프는 DateTimeZone.From로 표준화한다.
// 통화 문자열 정규화 CleanCurrency = (t as text) => let s = Text.Remove(t, {",","₩","$"," "}) in try Number.From(s) otherwise null; 12. 가져오기 실패를 예방하는 운영 팁
- JSON 스키마 버전 번호를 별도 필드로 포함하여 변화를 추적한다.
- 배포 전 대표 샘플 3종 이상으로 쿼리를 회귀 테스트한다.
- 폴더 결합 시 스키마 드리프트를 가정한 방어적 확장을 적용한다.
- 대용량은 파일을 기간·키 기준으로 분할하여 로드한다.
13. 오류 메시지별 빠른 해결 매핑표
| 오류 메시지 | 즉시 조치 | M 코드 포인트 |
|---|---|---|
| We couldn't parse the input이다. | 인코딩 65001 명시한다. | Json.Document(File.Contents(...), 65001) |
| The document root must not be followed by other values이다. | 라인 파서 사용 또는 배열로 래핑한다. | FnJsonLinesToTable 함수 적용한다. |
| We couldn't convert to Table이다. | 레코드/리스트 분기 후 변환한다. | Record.ToTable, Table.FromList |
| Invalid character in the given encoding이다. | 제어문자 제거, 인코딩 지정한다. | Text.FromBinary(..., 65001) |
| Access is denied 또는 403/401이다. | 인증·프록시 설정을 점검한다. | Web.Contents 헤더·자격 증명 |
14. 최종 검증: 단계별 미리보기 체크 포인트
- Source 단계에서 값 타입이 list 또는 record인지 확인한다.
- 각 Expand 단계 직후 열 수 증가와 누락 필드를 점검한다.
- 형식 변환 단계에서 오류 아이콘이 0개임을 확인한다.
- 새로 고침 시 외부 의존성(Web, 폴더 권한)이 유지되는지 테스트한다.
FAQ
JSON이 너무 커서 열리지 않을 때 어떻게 하나?
파일을 기간별로 분할하고 폴더 결합으로 처리하거나 JSON Lines 방식으로 저장하여 라인 단위 파싱을 적용한다.
특정 파일만 실패하는 이유는 무엇인가?
필드 누락·형식 불일치·루트 구조 차이로 인한 스키마 충돌 가능성이 높다. 조건부 확장과 try..otherwise를 적용하여 방어적으로 처리한다.
REST API에서 간헐적으로 실패한다. 어떻게 안정화하나?
ManualStatusHandling과 Timeout을 지정하고, 재시도 함수와 지수 백오프를 적용한다. 필요한 헤더와 인증 스코프를 명시한다.
한글이 깨져서 물음표로 보일 때 해결법은?
Json.Document와 Text.FromBinary에 65001을 명시하고, 원본이 EUC-KR이면 해당 코드 페이지 번호로 변환하여 재저장한다.
열 머리글이 매번 달라져 실패한다. 어떻게 대응하나?
Record.FieldNames로 동적 컬럼 목록을 계산하고, 누락 컬럼은 null로 채워 결합한다.