파워쿼리를 활용한 조합 데이터 만들기

이번 포스팅은 조합 데이터를 쉽게 만들고 관리할 수 있는 팁에 관한 내용입니다. 

데이터를 다루는 일을 하다 보면 가끔 조합 형태의 기준 자료가 필요할 때가 있습니다.

예를 들어 어떤 제품의 특성을 '색상', '크기', '용도' 로 나누어 볼 때 각각의 특성 별로 구성 가능한 조합을 미리 만들어 봐야 할 경우가 있죠. 물론 조합을 한개씩 타이핑 하여 만들 수도 있지만 예시처럼 3가지 특성을 조합 할 때도 제법 많은 경우의 수를 고려해야 합니다. 일일이 타이핑하는 것이 쉬운 일이 아닌 경우가 꽤 됩니다. 

위 표와 같은 경우만 해도 색상(4)*크기(3)*용도(3*) = 36개의 조합이 필요하죠. 

조합

빨강-대-판매용

빨강-대-증정용

빨강-대-개발용

빨강-중-판매용

…....

이런 모양의 데이터가 얻고 싶은 데요. 손이 고달프죠. 

이걸 한방에 처리 하는 방법. 그리고 각 속성의 항목이 추가 되었을 경우에도 쉽게 다시 만드는 방법을 소개합니다. 

1. 파워피벗을 이용한 모든 조합을 한번에 만들기   

제가 사용하는 방법 중 파워피벗을 사용한 전체 조합을 만드는 방법을 먼저 소개합니다.

파워피벗을 사용해야하니 일단 표(Table)로 변환을 해야 겠죠. 그리고 데이터 모델로 구성이 필요합니다. 

2019.07.04 - [엑셀/파워피벗 공부] - [파워피벗 공부] 1. 데이터 모델 만들기

[파워피벗 공부] 1. 데이터 모델 만들기

요 부분은 이전 글 참조 또는 구글링 하시면 될 것이고요. 

표로 각 속성을 변환하고

데이터 모델에 추가

요기까지 하고 '피벗테이블'을 만들어주면 됩니다. 

피벗테이블을 만들어보면 아래와 같이 각 데이터모델이 모두 피벗 테이블 필드 창에 표시됩니다. 

파워피벗 테이블로 생성

이제 각 테이블의 필드를 순서대로 행에 집어 넣습니다. 이렇게 되면 아래와 같이 모든 조합이 생성됩니다. 

음.... 제가 처음 이 방식을 떠올린 원리는 SQL로 Select 문을 조인 없이 그냥 실행했을 경우에 기초한 건데요. 

 3개 Table이 아무 관계도 같지 않았기 때문에 모든 경우를 다 조합해서 데이터의 출력이 이루어지는 거죠. 이걸 부르는 말이 있는데 생각이 안나네요. ㅜㅜ; 

이렇게 한번 조합이 구성이 되면 새로운 값이 들어와도 새로고침만 하면 계속 데이터가 추가가 되니 간단하게 전체 조합을 유지 할 수 있습니다. 만약 '제품등급' 이란 속성이 추가되면 표를 하나 늘려주기만 하면 됩니다. 간단하죠. 

그러나 여기서 문제가 하나 발생합니다. 

조합에 전제가 걸리는 것이죠. 예를 들어 '색상'이 '검정' 인 경우는 '개발용'으로만 조합되어야 하는 제한이 필요하다면 어떻게 해야 할까요? 물론 값복사 한 후에 해당 조합을 지워버리면 됩니다. 조합의 조건이 간단하면 그냥 손으로 지우는게 훨씬 빠릅니다. 

하지만 데이터 양이 많고 조합의 조건이 복잡하며 자주 변경될 수 있어 꾸준히 관리가 필요한 경우에는 어떻게 해야 할까요? 

이 글의 제목이 "파워쿼리를 활용한 ... "  인 이유가 있는 것이죠. 

2. 조합의 구성 조건이 있는 경우 파워쿼리를 활용한 조합 관리 

위 방식 보다는 조금 손이 더 가는 작업입니다. 

이 방식의 장점은 관리가 편리하다는 것이죠. 조합의 조건을 시뮬레이션 할 수 있습니다. 

먼저 데이터는 아래와 같이 준비합니다. 

조합을 피벗팅된 상태로 표로 구성

위 표(Table)과 같이 조합을 피벗팅이 된 상태로 표를 구성합니다. 일단은 전체 조합을 다 채운 상태로 시작합니다. 

이제 이 표(Table)를 파워쿼리로 생성하고 작업을 아래와 같이 수행합니다. 

작업단계

저는 "적용된 단계"가 영문으로 되어 있는데 이건 고급편집기 사용시 조금 편하게 하려고 '항상영어'를 선택해서 그렇습니다. 수행한 내용은 아래 순서입니다. (영문상태로 작업을 해버리다보니... ㅡㅡ;) 

1 열피벗 해제 - 색상

2 열피벗 해제 - 크기

3 열피벗 해제 - 용도

4 필요없는 필드 삭제

5 열병합 - 조합 필드 만들기

6 필드명 변경

파워쿼리로 불러온 후에 먼저 각 필드를 묶어서 색상->크기->용도 순으로 피벗팅을 각각 열피벗 해제를 수행합니다. 

열 피벗 해제

그리고 필요없는 '특성' 필드를 삭제하고 열병합 기능으로 조합 필드를 만들어 줍니다. 

조합으로 새로 만든 필드를 기준으로 중복된 항목 제거를 수행하고 깔끔하게 필드명을 변경해주면 됩니다. 

엑셀 시트에 로드를 해볼까요. 

1번 파워피벗을 사용한 모든 조합일때와 동일하게 36가지의 조합이 로드됩니다. 

자. 이제 여기서 조건을 걸어 봅시다. 

 >> 색상이 '검정'인 경우에는 크기가 '대' 만 조합 

원본 표(Table)에서 색상4의 검정을 1개만 남기고 그 행에는 크기를 '대' 만 남겨 놓은 상태에서 새로 고침을 하면 출력에 검정은 크기가 대인 것만 로드됩니다. 

저는 이 방식을 키(key)가 되는 필드를 정해 놓고 이리 저리 조합을 구성하면서 사용해서 복잡한 경우는 테스트해보지 않았습니다. 위 방식대로 만들어 보시고 이것 저것 수정해가면서 연구하시면 보다 개선된 방법도 나올 것 같네요. 

노랑은 중,소 인 크기와 판매용,증정용만 으로 조합 조건을 추가

3. 후기   

파워쿼리로 조합 조건을 만드는 이 방식은 최근 일하고 있는 회사에서 정육 제품의 부위 별 4가지 제조특성 조합을 만드는 과정에서 처음 사용해본건데요.  항상 전체 조합을 만들고 불필요한 조합을 지워서 사용하는 1번 방식만 사용하다가 이런 저런 특성으로 조합을 계속 변경하면서 결과를 확인하기 위한 방법을 고민하다가 나온 꼼수입니다. 

경영 계획이나 예측 데이터를 구성할 때 기초 작업으로 활용도가 높을 것으로 생각됩니다.