PQ (table merge) before-after reconciliation 변경 전 후 비교/대사

20220601 1100

REF
[1] "https://support.microsoft.com/ko-kr/office/%EC%BF%BC%EB%A6%AC-%EB%B3%91%ED%95%A9-%ED%8C%8C%EC%9B%8C-%EC%BF%BC%EB%A6%AC-fd157620-5470-4c0f-b132-7ca2616d17f9"
[2] "https://blog.naver.com/PostView.nhn?blogId=jena-k&logNo=222255039999" 왼쪽/오른쪽/양쪽 외부조인 및 내부조인의 설명
개요
지난 주 거래처별 물품 판매 내역을 이번주와 비교/대사하고 싶다.

(결과물)
거래처, 지난주 판매액, 이번주 판매액, 변동
갑
을
병
정

엑셀로 무작정 한다면..
지난주 거래처와 이번주 거래처 이름을 모아서 중복을 제거하여 목록을 만들고
vlookup을 써서 지난주 판매액, 이번주 판매액을 달아준다.

파워쿼리로 해보자
(매일 하는 일이라면 자동화가 필요하니까..)

예제 데이터 구조
(문제점1) id가 있으나 unique하지 않다
거래처 하나(id)에 여러 개의 물건(item)이 있다. 예를 들어 id=x1에 item=사과, 배 등등이 있다.
따라서 id만으로 두 테이블을 병합하면 문제가 생긴다.
(해결책) id와 item을 연결해 새로운 key를 만든다.

(문제점2) 지난 주에 있던 거래처가 없어질 수도 있고(을,a), 이번 주에 새로운 거래처가 생길 수도 있다(정,b)
1) 지난 주 거래처 기준으로 병합하면 b 경우가 누락된다.  ⟶ 왼쪽 (외부)조인
2) 이번 주 거래처 기준으로 병합하면 a 경우가 누락된다.  ⟶ 오른쪽 (외부)조인
3) 양쪽으로 모두 병합하면 a 경우에 해당하는 정보가 이번주 필드에는 없고, b 경우에 해당하는 정보가 지난주 필드에는 없다(아래 '1.1 결과' 그림 참조).  ⟶ 양쪽(완전) 외부조인

(해결책)위 3번방법으로 병합한 뒤, 양쪽에 동시에 필요한 필드를 만들어 준다. 예를 들어 거래처명2(아래 예제에서 'mCustomer' 필드 같은 것)를 만들어 준다.
a경우면 지난주 거래처명에서 정보를 가져오고, b 경우면 이번주 거래처 명에서 정보를 가져오는 필드를 만든다.

(Theorem) "거래처명 & 상품명"으로 새로운 key를 만들어 두 테이블을 병합할 경우, 동일한 레코드에서 지난주 거래처명과 이번주 거래처명이 상충되는 경우는 발생하지 않는다. 즉 다음의 경우만 발생한다.
i) 지난주 거래처명은 있고 이번주 거래처명은 없거나
ii) 지난주 거래처명은 없고 이번주 거래처명은 있거나
ii) 지난주 거래처명과 이번주 거래처명이 동시에 있으면서 둘이 같은 경우


실습

1. 병합

(경로) 파워쿼리가 아니라 엑셀에 메뉴가 있다. Data > Get & Transform > New Query > Combine queries > Merge

새로 병합 쿼리를 만드는 메뉴가 없다*. 그래서 빈 쿼리를 만들고 아래 명령을 수동으로 입력
*pc in my home has the menu different from pc in office ㅠㅠ;
* 사무실에 있는 MS office version = Professional Plus 2016 (Excel > File > Account에서 확인 가능)

= Table.NestedJoin(t_before,{"key"},t_after,{"key"},"t_after",JoinKind.FullOuter)

1.1 결과

1.2 Diff필드 추가
i) 그전에 '금액'과 't_after.금액'의 null을 0으로 바꿔주고
ii) mCustomer 필드를 추가

= Table.AddColumn(#"Added Custom1", "mCustom", each if [Customer] = null then [t_after.Customer] else [Customer])

2. 최종결과
피벗테이블을 만들면...완성

파일

20220601 2246 exercise_table merge_recon.xlsx

0.29MB