1. 합집합, 교집합, 차집합
A집합과 B집합이 있을 때
두 개를 합치면 합집합,
겹친 부분은 교집합,
A에서 B를 빼거나 B에서 A를 빼면 차집합입니다.
그런데, 파워 쿼리에서는
A집합을 기준으로 A집합의 요소와 일치하는 B집합의 요소를 결합하는 것을 왼쪽 외부 조인,
반대로 B집합을 기준으로 B집합의 요소와 일치하는 A집합의 요소를 결합하는 것을 오른쪽 외부 조인,
A집합에서 B집합을 빼는 차집합이 왼쪽 앤티 조인, B집합에서 A집합을 빼는 차집합이 오른쪽 앤티 조인,
합집합은 완전 외부 조인, 교집합은 내부 조인으로 분류하고 있습니다.
파워 쿼리에서 쿼리가 위,아래로 표시되는데, 왼쪽과 오른쪽이라고 표현하는 이유는
오라클, MySQL 등 데이터베이스의 질의어인 SQL(Strucrtured Query Language)에서 사용하는 Join 명령이 Left, Right로 표현되기 때문입니다.
(조인의 종류)
2. 왼쪽 외부 조인(Left Outer Join)
Vlookup과 비교하기 위해 이전에도 다뤘지만
여러 가지 경우를 가정하여 상세하게 다뤄보겠습니다.
경우의 수는 찾고자 하는 데이터가 오른쪽에 모두 또는 일부만 있는 경우와 모두 없는 경우 3가지 입니다.
왼쪽에 일자별, 지점별 매출액이 있고, 오른쪽에는 지점별 지점장이 있습니다.
지점명에 해당하는 지점장을 지점명 오른쪽에 표시하는 것을 해보겠습니다.
가. 찾고자 하는 왼쪽 데이터가 오른쪽에 모두 있는 경우
위 표는 지점명이 A,B,C인데, 오른쪽에 A,B,C가 모두 있습니다.
(1) 표1 쿼리 만들기
왼쪽 표에 커서를 넣고 데이터 탭에서 테비블/범위에서를 클릭합니다.
그러면 표가 아니기 때문에 표 만들기 창이 표시되면서 범위가 자동으로 설정되고, 머리글 포함에도 체크가 자동으로 되어 있습니다. 맞으므로 확인 버튼을 누릅니다.
그러면 파워 쿼리 편집기 창이 열리면서 데이터의 형식이 변환되는데,
워크 시트에서는 날짜로만 보이는 것이 날짜시간 형식으로 잘못 표시(변환)되고 있으므로,
일자 왼쪽의 날짜시간표시 아이콘을 누른 후 날짜를 클릭합니다.
그러면 '현재 전환 바꾸기', '새 단계 추가', '취소' 버튼 3개가 나오는데,
'현재 전환 바꾸기'는 단계를 추가하지 않고 현 단계의 날짜시간 표시형식을 변경하는 것이고,
'새 단계 추가'는 단계를 추가한 후 날짜시간 표시형식을 변경하는 차이점이 있습니다.
새 단계를 추가할 필요가 없으므로 '현재 전환 바꾸기'를 누릅니다.
그러면 날짜 형식으로 바뀝니다.
그리고, 윗 부분 수식 입력줄을 보면 type이 datetime에서 date로 바뀌었습니다.
따라서, 위와 같이 날짜시간 아이콘을 누르지 않고, type 다음의 datetime을 date로 바꿔서 날짜 형식으로 바꿀 수도 있습니다.
이제 닫기 및 로드 아래 부분을 누르고, 닫기 및 다음으로 로드를 누릅니다.
그러면 파워쿼리 편집기가 닫히면서 '데이터 가져오기' 창이 열리는데, 기본 값이 표입니다. 그외에도 피벗 테이블 보고서, 피벗 차트, 연결만 만들기가 있는데, 연결만 만들기를 선택하고 확인 버튼을 누릅니다.
연결만 만들기를 선택해도 나중에 표나 피벗 테이블 등을 쉽게 만들 수 있고, '연결만 만들기'가 속도가 빠르기 때문입니다.
(2) 표2 쿼리 만들기
오른쪽 표 영역에 커서를 두고, 위에서 한 것처럼
데이터 탭 - 테이블/범위에서를 누르고, 표 만들기를 하면
파워 쿼리 편집기가 열리면서 표2란 쿼리가 생기는데,
데이터 형식이 텍스트로 잘 되어 있어 변경할 것이 없으므로
이번에는 표1과 표2 쿼리가 만들어졌으므로 닫기 및 로드 아래 부분을 눌러 파워쿼리 편집기를 닫지 않고 작업이 가능합니다.
(3) 쿼리 병합
왼쪽 외부 조인을 해야 하므로 표1을 선택하고,
홈 탭에서 쿼리 병합 오른쪽 콤보 상자 버튼을 누른 후 '쿼리를 새 항목으로 병합'을 선택합니다.
윗 부분 '쿼리 병합'을 누르면 표1에서 병합 작업을 해서 현재 상태의 표1을 다시 사용할 수 없기 때문입니다.
그러면 위에 병합이 표시되고,
윗 부분에 표1이 열려 있고, 아래 부분에는 쿼리가 선택되어 있지 않으므로, 클릭한 후 표2를 선택합니다.
표2가 열립니다.
이제 비교할 열(일치하는 열)을 선택해야 하므로 표1에서 두번째 열인 지점명을 클릭하고, 표2에서 첫번째 열인 지점명을 클릭하고, 아래 조인 종류를 보면 왼쪽 외부로 되어 있으므로 확인 버튼을 누릅니다.
그러면 왼쪽 쿼리 창에 병합1이 추가되면서 오른쪽에 표가 만들어지는데, 매출액 다음에 표2로 해서 Table로 표시되어 있습니다.
표2 오른쪽의 확장 버튼을 누르면
아래에 열 이름, 지점명과 지점장이 표시되는데, 지점명은 중복되므로 체크를 해제하고,
그 아래 '원래 열 이름을 접두사로 사용'에 체크가 되어 있는데, 여기에 체크가 되어 있으면 열 이름이 표2.지점장이 되므로 체크를 해제하고 확인 버튼을 누릅니다.
그러면 매출액 오른쪽에 지점장이 표시됩니다.
지점장의 위치를 변경하려면 열 이름 지점장을 클릭 한 다음 지점명 오른쪽으로 끈 다음 놓으면 됩니다.
그러면 아래와 같이 지점명 오른쪽에 지점장이 표시됩니다.
(4) 열 이름을 접두사로 사용의 체크를 해제하지 않은 경우
만약 위에서 '원래 열 이름을 접두사로 사용'의 체크를 해제하지 않았다면
오른쪽 쿼리 설정 창의 '확장된 표2' 오른쪽의 설정 아이콘을 누른 후
기본 이름 접두사에 표시된 '표2'를 지우고 확인 버튼을 누르면 됩니다.
(5) 병합1 표 만들기
'닫기 및 로드' 아래 '닫기 및 다음으로 로드'를 누르고,
'연결만 만들기'에 체크하고 확인 버튼을 누릅니다.
이 때 '닫기 및 로드' 윗부분을 누르면 병합1 시트외에 쓸데 없이 표2 시트까지 생기기 때문입니다.
'연결만 만들기'만 누르면 아래와 같이 시트는 변함이 없고, 오른쪽 쿼리 및 연결 창에 표1외에 표2와 병합1이 생깁니다.
왼쪽 외부 조인된 결과를 시트에 표시하기 위해서는
병합1 쿼리에 마우스 커서를 올려놓고, 마우스 오른쪽 버튼을 누른 후
'다음으로 로드' 메뉴를 누릅니다.
그러면 파워 쿼리 편집기가 닫힐 때 나오던 '데이터 가져오기' 창이 나오는데, 이번에는 표를 선택하고, 새 워크시트를 선택한 다음 확인 버튼을 누르는 것이 좋지만,
설명을 위해서 기존 워크시트를 누른 후 H1 셀을 클릭하겠습니다. 그리고 확인 버튼을 누르면
파워 쿼리 편집기에서 작업했던 내용이 워크 시트에 표로 표시됩니다.
나. 찾고자 하는 왼쪽 데이터가 오른쪽에 일부만 없는 경우
표1의 9행에 1/14, D, 2234를 입력해서 표2에 없는 지점명 D를 입력해보겠습니다.
표1과 표2가 결합된 병합1아지만 표1의 데이터가 변경돼도 바로 반영되지 않으므로
병합1 표안에 커서를 둬서 쿼리 탭이 생기도록 하고,
쿼리 탭을 누른 후 새로 고침 윗 부분을 누릅니다.
그러면 비로서 표1의 추가된 데이터가 병합1에 표시되는데, 지점명 D에 대한 지점장에 대한 데이터가 표2에 없으므로 빈 칸으로 표시됩니다.
다. 찾고자 하는 왼쪽 데이터가 오른쪽에 모두 없는 경우
표2의 데이터를 무두 지우고, 병합1에 커서를 두고 쿼리 탭의 새로 고침을 누르면
지점명이 전혀 일치하는 것이 없으므로 지점장 열도 모두 빈 칸으로 표시됩니다.
라. 오른쪽에만 왼쪽에 없는 데이터가 있을 경우
Ctrl + Z키를 눌러 표2의 데이터를 복구한 후
쿼리 탭의 새로 고침 명령을 눌러도 왼쪽 쿼리를 기준으로 데이터를 작성하므로 오른쪽에만 있는 데이터는 병합1에 영향을 주지 못합니다.
'Excel - 파워 쿼리' 카테고리의 다른 글
| 파워 쿼리 병합(3) - 완전 외부 조인 (0) | 2023.06.28 |
|---|---|
| 파워 쿼리 병합(2) - 오른쪽 외부 조인 (0) | 2023.06.27 |
| 파워 쿼리와 Vlookup, Index+Match 함수 비교(3) (0) | 2023.06.22 |
| 파워 쿼리와 Vlookup, Index+Match 함수 비교(2) (0) | 2023.06.21 |
| 파워 쿼리와 Vlookup, Index+Match 함수 비교(1) (0) | 2023.06.20 |