[파워쿼리] 파워쿼리 쿼리 추가 - union all

지난 쿼리 병합 포스팅에 이어 오늘은 쿼리추가 에 대해서 정리를 해보자. 

쿼리 병합은 조인이다. 그럼 쿼리 추가는 ?? 

포스팅 제목 처럼 SQL 의 'Union all' 인 셈인데, 조인은 엑셀에서 처리가 어려우니 당연히 파워쿼리로 처리하는 것이 데이터 다루는데 좋다는 것이 명확한다. 그러나 '쿼리추가'는 언제 써야 하나? 

왜 이런 고민이 드냐하면... 데이터 전처리는 엑셀 그자체로 너무 훌륭한 편? 이기 때문에 데이터를 더 붙일 일이 있으면 수고 스럽지만 그냥 시트에서 복사해서 붙여넣기 작업을 하면된다. 굳이 어렵게 쿼리 추가를 익힐 이유가 없다. 하지만 파워쿼리로 익혀두면 발생되는 장점, 데이터 전처리의 자동화를 생각해보면 솔깃할 수 밖에 없을 것이다.

데이터 수집 과정에서 흔히 겪는 문제인 두가지 데이터를 하나의 테이블로 만들기를 가지고 '쿼리 추가' 기능을 소개한다. 

데이터는 요즘 계속 사용되고 있는 판매 계획과 실적 데이터 이다. 

이 두개의 테이블을 하나의 테이블로 합치는 파워쿼리를 만들것이다. 

실적 데이터와 계획 데이터의 차이는 우선 실적은 일자 기준이고 계획은 월 기준인 점과 계획에는 결제방식에 대한 정보가 없다는 점 이 두가지다. 

그럼 두개의 테이블을 하나의 테이블로 합치려면 계획이 가지고 있는 정보가 더 적으므로 계획에 기준을 두어야 한다. 만약 손으로 작업을 한다면 어떻게 할까? 계획을 그대로 사용하고 실적 부분을 일자기준을 월로 그리고 결재방식을 제외한 상태로 합계를 내어 계획 데이터 맨 아래에 구분 필드를 두어 붙여넣게 될 것이다. 

만약 엑셀 기능만으로 한다면 

위의 그림과 같이 실적 테이블에서 월을 month 함수로 추출하고 그상태로 피벗테이블을 구성하여 결재방식을 뺀 나머지로 요약하여 이 결과를 구분열에 실적이란 단서를 달아 붙여넣었을 것이다. 전혀 나쁜 방법이 아니다. 빠르고 실수의 여지도 거의 없으며 작업의 과정과 추적도 용이한 좋은 방법이다. 하지만 계속 반복되는 작업이라면 아무리 좋은것도 굳이 여러번 할 필요는 없으니까 파워쿼리를 써보자. 

먼저 테이블을 출처로 파워쿼리를 생성하자. 판매실적과 판매계획을 각각 파워쿼리로 추가한다. 

각 테이블을 선택하고 '데이터>테이블/범위에서' 를 눌러 파워쿼리를 만들자. 각각 따로 만들어야 하는데 파워쿼리 창이 뜨면 엑셀 창은 비활성화 되므로 판매실적을 만들고 파워쿼리 메뉴의 '홈>닫기 및 로드' 로 창을 닫아야 한다. 이때 '닫기 및 다음으로 로드' 를 선택하면 아래와 같이 로드와 관련된 옵션을 선택할 수 있다. 

데이터 로드 관련 옵션

엑셀 시트에 데이터를 출력할 방법을 정할 수 있다. 표,피벗 테이블, 차트 가 가능하며 출력없이 연결만 만들 수도 있다. 나는 보통 엑셀에 있는 값을 가지고 처리 할 경우 '연결만 만들기'로 중간과정의 쿼리는 출력하지 않는다. 새로고침 시간만 잡아먹기 때문이다. 

이제 작업은 그냥 엑셀로 하는 방법과 동일한 일을 파워쿼리로 하면 된다. 하지만 오늘은 '쿼리추가(union all)' 기능소개가 목적이니 바로 쿼리 추가를 해보자. 

'홈>쿼리추가>쿼리를 새 항목으로추가'를 클릭한다. 

추가 창에서 기본테이블과 여기에 추가될 테이블을 선택하고 확인을 클릭한다. 

두개의 쿼리가 가지고 있는 항목 중 동일한 열은 같은 열로 처리하고 이름이 다른 열은 모두 표시하는 형태로 두개의 데이터가 붙어버렸다. SQL 의 Union all 은 두개의 쿼리의 Select 내용이 동일해야 하나 파워쿼리의 쿼리추가에서는 그런것을 따지지 않는다. 그냥 시원하게 다 띄워버린다. 

그럼 이상태에서 매출실적의 매출일자에서 월을 추출하고 판매계획의 구성 항목 기준으로 데이터를 그룹핑 하면 완성된다. 

완성된 쿼리

쿼리 추가로 실적과 계획의 두 테이블을 합치는 새로운쿼리가 완성되었다. 

이제 동일한 형태의 데이터를 새로 넣어 주기만 하면 두 테이블을 합치는 작업은 더 이상 하지 않아도 된다. 

이 과정은 원가, 경영관리 담당자들이 피벗테이블을 통해 자료를 요약/분석하고자 하는 전처리 과정 중 가장 대표적인 작업이다. 내가 일을 하면서 경험한 것 회사들도 대부분 이 작업이 없는 경우가 없었다. 

프로그램으로 다 만들기에는 너무 정규적이지 않고 그렇다고 반복되는 일이 귀찮을때 파워쿼리를 찾자. 답이 있다. 

일찍 퇴근합시다. 

Data_품목_매장_판매실적_쿼리추가_union all.xlsx

0.47MB

파워쿼리를 첨부된 파일에 데이터를 바꾸어서 실습해보면 이해가 빠를 것 같네요. 꼭 손으로 익혀야 합니다.