파워쿼리 - 파워쿼리의 강력함 그냥 따라해보기

파워쿼리의 강력함에 한번 빠져 봅시다~~~

파워쿼리의 강력함을 아무리 거품을 머금고 떠들어 봐야 한번 해보는거 만큼 그 이상이 있을까?

그냥 따라하다 보면 아 이걸 왜 써야 하는지 얼마나 강력한지 그냥 느낄 수 있을것이다.

다음과 같은 데이터가 있다고 가정 해보자

그렇게 셀 병합하지 말아라, 양식과 데이터는 분류해라 , 데이터 구조는 단순화 해라라고 말을 해도 기존에 그렇게 파일을 쓰고 있으니 어쩔 수 없고, 아직 그렇게 쓰기엔 숙달이 되지 않았으니 어쩔 수 없는 등 다양한 이유가 있을것이다. 

위와 같은 데이터는 RawData일까 양식일까? 

양식이다. 왜 양식일까?

이렇게 정의 내리면 더 명확하게 이해 될수 있을것 같다. RawData는 명확하게 그 자체를 가지고 피벗테이블을 돌릴수 있으면 RawData가 맞다. 그런데 위 데이터를 기준으로 피벗테이블을 돌릴 수 있을까?

사원별 제품 생산 단위 합계, 평균, 최대값을 내려면 데이터를 다시 찢어야 하는 상황이 발생 된다. 정확하게 위 데이터는 크로스탭 구조를 갖는 양식이다. 각 값은 왼쪽에 이름과 생산품의 결과이다. 이 데이터를 분석하기 위해선 적어도 원본 테이블 구조를 다음과 같이 만들어야 한다.

이렇게 작업을 하기 위해선 각 줄의 값을 복사해서 붙여넣기에 가로세로 변환을 하여 붙여 넣고.....이런 작업을 줄수만큼 반복해야 한다. 한 50정도는 할만 할지도 모른다. 그런데 만약 데이터 줄수가 몇 백개이면?

파워쿼리는 이런 작업을 한번에 그것도 클릭질 몇번으로 가능하게 하니 어찌 이걸 해야 한다고 침을 튀며 이야기 하지 않을 수 있을까?

자 이제부터 저 지랄맞은 원본 데이터를 어떻게 깔끔하게 최소한의 노력으로 깔끔 스럽게 떨궈내는지 파워쿼리 그냥 함 해 봅시다~~~

예제파일 :파워쿼리예제1.xlsx

[2016 버전으로 설명되므로 2010,2013 버전 사용자들은 파워쿼리 설치 후 파워쿼리 메뉴에서 보면됨]

1. 데이터 내부 한곳을 선택 한 후 리본메뉴의 [데이터]-가져오기 변환 항목에서 테이블에서를 선택 한다.

2. 데이터 원본이 표가 아니기 때문에 표 만들기 메뉴가 나오고 첫글머리 사용 부분이 나오는 것을 확인 할 수 있을것이다. 여기서 그닥 중요한 것은 아니다. 그냥 데이터 전체를 범위로 잡았는지 그리고 나중에 작업의 수고를 덜기 위해서 글머리 포함이 체크 되었는지만 확인 하면 된다. 그런데 이 예제에서는 상단에 셀이 병합되어 있으므로 사실상 글머리포함은 의미가 없다.

3. 2에서 확인을 하고 다음으로 넘어가면 드디어 그 말로만 듣던 파워쿼리의 편집창이 뜨게 된다. 파워쿼리의 편집창에 대한 설명은 다음에 상세히 하기로 하고 이번 포스트에선 그냥 따라해 보면서 이넘이 도대체 머하는 넘인지만 한번 느껴 보면 된다. 그런데 잘 보면 원본 첫 행이 병합이 되어 있어서 파워쿼리는 행은 중요한 정보가 아니라 필드값 즉 열이 중요하기 때문에 무시된 것을 볼 수 있다. 좀더 상황에 맞게 표현하면 병합이 풀렸다.

4. 여기서 첫번째 스킬 들어 갑니다~~  파워쿼리 편집기의 리본 메뉴의 홈 메뉴에서 첫행을 머리글로 사용이라는 메뉴가 보일 것이다. 이 기능을 설명 해야 할까요? 안해야 할까요?~~~어려우신분 한번 잘 생각해보세욧!!!!

셀 병합이 깨진 첫 줄은 사라지고 이제 제대로된 필드명 즉 열 이름이 자리를 잡았다.

5. 제일 어려운 개념이다. 이제 여기가 고비이니 여길 꼭 잘 무사히 넘어가서 꼭 살아서 만나길 바란다.

데이터 구조를 보면 사번 성명까지는 괜찮은데 H1에서 부터H8 까지는 제품명이다. 지금 원본 자체가 피벗이 되어 있다고 보면 된다. 잘 생각 해보다 피벗테이블의 행 영역에는 사번과 성명이 열 영역에는 제품명이 적용된 그런 피벗이 적용된 형태다. 그러니 우리는 꺼꾸로 분석을 하기 위해서 이 피벗을 깨야된다. 즉 피벗의 반대이니까 이름하여 언피벗 unpivot ~~~ unpivot의 대상은 제품명이니 제품명 글머리를 선택하면 열 전체가 선택되는걸 확인 할 수 있을 것이다. 첫번째 H1 글머리 클릭하고 shift 키 누른 상태에서 H8 선택하여 영역전체를 선택한다. 그리고 변환 메뉴에서 열 항목의  열 피벗해제를 누르면 두둥~~~!!!

6. 변환된 결과에서 글머리를 두번 클릭 하여 필드명을 다시 바꿀 수 있다.

7. 이제 필드 이름까지 다 바꿨다면 상단의 홈 메뉴의 닫기 및 로드를 선택하면 엑셀에 새로운 시트에 우리가 원하는 결과물이 표로 생성되는 것을 확인 할 수 있을 것이다.

8. 여기서 더 대에박~~~원본에 데이터를 새롭게 더 추가 하고 데이터 모두 새로고침인 Ctrl+Alt+F5를 누르면 그 추가된 데이터가 적용된것을 확인 할 수있을것이다. 이제 쓰던 양식 그대로 데이터만 추가하면 끝

정리

파워쿼리는 간단한 함수를 클릭만으로도 기능을 적용 할수 있게 되었다. 또한 작업절차가 기록이 되기 때문에 작업루틴이 매번 똑같이 반복되는 작업에선 매크로와 같은 기능까지 해 주게 된다. 이렇게 표로 나온 결과를 기반으로 다시 피벗테이블을 이용해서 분석을 하게 되면 이제 데이터만 추가하기만 하면 되는 것이다.