데이터 분석가로서 마스터 판매 대시보드를 생성하는 임무를 맡았다고 가정해 보겠습니다. 하지만 깔끔한 데이터 세트부터 시작하는 대신, 지역과 월별로 나뉜 50개의 CSV 파일이 담긴 Monthly_Logs 폴더, 배송비 데이터가 포함된 PDF, 그리고 제품 원가가 저장된 Supabase PostgreSQL 데이터베이스에 대한 액세스 권한을 넘겨받았습니다. 여러분은 이 모든 것을 실제로 작업할 수 있는 하나의 깔끔하고 정규화된 테이블로 만들어야 합니다.
처음에는 이런 종류의 프로젝트를 처리하는 유일한 방법이 여러 파일에서 정보를 수동으로 복사하여 붙여넣는 것뿐이라고 생각할 수도 있지만, 저는 전혀 그럴 필요가 없다는 것을 알게 되었습니다. Power Query를 사용하여 세 가지 소스에서 데이터를 가져와 단 몇 분 만에 모든 것을 하나의 통합된 데이터 세트로 병합했습니다. 그 과정에서 처음부터 다시 만들 필요 없이 불필요한 행을 제거하고, 열을 분할하고, 사용자 지정 열을 추가하고, 일관성 없는 텍스트 형식을 정리했습니다.
직접 따라 해보고 싶으시다면, 이 과정을 직접 시도하는 데 필요한 모든 파일을 여기에 첨부해 두었습니다.
관련 항목
CSV 파일 정리 및 통합
Power Query를 사용하여 50개의 CSV 파일을 한 번에 결합, 정리, 분할, 피벗 해제 및 표준화하기
이 경우, Monthly_Logs 폴더에는 지역 및 월별로 하나씩, 총 50개의 개별 CSV 파일이 포함되어 있습니다. 모든 파일에는 상단에 3개의 불필요한 행, 가로로 펼쳐진 26개의 날짜 열, 101-Chicago-60601과 같은 항목을 사용하여 세 가지 다른 값을 하나의 필드로 결합한 Store_Info 열, 15개의 불필요한 Internal_Notes 열, 그리고 서로 다른 팀이 서로 다른 명명 규칙으로 입력한 것처럼 보이는 제품 이름이 포함되어 있습니다.
모든 것을 정리하기 시작하려면 Excel에서 데이터 > 데이터 가져오기 > 파일에서 > 폴더에서로 이동하여 Monthly_Logs 폴더를 선택하고 확인을 클릭합니다. 미리 보기 대화 상자에서 데이터 변환을 선택하면 Power Query가 각 행이 별도의 파일을 나타내는 테이블을 로드합니다. 거기에서 Content 열에 이중 화살표 아이콘으로 표시되는 파일 결합 버튼을 클릭합니다. Power Query는 즉시 50개의 모든 CSV 파일을 하나의 테이블로 쌓습니다.
하지만 모든 파일이 불필요한 행으로 시작하기 때문에 먼저 정리하지 않고는 결합할 수 없습니다. 왼쪽의 쿼리 창에서 Power Query는 자동으로 Transform Sample File이라는 도우미 쿼리를 생성합니다. 이 쿼리는 병합이 발생하기 전에 모든 CSV 파일이 처리되는 방식에 대한 템플릿 역할을 합니다. 이 쿼리를 열고 홈 > 행 제거 > 상단 행 제거 > 3으로 이동하여 정리 단계를 적용한 다음, 홈 > 첫 행을 머리글로 사용을 선택합니다. 기본 Monthly_Logs 쿼리로 돌아오면 Power Query가 50개의 모든 CSV 파일을 결합하기 전에 동일한 정리 단계를 자동으로 적용하므로 전체 데이터 세트에서 불필요한 행이 한 번에 제거됩니다.
다음 문제는 판매 데이터 자체의 구조입니다. Jan-2023 및 Feb-2023과 같은 머리글이 있는 26개의 개별 월 열을 갖는 것은 분석을 불필요하게 어렵게 만듭니다. 특히 나중에 Excel에서 피벗 테이블이나 대시보드를 만들 계획이라면 더욱 그렇습니다. 데이터를 정규화하려면 Ctrl 키를 누른 상태에서 26개의 모든 월 열을 선택하고 마우스 오른쪽 버튼을 클릭한 다음 열 피벗 해제를 선택합니다. Power Query는 해당 넓은 열을 훨씬 더 깔끔한 두 개의 필드, 즉 월 이름이 포함된 열 하나와 판매 값이 포함된 열 하나로 변환합니다. 그런 다음 열 이름을 Month 및 Sales_Amount와 같이 더 명확한 것으로 바꿀 수 있습니다.
Store_Info 열도 여러 값을 하나의 필드로 결합하므로 주의가 필요합니다. 열을 선택하고 홈 > 열 분할 > 구분 기호 기준을 선택합니다. 하이픈(-) 구분 기호를 선택하고 각 발생 항목에서 분할하도록 설정합니다. Power Query는 즉시 필드를 개별 열로 분리하며, 이를 Store_ID, City, Zip으로 이름을 바꿀 수 있습니다.
이 시점에서 15개의 Internal_Notes 열은 단지 혼란을 가중시킬 뿐입니다. Ctrl 키를 누른 상태에서 각 불필요한 열을 선택하고 마우스 오른쪽 버튼을 클릭한 다음 열 제거를 선택하여 데이터 세트에서 지웁니다.
Product_Name 열도 표준화가 필요합니다. 일관성 없는 명명은 보고 문제를 일으키기 때문입니다. 먼저 열을 선택하고 변환 > 서식 > 다듬기를 선택하여 제품 이름 앞뒤의 추가 공백을 제거합니다. 그런 다음 변환 > 서식 > 각 단어의 첫 글자를 대문자로를 사용하여 텍스트 대소문자를 표준화합니다. 그 후 홈 > 값 바꾸기를 사용하여 명명 변형을 통합합니다. 예를 들어 "milk"를 "Whole Milk"로, "Milk-W"를 "Whole Milk"로 바꿀 수 있습니다. "Whole Whole Milk"와 같이 바꾸는 과정에서 실수로 생성된 중복 항목이 있는지 확인하여 정리하는 것도 좋습니다.
정리 과정을 마무리하려면 Transaction_ID 열을 선택하고 마우스 오른쪽 버튼을 클릭한 다음 중복 제거를 선택하여 중복 트랜잭션을 제거합니다. Power Query는 첫 번째 항목을 유지하고 반복되는 항목을 제거합니다. 그런 다음 Quantity 열을 필터링하고 null 값을 선택 취소하여 수량이 누락된 행을 제거합니다.
최종 결합된 데이터 세트가 상당히 크기 때문에 표준 Excel 워크시트에 직접 로드하지 않는 것이 좋습니다. 대신 홈 > 닫기 및 로드 > 닫기 및 로드…로 이동하여 연결만 만들기를 선택하고 이 데이터를 데이터 모델에 추가를 선택합니다.
이렇게 하면 데이터 세트가 Power Pivot으로 푸시되어 더 큰 데이터 세트를 훨씬 더 효율적으로 처리하고 통합 문서가 느려지거나 충돌하는 것을 방지할 수 있습니다.
어디서나 더 많은 데이터 결합하기
데이터베이스, PDF, 스프레드시트 등 무엇이든 Power Query가 하나로 모아줍니다
CSV 파일이 정리되고 통합되었으므로 다음 단계는 마스터 판매 대시보드에 필요한 나머지 데이터를 가져오는 것입니다. 이 경우 Supabase PostgreSQL 데이터베이스에서 제품 원가 데이터를 가져오고 PDF에 저장된 배송 요금을 가져오는 것을 의미합니다. 완전히 다른 데이터 소스임에도 불구하고 Power Query는 큰 문제 없이 둘 다 처리합니다.
데이터베이스 연결의 경우, 먼저 ODBC를 통해 Supabase에 연결한 다음 데이터 > 데이터 가져오기 > 기타 원본에서 > ODBC에서로 이동하여 해당 연결을 사용하여 Power Query로 데이터를 가져오는 것이 가장 쉽다는 것을 알았습니다. 자격 증명을 입력하고 탐색기 창에서 product_cost 테이블을 선택한 다음 로드…를 선택합니다. PostgreSQL 드라이버를 설치하고 데이터 > 데이터 가져오기 > 데이터베이스에서 > PostgreSQL 데이터베이스에서를 통해 직접 연결할 수도 있습니다. 어느 쪽이든 CSV 데이터 세트를 처리한 것과 같은 방식으로 데이터를 데이터 모델에 직접 로드하면 됩니다.
product_cost 테이블이 로드되면 판매 데이터와 병합할 수 있습니다. Monthly_Logs 쿼리를 열고 홈 > 쿼리 병합으로 이동합니다. 판매 데이터에서 일치하는 열로 Product_Name을 선택하고 드롭다운에서 product_cost 쿼리를 선택한 다음 데이터베이스 측의 product_name 열과 일치시킵니다. 왼쪽 우선 조인(Left Outer join)을 사용하면 일치하는 제품이 있는 곳마다 원가 데이터를 가져오면서 모든 판매 행을 유지할 수 있습니다. 병합이 완료되면 병합된 열의 확장 아이콘을 클릭하고 cost_per_unit, retail_price, gross_margin_pct, SKU와 같이 가져올 필드를 선택합니다. 그런 다음 닫기 및 로드…를 사용하여 변경 사항을 적용합니다.
PDF 가져오기도 비슷한 방식으로 작동합니다. 데이터 > 데이터 가져오기 > 파일에서 > PDF에서로 이동하여 Shipping_Costs_Invoice.pdf를 찾아보면 Power Query가 자동으로 문서에서 테이블을 검색합니다. 탐색기 창에서 배송 데이터가 포함된 테이블을 선택하고 데이터 모델에 로드합니다.
하지만 배송 정보를 판매 데이터에 병합하려면 두 데이터 세트 모두 공유 열이 필요합니다. 배송 테이블은 지역별로 요금을 구성하지만, 판매 데이터에는 도시 이름만 포함되어 있습니다. 이 간극을 메우려면 Monthly_Logs 쿼리 내에서 열 추가 > 조건부 열로 이동하여 새 Region 열을 만듭니다. 거기에서 "City가 Chicago와 같으면 Northeast"와 같은 규칙을 만들고 데이터 세트의 모든 도시에 대해 이 과정을 반복하며 일치하지 않는 항목에 대해서는 하단에 포괄적인 Else 조건을 남겨둡니다.
도시 목록이 크면 조건부 규칙을 수동으로 만드는 것이 지루해질 것입니다. 더 효율적인 접근 방식은 Excel에서 City 및 Region 값이 포함된 간단한 두 열 조회 테이블을 만드는 것입니다. 범위를 Ctrl + T로 명명된 Excel 테이블로 변환하고, 데이터 > 테이블/범위에서를 통해 Power Query로 가져와 데이터 모델에 로드합니다. 거기에서 Monthly_Logs 내의 쿼리 병합을 사용하여 두 테이블의 City 열을 일치시키고 해당 Region을 자동으로 가져옵니다.
Region 열이 제자리에 있으면 배송 데이터를 병합하는 것이 간단해집니다. 홈 > 쿼리 병합으로 이동하여 배송 테이블을 선택하고 두 데이터 세트의 Region 열을 일치시키고 왼쪽 우선 조인을 선택한 다음 Shipping Zone, Carrier, Avg Weight, Rate/lb, Fuel Surcharge, Handling Fee와 같이 포함할 필드를 확장합니다.
이 과정이 끝나면 50개월 분량의 쌓이고 피벗 해제된 판매 데이터, 적절하게 분리된 Store_ID, City, Zip 열, 중복 및 null 값이 제거된 표준화된 제품 이름, Supabase에서 직접 가져온 제품 원가 및 마진 정보, PDF에서 추출한 배송비 데이터가 포함된 단일하고 깔끔한 데이터 모델이 완성됩니다. 거기에서 어려운 정리 및 통합 작업이 이미 완료되었기 때문에 대시보드를 만드는 것이 훨씬 간단해집니다. 이제 삽입 탭으로 이동하여 피벗 테이블 > 데이터 모델에서를 선택하기만 하면 됩니다.
또는 Monthly_Logs 쿼리를 마우스 오른쪽 버튼으로 클릭하고 로드…를 선택하여 원하는 Excel 워크시트의 테이블에 데이터 세트를 삽입할 수 있습니다.
워크플로는 한 번만 구축하면 됩니다
예전에는 하루 종일 걸리던 작업들을 이제 단 몇 분 만에 설정할 수 있습니다. 다음에 Monthly_Logs 폴더에 새 CSV 파일이 도착하면 데이터 > 모두 새로 고침으로 이동하기만 하면 Power Query가 모든 변환을 자동으로 다시 실행합니다. 새 파일이 일치하는 머리글과 열 개수로 동일한 구조를 따르는 한, 전체 정리 과정이 몇 초 만에 다시 수행됩니다.
결국 제가 Power Query를 Excel에서 가장 간과되는 도구 중 하나라고 생각하는 이유가 바로 이것입니다. Excel 스프레드시트든 SQL 서버든 거의 어디에서나 데이터를 가져올 수 있고, 수동으로 하면 몇 시간이 걸릴 정리 작업을 처리하며, 일반 워크시트를 압도할 만큼 큰 데이터 세트를 처리할 수 있습니다. 더 중요한 것은 새로운 데이터가 나타날 때마다 동일한 정리 작업을 반복할 필요가 없다는 점이며, 이는 매우 가치 있는 일입니다.
OS Windows, macOS
지원되는 데스크톱 브라우저 웹 앱을 통한 모든 브라우저
개발자 Microsoft
무료 체험 1개월
가격 모델 구독
iOS 호환 가능 예
Microsoft Excel은 데이터 정리, 분석 및 시각화에 사용되는 강력한 스프레드시트 애플리케이션입니다. 복잡한 데이터 세트를 효율적으로 처리하기 위해 수식, 함수, 피벗 테이블 및 차트를 지원합니다. 비즈니스 및 교육 분야에서 널리 사용되는 Excel은 협업, 자동화 및 실시간 데이터 통찰력을 위해 다른 Microsoft 365 앱과도 통합됩니다.