- Power Query를 사용하면 동일한 데이터 스키마를 공유하는 한 로컬 또는 클라우드 폴더에 있는 여러 파일을 통합할 수 있습니다.
- 샘플 파일을 사용하면 상위 행 제거 또는 헤더 변경과 같은 일괄 변환을 더 쉽게 적용할 수 있습니다.
- 열이 정확히 일치하지 않을 경우 제약이 발생하며, 복잡한 경우에는 수동으로 정리하거나 VBA 스크립트를 사용해야 합니다.
여러 폴더에 흩어져 있는 월간 또는 주간 보고서가 있다고 상상해 보세요. 아침 시간을 허비하며 정신없이 데이터를 복사 붙여넣기하는 대신, 단 몇 번의 클릭만으로 모든 데이터를 하나의 엑셀 스프레드시트로 통합할 수 있다면 어떨까요? 바로 이럴 때 파워 쿼리가 유용합니다. 파워 쿼리는 데이터 작업을 하는 모든 사람에게 없어서는 안 될 필수 도구로, 동일한 구조를 가진 파일들을 손쉽게 통합할 수 있도록 도와줍니다.
이 시스템의 가장 멋진 점은 PC의 로컬 폴더에만 국한되지 않고 SharePoint 사이트, Azure Data Lake 또는 Azure Blob 에서 데이터를 가져올 수 있다는 것입니다 . 원활한 작동을 위한 핵심은 파일의 스키마가 동일해야 한다는 것입니다. 즉, 열이 일치해야 합니다. Power Query는 데이터에 불필요한 행이나 오류가 포함된 경우 자동으로 정리해주는 기능도 갖추고 있습니다.
단계별 조합 과정
데이터 소스에 연결하면 가장 먼저 파일 시스템 보기가 표시됩니다. 여기에서 가장 중요한 열은 각 문서의 이진 표현이 포함된 '콘텐츠' 열 입니다 . 이 이진 값 중 하나를 클릭하면 프로그램은 해당 파일이 CSV, JSON 또는 Excel 파일인지 확인하여 열려고 시도합니다. 전체 배치를 결합하려면 두 가지 옵션이 있습니다. '병합' 버튼을 직접 클릭하거나, 더 세밀하게 작업하려면 ' 데이터 변환'을 선택하여 파일 목록을 필터링 (예: 특정 폴더 제외)한 후 병합을 진행할 수 있습니다.
병합 대화 상자가 열리면 Power Query는 샘플 파일을 사용하여 구조를 분석합니다 . 기본적으로 목록의 첫 번째 파일을 선택하지만, 데이터 세트를 더 잘 나타내는 다른 파일이 있는 경우 변경할 수 있습니다. 여기에서 오류가 있는 파일을 건너뛰는 옵션을 선택할 수 있는데 , 보고서가 손상되었거나 비어 있는 경우 전체 프로세스가 중단되는 것을 방지하는 데 매우 유용합니다.
출력 관리 및 데이터 정제
병합을 승인하면 시스템은 백그라운드에서 몇 가지 기술적인 작업을 수행합니다. 샘플 쿼리와 매개변수화된 함수를 생성하는 것이죠. 즉 , 샘플 파일에 변경 사항을 적용 하면 폴더 내의 다른 모든 파일에도 자동으로 반영됩니다. 예를 들어 CSV 파일에 실제 데이터 앞에 불필요한 헤더 행이 네 개 있는 경우, 변환 쿼리에서 상위 행 제거 옵션을 사용하여 해당 부분을 정리할 수 있습니다.
행을 정리한 후에는 " 첫 번째 행을 머리글로 사용" 기능을 적용하는 것이 중요합니다 . 이 작업을 수행하면 Power Query에서 일반적으로 데이터 형식 변환 단계를 자동으로 추가한다는 점에 유의하세요. 기본 쿼리로 돌아갔을 때 'Column1' 열을 찾을 수 없다는 오류 메시지가 표시되더라도 걱정하지 마세요. 예제에서 열 이름을 변경했기 때문에 발생하는 정상적인 현상 이며, 이후 단계에서 이전 이름을 인식하지 못하는 것입니다. 적용된 단계 창에서 해당 오류가 있는 단계를 삭제하면 모든 것이 정상적으로 작동합니다.
일반적인 문제점 및 발전된 대안
때로는 삶이 그렇게 단순하지 않고, 완전히 동일하지 않은 파일을 만나게 될 수도 있습니다. 예를 들어, ServiceNow 보고서에서 헤더 이름이 'resolved_on'과 'closed_on'인 경우, Power Query는 일관된 구조를 요구하기 때문에 문제를 일으킬 수 있습니다 . 샘플 파일이 나머지 파일과 정확히 일치하지 않으면, Power Query는 일치하지 않는 파일의 정보를 생략하거나 가져오기 오류를 발생시킬 수 있습니다.
데이터 구조가 완전히 혼란스럽거나 데이터 양이 방대할 경우(엑셀 행 백만 개 이상) 다른 옵션이 있습니다. 일부 사용자는 CSV 인터페이스를 사용하는 VBA 스크립트를 선택하는데 , 이 방법은 필드 또는 레코드 구분 기호가 파일마다 다르더라도 헤더가 중복되지 않도록 파일을 병합할 수 있습니다. Windows Shell은 매우 빠르지만, 내장된 줄 바꿈을 처리하는 데 종종 실패하는 반면, 안정적인 VBA 솔루션은 RAM에 과부하를 주지 않고 수백만 개의 레코드를 쉽게 처리할 수 있습니다.
Power Query에서 데이터를 결합한 후에는 최종 테이블을 로드하기 전에 각 열에 올바른 데이터 형식 (날짜는 날짜 형식, 소득은 통화 형식 등)을 지정해야 합니다. 원본 이름 열을 필터링하여 폴더의 모든 파일이 성공적으로 처리되었는지 확인하면 모든 것이 올바르게 처리되었는지 확인할 수 있습니다.
여러 CSV 파일을 자동으로 병합하는 기능을 통해 지루한 수동 작업에서 벗어나 전문적인 워크플로로 전환할 수 있습니다. 데이터 정리를 위해 Power Query의 유연성을 활용하거나, 구조가 호환되지 않거나 데이터 양이 스프레드시트의 용량을 초과하는 경우 VBA를 사용할 수도 있습니다.