수년 동안 저는 간단한 계산과 기본적인 표를 만드는 데 Excel을 사용했습니다.일반적인 수식을 활용하고 간단한 데이터 조작은 했지만, 프로젝트가 점점 복잡해지기 전까지는 Excel의 복잡한 기능을 깊이 파고들 필요가 전혀 없었습니다.
내 탐험을 촉발한 도전
제가 사는 지역의 PC 부품 가격이 치솟고 있습니다.시장 변동과 수입세 때문에 미국보다 더 높은 경우가 많았죠.그래서 가격 차이를 조사하게 되었습니다.아마존이나 뉴에그 같은 플랫폼에서 직접 구매하는 것이 지역 소매업체보다 더 경제적일지 알아보고 싶었습니다.이를 위해 CPU, GPU, RAM 등 필수 PC 부품의 몇 달치 가격 데이터를 수집했습니다.처음에는 간단한 추적 프로젝트라고 생각했던 것이 금세 어려운 작업으로 바뀌었습니다.
제가 수집한 데이터는 혼란스러웠습니다.각 소매업체의 파일 형식이 서로 달라서 데이터를 병합하는 것이 매우 어려웠습니다.예를 들어 Amazon은 날짜를 MM/DD/YYYY 형식으로 기록하는 반면, Newegg는 YYYYMMDD 형식을 사용했고, 제가 다니는 지역 상점인 Shopee는 DD-MM-YYYY 형식을 고수했습니다.
데이터 불일치는 날짜 형식에서만 그치지 않았습니다.열 이름이 완전히 달랐습니다. Newegg는 가격을 “retail_price”로 표시했고, Amazon은 “uni_price_usd”를 선택했으며, Shopee는 단순히 “price_php”로 표시했습니다.게다가 가격 형식도 다양해서 일부 항목은 “₱18, 600″과 같은 통화 기호를 사용하는 반면, 다른 항목은 “320”과 같은 일반 숫자로 표시되었습니다.브랜드 이름조차도 “gigabyte”, “GIGABYTE INC”, “Gigabyte Tech”와 같이 다양한 형식으로 표시되어 일관성이 없었습니다.
이 데이터를 수동으로 정리하고 통합하는 작업은 점점 더 지쳐갔습니다.파일 간 복사, 일관성 없는 값 교체, 빈 행 제거에 수많은 시간을 허비했습니다.게다가 PHP에서 USD로 가격을 변환하려면 두 번째 모니터에서 실시간 환율을 계속 확인해야 했습니다.이 노동 집약적인 작업은 지루하고 잠재적인 오류로 가득 차서 거의 프로젝트를 포기할 뻔했습니다.
그때 Excel 마니아들이 극찬하는 기능인 파워 쿼리를 살펴보기로 했습니다. Excel에는 수많은 고급 도구가 있지만, 파워 쿼리가 제 문제를 해결해 준다는 것을 알게 되었습니다. YouTube에서 교육용 동영상을 시청한 후, 파워 쿼리 편집기를 사용하여 수집한 무질서한 데이터를 정리하면 얼마나 많은 시간을 절약할 수 있는지 깨달았습니다.
Power Query 사용에 대한 단계별 접근 방식
얼마 지나지 않아 Power Query Editor에서 지저분한 CSV 파일을 정리하고 일관되고 체계적인 스프레드시트로 변환하는 간단한 절차를 개발했습니다.
먼저 Power Query 편집기로 데이터를 가져왔습니다.새 Excel 통합 문서를 열고 리본 메뉴에서 ‘데이터’를 선택한 다음, ‘텍스트/CSV에서’를 선택했습니다. CSV 파일을 선택한 후 ‘데이터 변환’을 클릭하여 Power Query 편집기를 실행했습니다.
첫 번째 단계는 날짜 열을 표준화하는 것이었습니다.두 소스의 타임라인이 12시간 차이가 나므로 날짜 형식을 통일하는 것이 필수적이었습니다.이 과정은 매우 간단했습니다.날짜 열 을 강조 표시하고 마우스 오른쪽 버튼을 클릭한 후 ‘유형 변경 > 로캘 사용’을 선택했습니다.이어지는 팝업에서 형식을 ‘ 날짜’ 로 설정하고 일관된 형식을 위해 ‘영어(미국)’를 선택했습니다.파워 쿼리는 MM/DD/YYYY, YYYY/MM/DD, DD-MM-YYYY 등 다양한 형식을 정확하게 식별하여 단일 형식으로 쉽게 표준화했습니다.
날짜 형식을 수정한 후 열을 정리했습니다.대부분의 오류는 데이터 스크래핑 도구에서 발생했기 때문에 ‘오류 제거’ 필터를 사용하여 잘못된 항목을 제거했습니다.이 단계를 통해 null 값과 남아 있는 문제가 있는 데이터를 효과적으로 제거하여 모든 파일에서 일관된 날짜 표현을 보장했습니다.
다음으로, ‘값 바꾸기’ 기능을 통해 혼란스러운 브랜드 이름을 수정했습니다.관련 열을 선택한 후 마우스 오른쪽 버튼을 클릭하고 ‘ 값 바꾸기 ‘에 접속하여 모든 브랜드 이름 변형을 입력하고 표준화했습니다.예를 들어 “gigabyte”와 “GIGABYTE INC.”를 하나의 일관된 항목인 “GIGABYTE”로 변환했습니다. AMD에도 동일한 방식을 적용하여 모든 데이터 소스에서 브랜드 열을 효과적으로 간소화했습니다.
-
Jayric Maning의 스크린샷 – 출처 표시 불필요 -
Jayric Maning의 스크린샷 – 출처 표시 불필요
마지막으로 가격 열을 표준화했습니다.많은 항목이 통화 관련 맥락이 없는 단순한 숫자로 이루어져 있어 변환 작업이 복잡했기 때문에 어려움이 있었습니다.다행히 매장 열에서는 즉각적인 맥락을 제공하여 Shopee PH 가격을 USD로 쉽게 변환할 수 있었습니다.
이 문제를 해결하기 위해 먼저 ‘값 바꾸기’를 사용하여 ‘가격’ 열에서 모든 통화 기호(예: $, ₱, USD, PHP)를 제거했습니다.’ 유형 변경’ > ’10진수’를 선택하여 입력 항목을 10진수 형식으로 변환한 후, ‘열’ 탭 의 ‘사용자 지정 열 ‘을 사용하여 “가격 USD”라는 새 열을 만들었습니다.다음 수식을 구현했습니다.
if [Store] = "Shopee PH"then Number. Round([Price] / 55, 2) else Number. Round([Price], 2)
확인 을 클릭하여 모든 가격을 Shopee PH의 USD로 변환하여 명확하게 비교할 수 있도록 했습니다.
이 수식은 Power Query M 수식 언어 로 작성되었습니다.이 스크립팅 언어는 비교적 이해하기 쉬운 직관적인 구문을 제공합니다.효과적으로 활용하기 위해 언어 전체를 숙달할 필요는 없습니다.저는 수식을 작성하는 데 필요한 경우에만 이 언어를 사용합니다.
데이터를 수정한 후 Power Query 편집기를 닫고 ‘ 유지’ 를 클릭하여 통합 문서에서 변경한 모든 내용을 저장했습니다.
Power Query를 통한 시간 절약
처음에는 파워 쿼리를 도입하는 데 주저했습니다.시간을 너무 많이 잡아먹는 또 다른 복잡한 기능이 추가될까 봐 걱정되었기 때문입니다.하지만 예상과는 달리 훨씬 간단하다는 것을 알게 되었습니다.파워 쿼리는 끝없는 찾기 및 바꾸기 작업을 실행하는 대신, 스크래핑 도구에서 생성된 데이터를 효율적으로 정리할 수 있게 해 주었습니다.
더욱 놀라운 점은 제가 구현한 모든 조정 사항이 문서화되어 정리 과정을 손쉽게 재현할 수 있다는 사실이었습니다.이는 데이터 정리를 위한 자동화된 스크립트 역할을 하여, 혼란스러운 CSV 파일을 체계적으로 정리된 스프레드시트로 변환합니다.웹 스크래핑을 통해 생성된 맞춤형 데이터 세트처럼 데이터 품질이 일정하지 않은 상황에 이상적입니다.
번거로운 데이터 정리 작업, 일관되지 않은 형식, 또는 여러 데이터 원본을 자주 관리하는 사람들을 위해 Power Query는 이러한 프로세스를 관리하기 쉬운 자동화된 워크플로로 간소화합니다.매주 몇 시간씩 문제를 수동으로 해결하는 대신, 새로 고침 클릭 한 번으로 바로 분석에 착수할 수 있습니다.훨씬 더 일찍 도입했으면 좋았을 Excel의 강력한 기능입니다.자동화되고 쉽게 반복 가능한 정리 프로세스의 효율성을 경험하고 나면 더 이상 뒤돌아볼 수 없습니다.
답글 남기기