전자 상거래 판매 데이터를 정리하기 위한 5가지 주요 파워 쿼리 기술을 마스터하세요

전자상거래 수출 데이터는 대부분 지저분합니다. 하나의 파일에는 여러 SKU 변형, 일관되지 않은 고객 정보, 결합된 주소 필드, 중복 또는 null 값, 분산된 가격 세부 정보가 포함될 수 있습니다. Excel이나 Power BI에서 이러한 데이터를 분석해 본 적이 있다면 상황이 얼마나 빨리 복잡해지는지 알 수 있습니다. 파워 쿼리가 매우 유용해지는 곳이 바로 여기입니다. Excel이나 Power BI 등 어떤 도구를 사용하든 파워 쿼리는 복잡한 수식을 작성하지 않고도 원시 전자상거래 데이터를 정리하고 재구성하는 데 도움이 됩니다.

이 자습서에서는 온라인 상점 데이터 세트에 특히 유용한 전자 상거래 판매 데이터에 대한 5가지 필수 파워 쿼리 변환을 알아봅니다.

SKU 변형 열 피벗 해제

많은 전자 상거래 수출에서는 Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2 및 Variant 3과 같이 각각 수량을 보유하는 여러 열에 주문한 제품을 저장합니다. 이 구조는 각 주문이 여러 열에 제품 세부정보를 분산시키기 때문에 분석하기 어렵습니다. 피벗 테이블, DAX 측정값 및 SUM 수식은 이 넓은 형식에서 효과적으로 집계할 수 없습니다. 피벗을 해제하면 분석에 이상적인 길고 정규화된 팩트 테이블이 생성됩니다.

단계:

• 파워 쿼리에 데이터 로드

• SKU 열 선택(재고 S, M, L, XL)
• 변환으로 이동 탭>> 열 피벗 해제를 선택합니다.

• 파워 쿼리는 이러한 SKU 열을 두 개의 새 열로 변환하고 이름을 바꿉니다.
  • 속성: 크기
  • 값: 수량

내보내기에서 별도의 행이 아닌 별도의 열에 여러 주문 제품을 저장할 때 이 변환을 사용하세요.

배송 주소 문자열을 여러 필드로 분할

원시 전자상거래 파일의 약 90%는 전체 배송 주소를 단일 문자열로 저장합니다. 이 필드를 분할하면 주 차원의 세금 보고, 배송비 최적화, 지역 성과 분석 및 매핑이 가능해집니다.

단계:

• 배송지 주소를 선택하세요. 열
• 홈으로 이동 탭>> 열 분할 선택>> 구분자별을 선택하세요.

• 데이터에 따라 다음과 같은 구분 기호를 사용하세요.
  • 쉼표
  • 하이픈
  • 줄 바꿈
  • 맞춤 구분 기호
• 기존 데이터의 경우 쉼표를 선택하세요. 구분 기호로
• 확인을 클릭하세요.

• 결과 열의 이름을 거리, 도시, 우편번호, 국가 등 의미 있는 라벨로 바꿉니다.

주소가 분할되면 도시별 주문, 지역별 배송 실적, 지역별 매출, 특정 위치의 재구매 고객을 분석할 수 있습니다. 이는 지역 배송 사업 및 지역 기반 판매 보고에 특히 유용합니다.

고급 참고사항: 모든 주소가 동일한 형식을 따르는 것은 아닙니다. 일부에는 추가 부품, 누락된 부품 또는 아파트 세부정보가 있을 수 있습니다. 이러한 경우에는 다음을 수행할 수 있습니다.

• 분할 후 공백 자르기
• 선택한 부분을 다시 병합
• 구분자 앞/뒤의 텍스트 추출 사용
• 예외 처리를 위한 맞춤 열 만들기

데이터 유형 정리, 정리 및 표준화

대규모 전자 상거래 데이터세트에서는 고객 이름, SKU, 이메일 주소, 제품 카테고리에 앞뒤 공백, 인쇄할 수 없는 문자, 일관되지 않은 대문자 표시가 포함되는 경우가 많습니다. 텍스트로 저장된 숫자 값과 같이 데이터 유형이 일치하지 않을 수도 있습니다.

이러한 작은 문제는 심각한 문제를 일으킬 수 있습니다. 동일한 데이터가 다른 값으로 나타나거나, 병합 작업에 실패하거나, 중복 그룹이 생성될 수 있습니다.

데이터 정리:

• 공백 자르기:
  • 텍스트 열 선택
  • 변환으로 이동>> 형식을 선택합니다.>> 자르기를 선택하세요.
  • 이렇게 하면 시작과 끝에서 추가 공백이 제거됩니다.

• 숨겨진 문자 정리:
  • 동일한 열을 선택한 경우
  • 형식을 선택하세요.>> 청소를 선택하세요.
  • 인쇄할 수 없는 문자가 제거됩니다.
• 대소문자 표준화:
  • 변환으로 이동>> 형식을 선택하세요.>> 선택하세요:
  • 각 단어를 대문자로 시작 이름
  • 소문자 필요한 경우 이메일로

데이터 유형 표준화:

• 날짜 열 선택
• 홈으로 이동 탭>> 데이터 유형을 선택합니다.>> 날짜 선택

• 필요한 경우 열을 추가하세요.
  • 열 추가로 이동합니다. 탭>> 날짜 확장
  • 연도 선택 , 월 , 일 , 분기 , 요일

• 수량, 가격 등 숫자 열 선택
• 열 헤더 아이콘을 확장하고>> 십진수를 선택합니다.
• 또는 변환으로 이동하세요. 탭>> 데이터 유형을 선택합니다.>> 십진수를 선택하세요. 또는 정수

이러한 변환은 그룹화, 병합, 필터링 및 조회 작업을 훨씬 더 안정적으로 만들어주기 때문에 필수적입니다.

중복 제거 및 Null 값 처리

Null은 전자상거래 데이터에서 흔히 발생합니다. 일부는 무해하지만 일부는 계산을 깨뜨립니다. 결제 대행사 및 동기화 프로세스로 인해 중복 OrderID가 생성될 수 있으며, 수량이나 가격이 null로 인해 총계와 시각적 정보가 깨질 수 있습니다. null이 올바르게 처리되지 않으면 결과가 오해될 수 있습니다.

중복 삭제:

• 주문 ID를 선택하세요. 및 주문 날짜 키로 사용(이렇게 하면 합법적인 당일 반복 주문이 제거되지 않음)
• 홈으로 이동 탭>> 중복 제거를 선택합니다.

• 완전히 깨진 행만 제거:
  • 홈으로 이동 탭>> 빈 행 제거 선택 (OrderID가 null인 행을 먼저 필터링)

값 바꾸기:

• 할인이 비어 있으면 할인이 없음을 의미하는 경우 null을 0으로 바꿉니다.
• 모든 수량 및 가격 열 선택
• 변환으로 이동 탭>> 값 바꾸기 선택 :
  • 찾을 값:null
  • 다음으로 바꾸기:0

텍스트 값 정리:

• Stock_과 같은 불필요한 접두사를 제거하세요.
• 크기 열 선택
• 변환으로 이동 탭>> 값 바꾸기 선택 :
  • 찾을 가치:주식_
  • 다음으로 바꾸기:(비워두기)

이제 크기 값이 깨끗하고 읽기 쉽게 나타납니다.

반복되는 값 작성:

• 주문의 첫 번째 행에만 OrderID 또는 고객 이름이 포함되는 경우도 있습니다.
• 열 선택
• 변환으로 이동>> 채우기를 선택하세요.>> 아래로 선택

중요 주의사항: 모든 null을 맹목적으로 바꾸지 마십시오. 누락된 값은 '해당 없음', '알 수 없음' 또는 데이터 문제를 나타낼 수 있습니다. 값을 바꾸기 전에 항상 비즈니스 의미를 이해하세요.

계산된 맞춤 열 생성

전자상거래 데이터에서 수익 또는 마진 열을 파생할 수 있습니다. Excel 수식을 사용하여 이 작업을 수행할 수 있지만 새로 고칠 때 중단됩니다. 파워 쿼리 사용자 지정 열은 자동으로 다시 계산되며 ETL 파이프라인의 일부로 유지됩니다.

순_수익:

• 열 추가로 이동합니다. 탭>> 맞춤 열을 선택합니다.
• 이름을 입력하세요
• 다음 수식을 삽입하세요

[Unit_Price] * [Qty] * (1 - [Discount_Pct])

총이익:

[Net_Revenue] - ([Cost_Per_Unit] * [Qty])

마진_퍼센트(%):

if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]

• 데이터 유형을 명시적으로 설정:Net_Revenue 및 Gross_Margin을 통화로 설정 , Margin_Pct를 백분율로

중요: 항상 나눗셈 공식을 보호하세요. 분모에 0이 있으면 파워 쿼리의 전체 열에 오류가 발생할 수 있으며 로드 중에 행이 삭제될 수 있습니다. if [X] =0이면 null else … 패턴을 사용하세요. 계산된 비율에 대해.

고성능 요약 테이블을 위한 그룹화 및 집계

수백만 개의 행이 포함된 원시 전자 상거래 파일은 보고서 속도를 저하시킬 수 있습니다. 그룹화는 드릴스루 분석을 위한 세부 쿼리를 유지하면서 대시보드에 대한 효율적인 집계 테이블을 생성합니다.

단계:

• 변환으로 이동>> 그룹화 기준을 선택합니다.
• 카테고리 선택>> 집계 추가를 클릭하세요. :
  • 순수익 합계
  • 평균 Margin_Pct
  • Order_ID 수(행)

• 새 검색어 이름을 Sales_Summary로 지정합니다. 원본을 Sales_Detail로 유지합니다. 드릴스루용

전문가의 팁: Power BI에서는 세부 쿼리에서 "로드 활성화"를 비활성화하고 요약 및 차원 테이블만 로드하여 성능을 향상시킵니다.

마지막 단계:

• 홈으로 이동 탭>> 닫기 및 적용을 선택합니다. 데이터를 로드하려면

결론

이제 이러한 5가지 필수 파워 쿼리 변환을 전자 상거래 판매 데이터에 적용할 수 있습니다. 파워 쿼리는 반복 가능한 방식으로 단순 변환과 복잡한 변환을 모두 처리하므로 전자 상거래 데이터 세트를 정리하는 가장 효과적인 도구 중 하나입니다. 데이터 준비 시간을 대폭 단축할 수 있습니다. 이는 일반적인 정리 단계가 아닙니다. 실제 전자상거래 보고 문제를 직접적으로 해결하고 다운스트림 분석을 훨씬 더 효율적으로 만듭니다. 이러한 변환에 익숙해지면 모든 새로운 마켓플레이스 내보내기에 대해 재사용 가능한 파워 쿼리 템플릿을 구축할 수 있습니다.