[파워쿼리] 데이터 행이 너무 많아 로드가 안될 때 해결방법: 연결만 만들기, 피벗테이블로 만들기

이번 글에서는 파워쿼리의 데이터 행이 너무 많아서 엑셀 로드 시 오류가 발생하는 원인과 해결방법에 대해 소개하겠습니다.

 

데이터 행이 너무 많을 경우(행 1,048,576개 초과) 문제점

 

▲ 엑셀은 최대 1,048,576개 행을 로드할 수 있습니다. 따라서 만약에 파워쿼리에서 작업한 데이터가 1,048,576행을 초과하면 데이터를 로드할 때 오류가 발생합니다

 

▲ 데이터 행이 너무 많을 시 위의 사진처럼 팝업창이 뜨고, '확인'을 누를 시 1,048,576개 행까지만 로드가 됩니다. 

위의 안내 메시지처럼 쿼리를 편집하여 데이터 행을 줄이는 방법도 있습니다. 하지만 1) 연결만 만들기 혹은 2) 피벗테이블로 만들기(엑셀 2016 버전에서는 지원되지 않음)로 데이터 행을 줄이지 않고 문제를 해결할 수 있습니다.

 

 

방법 #1.  쿼리 데이터 '연결만 만들기' 이후 피벗 테이블 생성

 

▲ 행 개수가 1,048576개를 초과하는 데이터를 만들기 위해 위의 사진처럼 행 끝까지 각각 1, 2로 채워진 표를 파워쿼리로 합쳐보겠습니다. 합쳐진 파워쿼리는 '연결'입니다. 

 

[파워쿼리] 쿼리추가로 여러 데이터를 하나의 표로 합치기

▲ 쿼리를 세로로 합치는 방법

 

 

1) 닫기 및 로드 > 닫기 및 다음으로 로드 연결만 만들기 

▲ 행이 너무 많은 쿼리를 선택하고, '닫기 및 로드 > 닫기 및 다음으로 로드'를 클릭해 줍니다. 이후 우측의 사진처럼 '연결만 만들기'를 선택하고 확인 버튼을 클릭합니다.

 

▲ 그 결과, 우측 탭을 보면 '연결'이라는 쿼리 설명에 '연결 전용입니다'라는 문구가 표시됩니다.

 

2) 연결 전용인 쿼리를 피벗 테이블 데이터 원본으로 만들기

▲ '삽입 탭 > 피벗 테이블 > 외부 데이터 원본에서'를 클릭하면 왼쪽 사진처럼 '외부 원본의 피벗 테이블'이라는 팝업창이 생성됩니다. 팝업창에서 '연결 선택'을 클릭하면 오른쪽의 사진처럼 쿼리 목록이 나옵니다. 이후 숫자 표를 합친 '연결' 쿼리를 선택합니다. 

 

▲ 그 결과, 피벗 테이블이 생성되고 위의 사진처럼 피벗 테이블 필드를 설정하여 데이터를 요할 수 있습니다. 원하는 분류의 피벗 테이블 숫자를 더블클릭하면 해당 수치에 대한 로데이터를 로드하여 볼 수 있게 됩니다. 

 

 

728x90

 

 

방법 #2. 피벗테이블로 만들기 (Excel 2016 미지원)

 

저도 이번 글을 쓰면서 알게된 사실인데요. MS 365 버전 엑셀에서는 '연결만 만들기' 과정을 제외하고, 쿼리를 바로 피벗 테이블 원본으로 만들 수 있습니다. 

 

▲ 파워쿼리 편집창에서 '닫기 및 로드 > 닫기 및 다음으로 로드' 후  '데이터 가져오기' 창에서 '피벗 테이블 보고서'를 클릭하면 오른쪽의 사진처럼 피벗 테이블이 생성됩니다. 

 

정리하자면, 데이터 행이 너무 많은 경우 쿼리를 '연결만 만들기' 이후 피벗 테이블 원본으로 설정해서 로드 오류가 발생하는 문제를 해결할 수 있습니다. 그리고 만약에 최신 버전의 엑셀을 사용하시면, '연결만 만들기' 과정을 생략하고 바로 피벗 테이블을 만들 수 있습니다.