Excel의 Power Query: 데이터 가져오기, 정리, 병합 및 새로 고침
By Mirko Peters · · Updated
Excel의 Power Query는 수식 없이 데이터를 가져오고 정리합니다. 위치를 찾는 방법, From Table/Range, 적용된 단계, 병합과 추가의 차이, 로드와 새로 고침을 알아봅니다.
Automatically translated with AI from the original post. Read the English original
Excel의 Power Query는 데이터 탭의 데이터 가져오기 및 변환 그룹에 있는 도구로, 데이터에 연결하고 정리하여 모양을 바꾼 뒤 결과를 워크시트에 로드합니다. 클릭한 단계는 기록되었다가 새로 고침할 때마다 다시 실행되므로, 매월 하는 정리 작업을 한 번의 클릭으로 끝낼 수 있습니다. 이 글은 매주 내보낸 파일을 복사하고 붙여넣고 수정하는 작업을 그만두고 싶은 Excel 사용자를 위한 것입니다. 시작 위치, 테이블 정리 방법, 추가와 병합, 로드와 새로 고침, 그리고 Mac과 웹에서의 제한 사항을 다룹니다.
핵심 포인트
- Excel의 Power Query는 데이터 탭의 '데이터 가져오기 및 변환' 그룹에 있습니다. 데이터에 연결하고, 형태를 다듬은 다음, 워크시트 또는 데이터 모델에 로드합니다.
- 수행한 모든 변경 사항은 '적용된 단계'에 단계로 기록됩니다. 원본 데이터는 변경되지 않으며, 새로 고칠 때마다 these 단계가 다시 실행됩니다.
- 추가는 동일한 열을 가진 테이블의 행을 쌓습니다. 병합은 일치하는 키 열을 기준으로 두 테이블을 연결하며, 일치하는 열의 데이터 형식이 같아야 합니다.
- 데이터, 모두 새로 고침을 선택하여 쿼리를 다시 실행합니다. '연결 속성'에서는 일정 간격으로 또는 파일을 열 때 새로 고치도록 설정할 수 있습니다.
- Excel for Mac과 Excel for the web은 Power Query의 일부 기능을 지원하며, 각 환경에서 작성할 수 있는 항목에 대한 Microsoft의 설명이 다르므로 사용 중인 빌드에서 직접 확인하세요.
Microsoft는 Power Query 작업을 네 단계로 설명합니다. 데이터에 연결하고, 변환하고, 다른 원본과 결합하고, 로드하는 것입니다. 이 과정 전체에서 원본은 변경되지 않습니다. 이 마지막 점 덕분에 여러 가지를 시험해 보기에 안전합니다. 시작한 내보내기 파일을 망가뜨릴 염려가 없습니다.
Power Query가 무엇이며 어디에서 찾을 수 있는지
Power Query는 그래픽 편집기를 갖춘 데이터 준비 엔진입니다. Power Query란 무엇인가에 대한 Microsoft 페이지에 따르면, 이 엔진은 추출, 변환, 로드 작업을 수행하며 자체 저장소가 없어 대상은 호스트 제품에 맡깁니다. Excel은 그중 하나의 호스트입니다. Power BI Desktop도 또 다른 호스트이며, 두 제품은 핵심 편집기를 공유합니다. 커넥터와 대상은 호스트에 따라 다를 수 있으므로, 한 제품에서 본 쿼리가 다른 제품에서도 똑같이 보일 것이라고 가정하지 마세요.
Power Query가 있는 위치는 Excel 버전에 따라 다릅니다. Microsoft의 Excel의 Power Query 개요에 따르면 다음과 같습니다.
- Windows용 Excel 2016, 2019 및 Microsoft 365. 기본으로 포함되어 있습니다. 데이터 탭으로 이동하여 데이터 가져오기 및 변환 그룹을 확인하세요.
- Windows용 Excel 2010 및 2013. Power Query는 자체 리본 탭이 있는 무료 추가 기능이었습니다. Microsoft는 이 기능이 지원 중단되었다고 밝혔지만, 여전히 작동할 수도 있습니다.
- Mac용 Excel. 여러 원본에서의 새로 고침을 지원합니다. Mac용 Excel 2016 및 2019에서는 사용할 수 없습니다.
- 웹용 Excel. 일부만 지원됩니다. 자세한 내용은 아래에서 설명합니다.
Windows에서는 Microsoft가 .NET Framework 4.7.2 이상과 웹 커넥터용 WebView2 Runtime을 요구 사항으로 안내합니다. 업무용 PC에서 명령이 보이지 않는다면 기능이 제거되었다고 단정하기 전에 빌드가 최신인지 IT 부서에 확인하십시오.
Excel에서 Power Query를 사용하는 방법: 첫 번째 쿼리
가장 쉽게 시작하는 방법은 통합 문서에 이미 있는 데이터를 사용하는 것입니다.
- Excel 표의 아무 셀이나 클릭합니다. 데이터가 일반 범위라면 먼저 Ctrl+T를 눌러 표로 변환하십시오.
- 데이터 > 데이터 가져오기 및 변환 > 표/범위에서를 선택합니다.
- Power Query 편집기가 열리고 데이터 미리 보기가 표시됩니다.
- 필요한 변경 작업을 수행합니다(다음 섹션 참조).
- 홈 > 닫고 로드를 선택합니다.
Microsoft의 Excel 표에서 가져오기 문서에 따르면 결과는 기본적으로 새 워크시트에 표시되며 원본 시트는 변경되지 않습니다. 다른 대상을 원하면 로드 위치를 사용하십시오. 원본과 혼동되지 않도록 새 시트의 이름을 바꿔 두십시오.
다른 원본에 연결하려면 데이터 > 데이터 가져오기에서 파일, 데이터베이스, 온라인 서비스, 웹 등 원본을 선택합니다. 선택하면 미리 보기가 나타나고 로드(Excel로 바로 로드)와 데이터 변환(먼저 편집기 열기) 중 하나를 고를 수 있습니다. 제 권장은 데이터가 깨끗해 보이더라도 거의 항상 데이터 변환을 선택하는 것입니다. 열 형식을 설정하는 곳이 편집기이며, 잘못된 형식이 이후 새로 고침 오류의 대부분을 일으키기 때문입니다.
빈 상태에서 시작하려면 같은 문서에서 데이터 > 데이터 가져오기 > 기타 원본 > 빈 쿼리를 안내하며, 데이터 가져오기 아래의 Power Query 편집기 시작도 있습니다.
쉽게 풀어 본 편집기
- 데이터 미리 보기에는 표가 표시되며 변경할 때마다 업데이트됩니다.
- 쿼리 설정 창에는 적용된 단계가 나열됩니다. 수행하는 작업마다 원본이나 변경된 형식 같은 단계가 하나씩 추가됩니다. 단계를 선택하면 그 시점의 데이터를 볼 수 있고, 단계를 수정하거나 삭제할 수도 있습니다.
- 쿼리 창에는 통합 문서의 모든 쿼리가 나열됩니다. 미리 보기 옆의 화살표로 열 수 있습니다.
- 고급 편집기에서는 Power Query M 언어로 작성된 기본 스크립트를 보고 편집할 수 있습니다. Microsoft는 인터페이스에서 만든 모든 단계가 M으로 기록된다고 설명합니다. 이 도구의 가치를 얻기 위해 스크립트를 직접 건드릴 필요는 없습니다.
단계로 데이터 정리 및 재구성하기
편집기에서 흔히 하는 정리 작업은 행 필터링, 열 제거, 데이터 형식 변경, 열 분할, 값 바꾸기입니다. 편집기는 이런 작업을 각각 단계로 기록합니다. 명령은 편집기의 리본 메뉴와 열 머리글을 마우스 오른쪽 단추로 클릭할 때 나타나는 메뉴에 있으며, 열 형식에 따라 보이는 항목이 달라지므로 리본 메뉴를 찾아보기 전에 머리글 메뉴를 먼저 확인하십시오.
대부분의 내보내기 파일에 잘 맞는 작업 순서는 다음과 같습니다.
- 형식을 먼저 설정합니다. 각 열 머리글 옆의 형식 아이콘을 확인하십시오. 날짜, 숫자, 텍스트는 이후 단계와 병합에서 서로 다르게 동작합니다.
- 필요 없는 것은 제거합니다. 열이 적을수록 결과가 작고 빨라지며, 원본이 바뀔 때 생기는 문제도 줄어듭니다.
- 행을 필터링합니다. 열 머리글의 화살표를 사용해 빈 값이나 원하지 않는 값을 제거하십시오.
- 텍스트를 수정합니다. 오타와 오래된 코드를 바로잡고, 공백을 정리하거나, 정보 두 개가 들어 있는 열을 분할하십시오.
- 나중에 쉽게 찾을 수 있도록 쿼리 설정 창에서 쿼리 이름을 바꿔 두십시오.
적용된 단계는 목록이므로 처음부터 다시 시작하지 않고도 이전의 실수를 고칠 수 있습니다. 원본이 바뀐 뒤 단계에서 오류가 나면 오류가 처음 발생한 단계를 확인하십시오. 대개 이름이나 형식이 바뀐 열을 가리킵니다.
이름이 바뀐 열에 대한 주의 사항입니다. Microsoft의 새로 고침 문서에 따르면 새 원본 열은 원본 단계를 검토한 뒤에야 Power Query에 나타나며, 미리 보기가 오래되었을 수 있다는 노란색 막대가 보이면 로컬 캐시를 새로 고쳐야 합니다. 새 열이 보이지 않는다면 이 두 가지를 먼저 확인하십시오.
데이터 결합: 추가와 병합
이 두 명령은 하는 일이 다르지만 종종 혼동됩니다.
Append는 행을 쌓아 올리는 방식입니다. 같은 형식의 표가 여러 개 있을 때, 예를 들어 월별로 내보낸 데이터를 하나로 합치고 싶을 때 사용합니다. Microsoft는 append를 한 쿼리의 행을 다른 쿼리 아래에 이어 붙이는 작업으로 설명합니다. 두 가지 방식이 있습니다. 인라인 append는 기존 쿼리를 기반으로 작업하고, 중간 append는 실행할 때마다 새 쿼리를 만듭니다.
Merge는 일치하는 값을 기준으로 두 표를 옆으로 나란히 결합합니다. 조회(lookup)와 비슷한 개념입니다. 단계별 절차는 Microsoft의 쿼리 병합 문서에 나와 있습니다.
- 일치 기준이 될 열이 있는 쿼리를 최소 두 개 준비합니다. 일치시킬 열은 텍스트나 숫자처럼 데이터 형식이 같아야 합니다.
- 편집기에서 홈 > 쿼리 병합을 선택합니다. 결과는 현재 쿼리의 새 단계로 추가됩니다. 별도의 쿼리로 만들고 싶다면 쿼리 병합 옆의 화살표를 열고 새 쿼리로 쿼리 병합을 선택합니다.
- 기본 테이블과 키 열을 선택한 다음 관련 테이블과 키 열을 선택합니다. 키 열이 여러 개라면 두 테이블에서 같은 순서로 선택합니다.
- 대화 상자에 표시되는 일치 개수를 확인합니다. 예상보다 적다면 키 열의 철자, 공백 또는 형식이 다를 가능성이 큽니다.
- 조인 종류(아래 참조)를 선택하고 확인을 누릅니다.
- 새 열이 나타납니다. 해당 머리글 옆의 확장 아이콘을 선택하고, 추가하려는 관련 열을 체크합니다.
Microsoft가 정리한 조인 종류는 다음과 같습니다.
- 왼쪽 외부는 기본 테이블의 모든 행과 관련 테이블에서 일치하는 행을 유지합니다.
- 오른쪽 외부는 관련 테이블의 모든 행과 기본 테이블에서 일치하는 행을 유지합니다.
- 전체 외부는 두 테이블의 모든 행을 유지합니다.
- 내부는 양쪽에서 모두 일치하는 행만 유지합니다.
- 왼쪽 안티는 일치하는 항목이 없는 기본 테이블의 행을 유지합니다. 오른쪽 안티는 일치하는 항목이 없는 관련 테이블의 행을 유지합니다.
- 교차는 기본 테이블의 모든 행을 관련 테이블의 모든 행과 짝지어 줍니다.
어느 테이블을 먼저 선택했는지에 따라 왼쪽과 오른쪽의 의미가 달라지므로 테이블 순서가 중요합니다. 퍼지 일치를 사용하여 병합 수행 확인란을 선택하면 근사값이 일치하는 행을 찾으며, Microsoft에 따르면 이 기능은 텍스트 열에서만 작동합니다. 이전에 조회 함수를 주로 사용했다면, 왼쪽 외부 조인이 가장 비슷한 방식입니다. 그리고 왼쪽 안티 조인은 고객 정보가 없는 주문처럼 일치하는 항목이 없는 행을 빠르게 찾는 데 유용합니다.
Microsoft는 개인 정보 수준도 언급합니다. Power Query는 각 원본을 Public, Organizational 또는 Private으로 분류하고, 이를 기준으로 개인 정보가 다른 원본에 노출될 수 있는 방식의 데이터 결합을 막습니다. 병합 과정에서 개인 정보 수준에 관한 메시지가 표시되면, 확인을 누르기 전에 내용을 꼼꼼히 읽어 보시기 바랍니다.
쿼리 로드 및 새로 고침
홈 > 닫기 및 로드를 선택하여 편집기를 닫습니다. 쿼리 만들기, 로드 및 편집에 관한 Microsoft 문서에는 다음과 같은 대상이 설명되어 있습니다.
- 닫기 및 로드는 결과를 워크시트로 보냅니다.
- 닫기 및 로드 위치 지정을 사용하면 데이터 모델과 같은 다른 대상을 선택할 수 있습니다. 여러 테이블을 기반으로 피벗 테이블을 만들 계획이라면 가져오기 대화 상자에서 이 데이터를 데이터 모델에 추가를 선택합니다.
- Load To가 비활성화되어 있으면 Close & Load를 사용한 뒤, 데이터 > 쿼리 및 연결으로 이동합니다. 쿼리 탭에서 해당 쿼리를 마우스 오른쪽 버튼으로 클릭하고 Load To를 선택합니다.
기본값을 변경하려면 파일 > 옵션 및 설정 > 쿼리 옵션 > 데이터 로드로 이동합니다. 표준 로드 설정을 사용하거나, 워크시트 또는 데이터 모델에 로드하도록 사용자 지정 기본값을 설정할 수 있습니다.
나중에 쿼리를 편집하려면 데이터 영역의 셀을 선택하고 쿼리 > 편집을 선택하거나, 쿼리 및 연결에서 해당 쿼리를 마우스 오른쪽 단추로 클릭하고 편집을 선택합니다.
새로 고침
새로 고침을 실행하면 현재 원본을 기준으로 모든 단계가 다시 실행됩니다. Microsoft의 새로 고침 문서에 나온 방법은 다음과 같습니다.
- 데이터 > 모두 새로 고침을 선택하면 통합 문서의 모든 항목이 업데이트됩니다(Ctrl+Alt+F5). 옆의 화살표를 누르면 선택한 항목만 새로 고치는 새로 고침을 사용할 수 있습니다(Alt+F5). Esc를 누르면 새로 고침이 중지됩니다.
- 자동 새로 고침을 설정하려면 데이터 > 쿼리 및 연결으로 이동하여 연결 탭을 연 다음, 연결을 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다. 사용 정보 탭에서 다음마다 새로 고침(분 단위)과 파일을 열 때 데이터 새로 고침을 설정할 수 있습니다.
- 같은 탭의 백그라운드에서 새로 고침 사용을 선택하면 쿼리가 실행되는 동안에도 다른 작업을 계속할 수 있습니다. OLAP 쿼리나 데이터 모델에 데이터를 공급하는 연결에는 이 옵션을 사용할 수 없습니다.
저장된 암호는 암호화되지 않으며 Microsoft도 저장하지 않을 것을 권장합니다. 정의 탭에서 암호 저장의 선택을 해제하면 Excel이 세션당 한 번만 암호를 묻습니다.
통합 문서를 다른 사람에게 보내는 경우, 받는 사람에게도 원본 데이터에 대한 액세스 권한이 필요할 수 있습니다. 내 드라이브의 파일이나 받는 사람이 열 수 없는 사이트를 읽는 쿼리는 상대방 컴퓨터에서 실패합니다. 데이터 원본을 공유 위치에 두거나, 로드된 결과를 값으로 보내십시오.
Mac 및 Excel 웹에서의 Power Query
두 플랫폼은 이 도구의 일부 기능을 지원하지만, Microsoft의 문서 내용이 완전히 일치하지는 않습니다.
Excel 웹. Microsoft 문서에 따르면 Excel 통합 문서, Text/CSV, XML, JSON, SQL Server, SharePoint Online 목록, OData 등의 원본에서 가져올 수 있으며, 빈 테이블이나 빈 쿼리도 만들 수 있습니다. Power Query 편집기를 열고(데이터 > 데이터 가져오기 > Power Query 편집기 시작), 닫고 로드로 로드하며, 데이터 > 모두 새로 고침으로 새로 고칠 수 있습니다. 쿼리 보기와 새로 고침은 모든 Microsoft 365 구독자가 사용할 수 있지만, 더 많은 기능은 비즈니스 플랜 또는 엔터프라이즈 플랜(E3 이상)이 필요합니다. 일부 기능은 브라우저에서 타사 쿠키를 허용해야 합니다.
Mac용 Excel. Microsoft 문서의 한 페이지에서는 데이터 > 데이터 가져오기 > 데이터 가져오기(Power Query)로 시작하고, 쿼리 형태 변경에는 Microsoft 365와 버전 16.69 이상이 필요하며, 데이터 변환을 선택하면 편집기가 열린다고 설명합니다. 같은 페이지의 별도 섹션에서는 Mac용 Excel에서는 편집기에서 쿼리를 작성할 수 없다고 명시하고, Windows에서는 VBA로 쿼리를 작성한 뒤 Mac에서 모듈을 가져오는 방식을 설명합니다. 두 설명은 서로 맞지 않아 저는 이를 정리할 수 없었습니다. 따라서 이 방식을 기반으로 프로세스를 구축하기 전에 본인의 Mac에서 직접 테스트해 보십시오. SQL Server 가져오기는 Insiders Beta에서만 제공되는 것으로 안내되어 있습니다.
Power Query로 충분하지 않은 경우
분명히 말씀드리면, Power Query는 데이터를 준비하기 위한 도구이지 데이터를 저장하기 위한 도구가 아닙니다. Microsoft에 따르면 결과가 저장되는 위치는 호스트 제품이 결정하며, Excel에서는 워크시트 또는 데이터 모델이 됩니다. 워크시트에는 행 수 제한이 고정되어 있으므로 매우 큰 쿼리 결과는 시트에 들어가지 않습니다. 이 경우에는 데이터 모델에 로드하거나, 작업을 데이터베이스나 전용 파이프라인으로 옮기십시오.
알아두면 좋은 다른 제한 사항은 다음과 같습니다.
- 쿼리는 실행될 때 원본을 읽습니다. 원본의 변경 사항을 통합 문서에 실시간으로 반영하지는 않습니다.
- 공동 작업은 불편합니다. 공유 통합 문서에서 두 사람이 같은 쿼리를 동시에 편집하는 것은 좋지 않습니다. 담당자를 한 명으로 정하고, 쿼리에 명확한 이름을 붙이십시오.
- 규제 대상 데이터를 다루는 경우, 통합 문서는 쉽게 복사될 수 있다는 점을 기억하십시오. 이러한 파일을 저장할 수 있는 위치에 대해서는 조직의 규칙을 따르십시오.
흔한 실수
- 데이터 형식을 확인하지 않고, 병합 결과가 일치하지 않는 이유를 궁금해하는 것.
- 형식이 다르거나 끝에 공백이 있는 열을 기준으로 병합하는 것.
- 추가를 의도했는데 병합을 사용하거나, 그 반대로 하는 것.
- 연결 전용 쿼리나 데이터 모델이 더 适합한데도 모든 항목을 워크시트에 로드하는 것.
- 연결에 원본 암호를 저장하는 것.
- 보고서를 보내기 전에 새로 고침을 잊는 것.
- 원본 파일을 옮긴 뒤 쿼리가 실패하는 이유를 이해하지 못하는 것.
함께 살펴볼 관련 주제
- 편집기에서 중복 제거. Power Query는 리본 메뉴의 일회성 명령과 달리, 단계로 표의 중복을 제거할 수 있으며 이 단계는 반복해서 실행할 수 있습니다.
- 열 피벗 해제. 피벗 테이블을 만들기 전에 월별 열을 행으로 바꾸십시오. Microsoft도 바로 이 작업에 Power Query를 사용하도록 안내합니다.
- 매개 변수. Microsoft에는 서로 다른 입력으로 쿼리를 재사용하기 위한 매개 변수 쿼리 페이지가 있습니다.
- M 언어. 고급 편집기에서 스크립트를 읽는 방법을 알아보세요. 먼저 Power Query M 페이지부터 시작하면 좋습니다.
- Power BI. 데이터가 Excel의 한계를 넘어설 때, Power BI Desktop에서 같은 편집기가 어떻게 작동하는지 알아보세요.
다음 단계
매주 손으로 정리하는 내보내기 파일을 하나 고르세요. 이를 표로 변환하고 표/범위에서를 실행한 뒤 열 형식을 설정하고 새 시트에 로드합니다. 다음 주에는 새 내보내기 파일을 표에 붙여 넣고 모두 새로 고침을 누르세요. 이것이 바로 Excel의 Power Query가 제 역할을 하는 모습입니다.
데이터 원본
- Excel의 Power Query 정보(Microsoft 지원)
- Power Query란? (Microsoft Learn)
- Excel 표에서 가져오기(Microsoft 지원)
- Excel에서 쿼리 만들기, 로드 또는 편집(Power Query)(Microsoft 지원)
- 쿼리 추가(Power Query)(Microsoft 지원)
- 쿼리 병합(Power Query)(Microsoft 지원)
- Excel에서 외부 데이터 연결 새로 고침(Microsoft 지원)
- Excel 웹에서 Power Query 사용(Microsoft 지원)
- Mac용 Excel에서 데이터 가져오기 및 모양 지정(Power Query)(Microsoft 지원)