당연히 됩니다...64비트 엑셀인가요

안녕하세요~

올려주신 질문은 원본 데이터 구조, 쿼리 단계, PC 사양에 따라 여러 방향으로 접근할 수 있고,
첨부파일이 없어 정확한 원인 진단은 어렵기 때문에 일반적인 기준에서 설명드리겠습니다.

파워쿼리는 4만 행~10만 행 정도의 데이터 때문에 사용이 어려운 기능은 아니고
엑셀 시트로 결과를 불러올 경우에는 엑셀 워크시트 자체 한계인 1,048,576행까지 출력할 수 있는데,
다만 Excel 2016(64비트)에서는 PC 메모리, 원본 데이터 구조, 그리고 쿼리 변환 방식에 따라 속도 차이가 크게 날 수 있습니다.

파워쿼리는 기본적으로 데이터를 한 번에 모두 메모리에 올려놓고 처리한다기보다는,
가능한 경우 행 단위로 데이터를 흘려보내며 처리하는 Streaming 방식에 가까운데
그런데 중간에 전체 데이터를 한 번에 확인해야 하는 작업이 들어가면,
이 흐름이 끊기고 메모리에 데이터를 쌓아야 하므로 속도가 크게 느려질 수 있습니다.

대표적으로 아래 작업들이 비교적 무거운 작업으로
- 정렬
- 그룹화
- 병합
- 여러 파일 결합
- 인덱스 열 추가
- 행 수 계산
- 중복 제거
- 사용자 지정 열 추가
- 텍스트 분할/열 병합

특히 정렬, 그룹화, 행 수 계산처럼 전체 데이터를 확인해야 다음 단계로 넘어갈 수 있는 작업은
대용량 데이터에서 체감 속도를 많이 떨어뜨릴 수 있습니다.

반대로 아래 작업들은 상대적으로 가벼운 편인데,
- 필요한 열만 선택
- 불필요한 행 필터링
- 단순 조건문
- 필요한 열의 데이터 형식 지정

따라서 4만~10만 행에서 문제가 발생한다면,
데이터 양 자체보다는 쿼리 설계나 원본 데이터 구조의 문제일 가능성이 높습니다.

그래서 아래 순서로 쿼리를 구성하는 것을 권하는데,
1. 필요한 열만 먼저 남기기
2. 불필요한 행을 초반에 필터링하기
3. 필요한 열에만 데이터 형식을 명시적으로 지정하기
4. 병합이나 조인은 가능한 한 줄어든 데이터 기준으로 수행하기
5. 정렬, 그룹화, 인덱스 추가 같은 무거운 작업은 최대한 마지막에 처리하기

이것을 흔히 Early Reduction, 즉 초반에 데이터 부피를 줄이는 방식으로
엔진이 읽고 처리해야 할 데이터의 절대량을 먼저 줄이면 이후 단계의 부담이 훨씬 줄어들게 됩니다.

결과 행 수가 많다면 시트에 바로 불러오기보다는
“연결만 만들기” 또는 “데이터 모델에 추가” 방식도 검토해 볼 수 있습니다.

또한 여러 원본을 결합하는 쿼리라면 개인 정보 보호 수준 때문에 속도가 느려질 수 있어
신뢰할 수 있는 데이터라면 아래 옵션을 검토해 볼 수 있습니다.

쿼리 옵션 > 현재 통합 문서 > 개인 정보 보호 > 개인 정보 보호 수준 무시

그리고 아울러 쿼리 속성에서 “백그라운드 새로 고침 사용”을 해제하면,
새로 고침 중 다른 작업은 어렵지만 처리 안정성이나 체감 속도가 좋아지는 경우도 있습니다.

결론적으로 4만~10만 행은 파워쿼리로 충분히 처리 가능한 범위입니다.
다만 Excel 2016 환경에서는 PC 사양, 쿼리 단계 구성에 따라 성능 차이가 크므로,
행 수보다는 쿼리 구조를 먼저 점검해 보시는 것이 좋습니다.

MS사이트 찾아보면,  더많은 데이터도 되지만,
시트104만행도 당연가능하다고 나옵니다.

혹 처리량이 많다면 오래걸릴수도...있을듯해서
간단한로직으로 test볼려보시면 좋을듯합니다.

https://support.microsoft.com/ko-kr/office/excel에서-사양-및-제한-power-query-5fb2807c-1b16-4257-aa5b-6793f051a9f4

아래내용도 찾아보았는데, 도움되시길바랍니다.
버젼간 차이가 있는지는 모르겠네요.

1. 파워쿼리 수행 상태 확인 방법
   쿼리가 멈춘것인지, 돌고있는지 확인시 다음을 체크하세요.

• 상태 표시줄(엑셀 창 하단): 데이터 로드 중에는 엑셀 창 오른쪽 하단 상태 표시줄에 "데이터를 가져오는 중..." 또는 "쿼리 실행 중..."이라는 메시지와 함께 로드된 행수가 올라가는 것을 볼 수 있습니다.
• 쿼리 및 연결 창: '데이터' 탭 > '쿼리 및 연결'을 클릭하면 우측에 창이 뜹니다. 실행 쿼리이름 옆에 회전하는 아이콘이 표시되거나 로딩메시지가 나타납니다.
• 작업 관리자 확인: Ctrl + Shift + Esc를 눌러 작업 관리자를 연 뒤, 'Microsoft Mashup Evaluation Container'라는 프로세스가 CPU나 메모리를 점유하고 있다면 파워쿼리가 배경에서 열심히 데이터를 연산하고 있는 상태입니다.

 
 2. 10만 행 작업이 느려지는 이유와 해결책

데이터양 보다는 '쿼리 단계의 복잡도' 때문일 가능성이 큽니다.

• 데이터 형식지정: 각 단계에서 '데이터형식 변경' 자주 일어나면 느려집니다.  형식을 지정하는 단계는 쿼리 마지막에 한 번만 몰아서 하는것이 좋습니다.
• 중복된 병합(Merge): 여러 테이블을 병합할 때, 데이터가 늘어나면서 연산량이 많이 늘어날 수 있습니다.
  병합 전 '행 필터링'통해 데이터 크기 먼저 줄이세요.
• 배경 새로고침 끄기: [데이터]->[쿼리 및 연결]->해당 쿼리 우클릭-> [속성]에서 '백그라운드 새로 고침 사용' 체크해제!
  쿼리에 집중시켜 처리속도 빨라집니다.
• 64비트 엑셀 권장:  32비트 사용중이면 메모리제한(약 2~4GB)으로 대용량 처리가 매우 느려질 수 있습니다.