매일 반복되는 데이터 수집 작업으로 소중한 시간을 낭비하고 계신가요? 엑셀의 파워 쿼리 기능을 활용하면 여러 파일에서 데이터를 가져오고 정리하는 과정을 자동화할 수 있습니다. 파워 쿼리는 코딩 지식 없이도 복잡한 데이터 처리를 가능하게 하는 강력한 도구입니다. 이 글에서는 파워 쿼리의 기본 개념부터 실무에서 바로 활용할 수 있는 데이터 통합 방법, 그리고 새로고침만으로 최신 데이터를 자동으로 업데이트하는 자동 갱신 설정까지 상세히 알아보겠습니다. 데이터 분석 업무의 효율성을 획기적으로 높이고 싶다면 지금부터 소개하는 내용을 주목해주시기 바랍니다.
파워 쿼리란 무엇이며 왜 필요한가
파워 쿼리는 마이크로소프트 엑셀에 내장된 데이터 연결 및 변환 도구입니다. 엑셀 2016 이상 버전에서는 기본 기능으로 제공되며, '데이터' 탭의 '데이터 가져오기 및 변환' 그룹에서 찾을 수 있습니다. 이 기능의 가장 큰 장점은 데이터의 원본을 건드리지 않고도 원하는 형태로 가공할 수 있다는 점입니다. 예를 들어 여러 지점의 판매 데이터가 각각 다른 엑셀 파일로 저장되어 있을 때, 수작업으로 복사하고 붙여넣는 대신 파워 쿼리를 사용하면 클릭 몇 번으로 모든 데이터를 통합할 수 있습니다.
파워 쿼리의 핵심은 '쿼리'라는 개념입니다. 쿼리는 데이터를 가져오고 변환하는 일련의 단계를 기록한 것으로, 마치 요리 레시피처럼 한 번 만들어두면 재료만 바뀌어도 동일한 과정을 반복 실행할 수 있습니다. 이러한 특성 덕분에 매주 또는 매달 동일한 형식의 데이터를 처리해야 하는 경우 처음 한 번만 설정해두면 이후에는 새로고침 버튼만 누르면 됩니다. 특히 CSV 파일, 텍스트 파일, 데이터베이스, 웹 페이지 등 다양한 소스에서 데이터를 가져올 수 있어 활용 범위가 넓습니다.
실무에서 파워 쿼리가 필요한 이유는 명확합니다. 첫째, 시간 절약입니다. 수십 개의 파일을 수동으로 합치는 작업이 몇 분으로 단축됩니다. 둘째, 오류 감소입니다. 사람이 직접 복사 붙여넣기를 하다 보면 실수가 발생하기 마련인데, 자동화된 프로세스는 일관된 결과를 보장합니다. 셋째, 재사용성입니다. 한 번 만든 쿼리는 계속 사용할 수 있어 반복 작업에 최적화되어 있습니다. 넷째, 데이터 정제 기능입니다. 불필요한 열 삭제, 데이터 타입 변경, 중복 제거, 조건부 필터링 등의 작업을 시각적 인터페이스로 간편하게 수행할 수 있습니다.
또한 파워 쿼리는 M 언어라는 함수형 프로그래밍 언어를 기반으로 작동하지만, 대부분의 작업은 마우스 클릭만으로 가능하며 자동으로 코드가 생성됩니다. 고급 사용자는 직접 M 코드를 작성하여 더욱 복잡한 변환도 수행할 수 있습니다. 파워 쿼리 편집기는 각 단계를 시각적으로 보여주기 때문에 어떤 변환이 적용되었는지 한눈에 파악할 수 있으며, 특정 단계를 수정하거나 삭제하는 것도 간단합니다. 이러한 특징들이 결합되어 파워 쿼리는 데이터 분석가뿐만 아니라 일반 비즈니스 사용자들에게도 강력한 도구로 자리잡고 있습니다.
여러 파일의 데이터를 한 번에 통합하는 방법
실무에서 가장 흔한 상황 중 하나는 동일한 구조를 가진 여러 파일을 하나로 합치는 작업입니다. 예를 들어 각 지점별 매출 데이터가 별도의 엑셀 파일로 관리되고 있거나, 매월 생성되는 보고서 파일들을 하나의 통합 데이터셋으로 만들어야 하는 경우입니다. 파워 쿼리의 '폴더에서 가져오기' 기능을 사용하면 이러한 작업을 자동화할 수 있습니다. 먼저 통합하려는 모든 파일을 하나의 폴더에 모아둡니다. 엑셀에서 '데이터' 탭으로 이동한 후 '데이터 가져오기' 메뉴에서 '파일에서' 옵션을 선택하고 '폴더에서'를 클릭합니다.
폴더를 선택하면 해당 폴더 내 모든 파일의 목록이 표시됩니다. 여기서 '데이터 변환' 버튼을 클릭하면 파워 쿼리 편집기가 열립니다. 편집기에서는 파일 속성 정보가 나타나는데, 실제 데이터를 불러오려면 'Content' 열 옆의 확장 아이콘을 클릭해야 합니다. 이 과정에서 첫 번째 파일을 샘플로 사용할지 묻는 메시지가 나타나며, 모든 파일이 동일한 구조라면 진행하면 됩니다. 그러면 모든 파일의 데이터가 하나의 테이블로 결합됩니다. 필요하다면 출처 파일명을 나타내는 열을 유지하여 어느 파일에서 온 데이터인지 추적할 수도 있습니다.
파일 통합 과정에서 주의할 점이 있습니다. 첫째, 모든 파일의 열 구조가 동일해야 합니다. 열 이름과 순서가 다르면 데이터가 제대로 매칭되지 않을 수 있습니다. 둘째, 데이터 타입을 확인해야 합니다. 날짜가 텍스트로 인식되거나 숫자가 문자열로 저장된 경우가 있으므로 적절한 데이터 타입으로 변환해주어야 합니다. 파워 쿼리 편집기에서 각 열 이름 옆의 아이콘을 보면 현재 데이터 타입을 확인할 수 있으며, 클릭하여 변경할 수 있습니다. 셋째, 헤더 행 처리입니다. 각 파일마다 헤더가 있다면 첫 행을 헤더로 사용하는 단계를 추가해야 하며, 중복된 헤더 행이 데이터로 들어오지 않도록 필터링해야 합니다.
더 나아가 여러 시트를 가진 통합 문서들을 처리할 수도 있습니다. 각 파일에 여러 시트가 있고 특정 시트만 가져오려면 필터 조건을 추가하면 됩니다. 예를 들어 'Sheet1'이라는 이름의 시트만 필터링하거나, 시트 이름에 '매출'이라는 단어가 포함된 것만 선택할 수 있습니다. 또한 파일명에서 정보를 추출하는 것도 가능합니다. 파일명이 '2024년1월_매출.xlsx' 형식이라면 파일명에서 연도와 월을 추출하여 새로운 열로 만들 수 있습니다. 이는 '열 추가' 탭의 '예제에서 열 추가' 기능이나 '사용자 지정 열' 기능을 통해 구현할 수 있습니다. 이렇게 만들어진 쿼리는 저장되며, 새로운 파일이 폴더에 추가되더라도 새로고침만 하면 자동으로 포함됩니다.
자동 갱신 설정으로 최신 데이터 유지하기
파워 쿼리의 진정한 가치는 한 번 설정한 쿼리를 지속적으로 활용할 수 있다는 점에 있습니다. 데이터 소스가 업데이트될 때마다 파워 쿼리를 새로고침하면 최신 정보가 자동으로 반영됩니다. 수동 새로고침은 간단합니다. '데이터' 탭의 '모두 새로고침' 버튼을 클릭하거나 특정 쿼리만 새로고침하려면 '쿼리 및 연결' 창에서 해당 쿼리를 마우스 오른쪽 클릭하여 '새로고침'을 선택하면 됩니다. 하지만 매번 수동으로 새로고침하는 것도 번거로울 수 있기 때문에 자동화 옵션을 설정하는 것이 좋습니다.
자동 새로고침을 설정하는 방법은 여러 가지가 있습니다. 가장 기본적인 방법은 통합 문서를 열 때 자동으로 새로고침되도록 하는 것입니다. '데이터' 탭에서 '연결 속성'을 클릭하거나, 쿼리를 마우스 오른쪽 클릭하여 '속성'을 선택합니다. 속성 창에서 '파일을 열 때 데이터 새로고침' 옵션을 체크하면 엑셀 파일을 열 때마다 자동으로 최신 데이터를 가져옵니다. 또한 '일정 시간마다 새로고침' 옵션을 활성화하면 지정한 시간 간격으로 자동 업데이트되도록 설정할 수 있습니다. 예를 들어 60분마다 새로고침하도록 설정하면 파일을 열어둔 상태에서 한 시간마다 데이터가 갱신됩니다.
더욱 고급 자동화를 원한다면 VBA 매크로나 파워 오토메이트를 활용할 수 있습니다. VBA를 사용하면 특정 이벤트가 발생했을 때 쿼리를 새로고침하도록 코드를 작성할 수 있습니다. 예를 들어 특정 버튼을 클릭했을 때, 특정 셀의 값이 변경되었을 때, 또는 정해진 시간에 매크로가 실행되도록 설정할 수 있습니다. 간단한 VBA 코드 예시로는 ActiveWorkbook.RefreshAll 명령어를 사용하여 모든 쿼리를 새로고침할 수 있습니다. 마이크로소프트 파워 오토메이트를 사용하면 클라우드 기반의 자동화 워크플로우를 만들 수 있어, 엑셀 파일이 닫혀 있어도 예약된 시간에 자동으로 새로고침되도록 할 수 있습니다.
자동 갱신 설정 시 고려해야 할 사항들도 있습니다. 첫째, 성능 문제입니다. 처리할 데이터양이 많거나 복잡한 변환이 포함된 경우 새로고침에 시간이 오래 걸릴 수 있습니다. 이런 경우 쿼리 최적화를 고려해야 하며, 불필요한 열을 초기 단계에서 제거하거나 필터를 먼저 적용하는 것이 좋습니다. 둘째, 데이터 소스의 접근성입니다. 네트워크 드라이브나 웹 소스의 경우 연결이 끊어지면 새로고침이 실패할 수 있으므로 오류 처리 방법을 마련해두어야 합니다. 셋째, 보안 설정입니다. 외부 데이터 소스에 연결할 때는 적절한 인증 정보가 필요하며, 파일을 공유할 때는 다른 사용자도 동일한 소스에 접근할 수 있는지 확인해야 합니다. 이러한 점들을 고려하여 자동 갱신을 설정하면 항상 최신 데이터를 기반으로 분석과 의사결정을 할 수 있습니다.