전자상거래에서 내보낸(export) 데이터는 대부분 지저분하게 되어 있습니다. 하나의 파일 안에 여러 SKU 옵션이 섞여 있고, 고객 정보는 일관성이 없으며, 주소는 한 줄로 뭉쳐 있고, 중복값이나 빈(null) 값이 존재하며, 가격 정보도 여기저기 흩어져 있는 경우가 흔합니다. 이런 데이터를 엑셀이나 Power BI에서 분석해 본 경험이 있다면 얼마나 금방 복잡해지는지 잘 아실 겁니다. 바로 이럴 때 파워 쿼리(Power Query)가 강력한 해결책이 됩니다. Excel이든 Power BI든, 파워 쿼리를 활용하면 복잡한 수식을 작성하지 않고도 원시 전자상거래 데이터를 손쉽게 정리하고 재구조화할 수 있습니다.
이번 튜토리얼에서는 온라인 스토어 데이터셋에 특히 유용한, 전자상거래 판매 데이터를 위한 5가지 핵심 파워 쿼리 변환 기법을 알아보겠습니다.
1. SKU 옵션 열 피벗 해제(Unpivot)
많은 전자상거래 내보내기 파일은 주문된 상품 정보를 여러 개의 열에 나눠 저장합니다. 예를 들어 Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2, Variant 3 같은 열마다 각각 수량이 담겨 있는 식입니다. 이런 구조는 하나의 주문 정보가 여러 열에 걸쳐 있어 분석하기 매우 어렵습니다. 피벗 테이블, DAX 측정값, SUM 수식 모두 이렇게 넓게 펼쳐진(wide) 형식에서는 효과적으로 집계할 수 없습니다. 피벗 해제를 수행하면 분석에 최적화된 길고 정규화된(long, normalized) 팩트 테이블이 만들어집니다.
실행 단계:
- 데이터를 파워 쿼리로 불러옵니다
- SKU 열(Stock S, M, L, XL)을 선택합니다
- 변환(Transform) 탭 >> 열 피벗 해제(Unpivot Columns)를 선택합니다
- 파워 쿼리가 해당 SKU 열들을 두 개의 새 열로 변환하며, 이름을 다음과 같이 변경합니다:
- 특성(Attribute): Size
- 값(Value): Quantity
주문된 여러 상품이 행이 아닌 별도의 열로 저장되어 있는 내보내기 파일이라면 이 변환 기법을 활용하세요.
2. 배송지 주소 문자열을 여러 필드로 분할
원시 전자상거래 파일의 약 90%는 전체 배송지 주소를 하나의 문자열로 저장합니다. 이 필드를 분할하면 시·도 단위 세금 보고, 배송비 최적화, 지역별 성과 분석, 지도 시각화 등이 가능해집니다.
실행 단계:
- 배송지 주소(Shipping Address) 열을 선택합니다
- 홈(Home) 탭 >> 열 분할(Split Column) >> 구분 기호별(By Delimiter)을 선택합니다
- 데이터에 맞는 구분 기호를 사용합니다:
- 쉼표
- 하이픈
- 줄바꿈
- 사용자 지정 구분자
- 본 예제 데이터에서는 쉼표(Comma)를 구분 기호로 선택합니다
- 확인(OK)을 클릭합니다
- 생성된 열에 의미 있는 이름을 부여합니다: Street(도로명), City(도시), Postcode(우편번호), Country(국가)
주소가 분할되면 도시별 주문 분석, 권역별 배송 성과, 지역별 매출, 특정 지역의 재구매 고객 분석 등이 가능해집니다. 특히 지역 기반 배송 사업이나 지역별 판매 보고서 작성에 매우 유용합니다.
심화 팁: 모든 주소가 동일한 형식을 따르지는 않습니다. 일부 주소에는 불필요한 요소가 있거나 누락된 부분이 있고, 아파트·동·호수 정보가 포함되기도 합니다. 이런 경우 다음 방법을 활용할 수 있습니다:
- 분할 후 앞뒤 공백 제거(Trim)
- 필요한 부분만 다시 병합(Merge)
- 구분 기호 앞/뒤 텍스트 추출(Extract Text Before/After Delimiter) 사용
- 예외 처리를 위한 사용자 지정 열 생성
3. 공백 제거, 데이터 정리 및 데이터 형식 표준화
대규모 전자상거래 데이터셋에서는 고객 이름, SKU, 이메일 주소, 상품 카테고리에 앞뒤 공백, 인쇄 불가능한 숨은 문자, 일관성 없는 대소문자가 섞여 있는 경우가 많습니다. 데이터 형식도 서로 맞지 않아 숫자가 텍스트로 저장되어 있기도 합니다.
이런 사소한 문제들이 큰 장애를 일으킬 수 있습니다. 동일한 데이터가 서로 다른 값으로 인식되거나, 병합(merge) 작업에서 실패하거나, 중복 그룹이 생기는 원인이 됩니다.
데이터 정리:
- 공백 제거(Trim):
- 텍스트 열을 선택합니다
- 변환(Transform) >> 형식(Format) >> Trim을 선택합니다
- 앞뒤의 불필요한 공백이 제거됩니다
- 숨은 문자 제거(Clean):
- 같은 열을 선택한 상태에서,
- 형식(Format) >> Clean을 선택합니다
- 인쇄 불가능한 문자가 제거됩니다
- 대소문자 표준화:
- 변환(Transform) >> 형식(Format)에서 다음을 선택합니다:
- 이름에는 단어 첫 글자만 대문자(Capitalize Each Word)
- 필요하다면 이메일에는 소문자(Lowercase)
데이터 형식 표준화:
- 날짜 열을 선택합니다
- 홈(Home) 탭 >> 데이터 형식(Data Type) >> 날짜(Date)를 선택합니다
- 필요하다면 날짜 관련 열을 추가할 수 있습니다:
- 열 추가(Add Column) 탭 >> 날짜(Date) 확장
- 연도(Year), 월(Month), 일(Day), 분기(Quarter), 요일(Day of Week) 선택
- 수량(Quantity), 가격(Price) 같은 숫자 열을 선택합니다
- 열 머리글 아이콘을 확장 >> 10진수(Decimal Number)를 선택합니다
- 또는 변환(Transform) 탭 >> 데이터 형식(Data Type) >> 10진수(Decimal Number) 또는 정수(Whole Number)를 선택합니다
이 변환 과정은 그룹화, 병합, 필터링, 조회(lookup) 작업의 신뢰성을 크게 높여주기 때문에 반드시 거쳐야 하는 필수 단계입니다.
4. 중복 제거 및 null 값 처리
null 값은 전자상거래 데이터에서 흔히 발견됩니다. 무해한 경우도 있지만, 계산을 망가뜨리는 경우도 있습니다. 결제 게이트웨이나 동기화 프로세스는 OrderID를 중복 생성할 수 있고, 수량이나 가격의 null 값은 합계와 시각화를 오류로 이끌 수 있습니다. null 값을 올바르게 처리하지 않으면 결과가 왜곡될 수 있습니다.
중복 제거:
- OrderID와 OrderDate를 키로 함께 선택합니다(같은 날 합법적으로 발생한 반복 주문이 삭제되는 것을 방지합니다)
- 홈(Home) 탭 >> 중복된 행 제거(Remove Duplicates)를 선택합니다
- 완전히 비어 있는 행만 제거합니다:
- 홈(Home) 탭 >> 빈 행 제거(Remove Blank Rows)를 선택합니다(먼저 OrderID가 null인 행을 필터링하는 것이 좋습니다)
값 바꾸기:
- 할인란이 비어 있다는 것이 '할인 없음'을 의미한다면 null을 0으로 바꿉니다
- 수량(Quantity)과 가격(Price) 열을 모두 선택합니다
- 변환(Transform) 탭 >> 값 바꾸기(Replace Values)를 선택합니다:
- 찾을 값: null
- 바꿀 값: 0
텍스트 값 정리:
- Stock_처럼 불필요한 접두사를 제거합니다
- Size 열을 선택합니다
- 변환(Transform) 탭 >> 값 바꾸기(Replace Values)를 선택합니다:
- 찾을 값: Stock_
- 바꿀 값: (비워 둠)
이제 Size 값이 깔끔하고 읽기 쉬운 형태로 표시됩니다.
반복 값 아래로 채우기(Fill Down):
- 때로는 주문의 첫 번째 행에만 OrderID나 고객 이름이 기록되어 있는 경우가 있습니다
- 해당 열을 선택합니다
- 변환(Transform) >> 채우기(Fill) >> 아래로(Down)를 선택합니다
중요한 주의사항: 모든 null을 무조건 바꾸면 안 됩니다. 비어 있는 값은 '해당 없음', '알 수 없음', 또는 데이터 오류를 의미할 수 있습니다. 값을 바꾸기 전에 반드시 그 비즈니스적 의미를 먼저 파악하세요.
5. 계산된 사용자 지정 열 만들기
전자상거래 데이터에서 매출(revenue)이나 마진(margin) 열을 직접 도출할 수 있습니다. 물론 엑셀 수식으로도 가능하지만, 그 경우 새로고침할 때마다 수식이 깨질 수 있습니다. 반면 파워 쿼리의 사용자 지정 열은 자동으로 다시 계산되며 ETL 파이프라인의 일부로 유지됩니다.
순매출(Net_Revenue):
- 열 추가(Add Column) 탭 >> 사용자 지정 열(Custom Column)을 선택합니다
- 열 이름을 입력합니다
- 다음 수식을 삽입합니다
[Unit_Price] * [Qty] * (1 - [Discount_Pct])
총마진(Gross_Margin):
[Net_Revenue] - ([Cost_Per_Unit] * [Qty])
마진율(Margin_Pct, %):
if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]
- 데이터 형식을 명시적으로 지정합니다: Net_Revenue와 Gross_Margin은 통화(Currency), Margin_Pct는 백분율(Percentage)로 설정합니다
중요: 나눗셈 수식에는 반드시 예외 처리를 넣으세요. 분모가 0이면 파워 쿼리에서 해당 열 전체에 오류가 발생하고, 로드 과정에서 행이 누락될 수 있습니다. 모든 비율 계산에는 if [X] = 0 then null else … 패턴을 사용하는 것이 안전합니다.
6. 고성능 요약 테이블을 위한 그룹화 및 집계
수백만 행에 달하는 원시 전자상거래 파일은 보고서 속도를 크게 늦출 수 있습니다. 그룹화를 통해 대시보드용 효율적인 집계 테이블을 만들고, 상세 분석(drill-through)용으로는 원본 상세 쿼리를 그대로 유지하는 것이 좋습니다.
실행 단계:
- 변환(Transform) >> 그룹화(Group By)를 선택합니다
- Category를 선택 >> 집계 추가(Add aggregation)를 클릭합니다:
- Net_Revenue의 합계
- Margin_Pct의 평균
- Order_ID(행 수)의 개수
- 새 쿼리의 이름을 Sales_Summary로 지정하고, 드릴스루 분석을 위해 원본 쿼리는 Sales_Detail로 유지합니다
전문가 팁: Power BI에서는 상세(detail) 쿼리의 '로드 사용(Enable load)' 옵션을 끄고, 요약 테이블과 차원(dimension) 테이블만 로드하면 성능이 크게 향상됩니다.
마지막 단계:
- 홈(Home) 탭 >> 닫기 및 적용(Close & Apply)을 선택하여 데이터를 로드합니다
결론
이제 전자상거래 판매 데이터에 바로 적용할 수 있는 5가지 핵심 파워 쿼리 변환 기법을 익혔습니다. 파워 쿼리는 간단한 변환부터 복잡한 변환까지 반복 가능한 방식으로 처리할 수 있어, 전자상거래 데이터셋 정리에 가장 효과적인 도구 중 하나입니다. 데이터 준비 시간을 획기적으로 줄여줄 수 있습니다. 이 기법들은 단순한 일반적인 정리 절차가 아니라, 실제 전자상거래 보고 환경에서 마주치는 문제들을 직접 해결하고 이후 분석 단계의 효율을 크게 높여줍니다. 이러한 변환에 익숙해지면, 새로운 마켓플레이스 내보내기 파일이 올 때마다 재사용할 수 있는 파워 쿼리 템플릿을 직접 구축할 수도 있습니다.