[파워쿼리기능소개] 5-3. 변환 - 홈 탭의 변환 기능

홈 탭에 위치한 변환 기능의 마지막 편인 '그룹화'입니다. 그룹화는 SQL의 그룹 함수와 같이 쓰이는 Group by와 동일한 역할을 합니다. 특정 필드를 기준으로 데이터를 그룹으로 묶어주고 합계, 행 카운트 등을 처리할 수 있습니다. 

1. 그룹화 란

기능의 사용 설명 전에 그룹화를 엑셀의 피벗테이블로 데이터를 요약하는 것과 비교하여 개념을 소개 합니다. 

 

파워 쿼리의 그룹화도 동일한 기능을 수행합니다. 그룹화는 실제 로드 될 데이터를 요약해서 저장한다고 생각하시면 편합니다. 위의 케이스를 파워 쿼리 그룹화로 처리하면 다음과 같습니다. 

학생 이름 필드 기준으로 그룹화를 수행하면서 새로운 열을 생성합니다. 새 열 이름을 지정하고 연산 방식을 '합계'로 지정하고 연산할 대상 열을 점수로 선택하고 확인을 누르면 다음과 같이 요약됩니다. 

연산의 종류는 합계, 평균, 최대, 최소, 중앙값, 행카운트 등이 있습니다. 

2. 활용

그룹화 기능은 어떻게 활용 될까요? 변환을 하는 여러 과정에서 사용되지만 가장 좋은 예는 단가 테이블을 구성할 경우가 될 것 같습니다. 거래 데이터를 토대로 단가를 산출하는 쿼리를 만든다고 하면 한번 만들어 놓은 쿼리에 추가 거래 건이 더 해질 때마다 단가 테이블에 바로 반영이 되겠지요. 

또 비슷한 케이스로 입출 거래 데이터를 원본으로 현재 재고량을 파악할 수 있도록 현재고 계산 테이블도 만들 수 있습니다. 입고를 +값으로 출고를 -값으로 정리한 거래 데이터가 있다면 전체의 합계를 통해 각 품목/창고/위치 등의 기준 필드 값 별로 현재 재고 값을 쉽게 뽑아낼 수 있습니다. 

좌측의 매출 데이터를 파워쿼리로 가져와서 그룹화-> 사용자 지정 열 추가로 매출단가를 계산하였습니다. 이렇게 되면 매출 데이터가 추가되어도 파워 쿼리로 로드해놓은 단가 리스트는 새로고침으로 자동 갱신됩니다. 

물론 단순히 단가를 확인하기위해서 이런 방법을 사용하기는 조금 복잡합니다. 일반 피벗테이블이나 간단한 함수 Sumifs와 같은 함수를 통해서도 쉽게 구할 수 있기 때문이죠. 단가 리스트는 갱신된 최신 단가 기반으로 다른 쿼리와 조인하여 새로운 값을 만들어 내는 중간 테이블의 역할로 더 많이 사용됩니다. 

예를 들면 오늘까지의 거래를 반영한 단가 기준 예상 손익 등 단가정보를 이용하여 추가 작업이 필요한 경우에 더 유용할 것으로 생각됩니다. 현재고 테이블도 마찬가지입니다. 파워쿼리 소개 시리즈를 완료하면 다음에는 업무 시나리오를 바탕으로 "작성하기" 시리즈를 계획하고 있습니다. 파워쿼리로 수불부 만들기 편 예고합니다. 

단가테이블이 로드된 모습

그 외에도 그룹화는 데이터의 유니크한 값이나 건수를 요약하기 위해서 종종 사용됩니다. 데이터 분석용으로는 피벗테이블이 더 우수합니다만 쿼리로 변환을 진행하는 과정에 화면을 오가는 일이 불편해서 그룹화를 활용하기도 합니다. 

중복 제거를 통해서도 확인 할 수 있지만 데이터 건수 등을 요약할 수 있으므로 활용 관점이 조금 다릅니다.