열이 다른 테이블(표)의 쿼리 추가

열이 동일한 경우의 병합에 대해서는

http://lsw3210.tistory.com/entry/%EC%BF%BC%EB%A6%AC-%EC%B6%94%EA%B0%80Vstack-%EA%B8%B0%EB%8A%A5%EC%99%80-%EB%B3%91%ED%95%A9Hstack-%EA%B8%B0%EB%8A%A5

쿼리 추가(Vstack 기능)와 병합(Hstack 기능)

 

에서 다뤘는데,

 

이번에는 열 이름이 다른 표를 추가하는 것을 해보겠습니다. 아래 파일은 쿼리 추가와 병합에만 집중할 수 있도록 표1과 표2를 '연결만 전용'으로 미리 만들어놓았습니다.

파워쿼리 추가 열 이름이 다를 때.xlsx

0.02MB

 

 

아래 표를 보면 일자, 지점명, 매출액은 동일한데 왼쪽에는 매출이익이 있고, 오른쪽에는 매출순위가 추가로 있습니다.

 

쿼리 추가와 완전 외부 조인의 결과를 비교해 보겠습니다.

 

 

1. 쿼리 추가

표1과 표2가 연결과 전용으로 미리 만들어져 있으므로 데이터 탭 > 쿼리 및 연결 명령을 눌러 표1과 표2 쿼리를 표시합니다.

 

표1을 더블 클릭해서 파워 쿼리 편집기를 연 다음 홈 탭 > 결합 그룹 > 쿼리를 새 항목으로 추가를 클릭합니다.

 

그러면 추가할 테이블의 수가 2개와  3개 이상으로 구분되는데 2개 이므로 넘어가고, 그 아래 테이블 선택에서 첫 번째 테이블을 표1로 되어 있으므로 마찬가지로 넘어가고, 두 번째 테이블로 표2를 선택하고 확인 버튼을 누릅니다.

 

그러면 일자와 지점명, 매출액을 중복되므로 아래에 추가되고, 매출이익과 매출순위는 표의 순서에 따라 오른쪽에 추가되는데, 있는 데이터만 표시되고, 없는 것은 null(값이 없음)으로 표시됩니다.

 

닫기 및 로드 아래 부분을 누르고, 닫기 및 다음으로 로드를 선택한 다음

표를 선택하고, 데이터가 들어갈 위치는 기존 워크시트, 셀 주소는 마우스로 K1셀을 클릭합니다. 그리고, 확인 버튼을 누르면

 

K1셀을 기점으로 추가된 데이터가 표 형식으로 출력됩니다. 파워 쿼리 편집기에서는 null로 보이던 것이 워크 시트에서는 빈 셀로 표시되는 차이점이 있습니다.

 

2. 완전 외부 조인

표 2개의 데이터를 모두 표시하는 것이므로 완전 외부 조인을 이용해서도 원하는 결과를 구할 수 있습니다. 다만, 일자가 아래에 붙는 것이 아니라 오른쪽에 배치되므로 이에 대한 처리가 필요합니다.

 

표1을 더블 클릭해서 파워 쿼리 편집기로 들어간 다음

이번에는 홈 탭 > 결합 그룹의 쿼리 병합 옆의 콤보상자를 누른 다음 '쿼리를 새 항목으로 병합'을 클릭합니다.

병합 창이 열리면 위에서는 표1 쿼리, 아래는 표2 쿼리를 선택하고, 표1과 표2에서 공통되는 부분인 일자와 지점명을 순서대로 클릭합니다. 일자와 지점명 오른쪽을 보면 1과 2가 쓰여 있습니다.

그리고, 조인 종류로는 완전 외부 조인을 선택하고 확인 버튼을 누르

 

그러면 병합1이라는 이름으로 쿼리가 추가되고, 오른쪽에 표가 표시되는데, 표2는 테이블 형식으로 되어 있으므로, 

일자 표시가 날짜와 시간 표시로 표시되는데, 일자 왼쪽 날짜시간 아이콘을 누른 후 날짜로 변경하면 됩니다.

 

표2 오른쪽 끝의 확장 아이콘을 누른 후 모든 열을 선택하고, '열 이름을 접두사로 사용'의 체크를 지웁니다. 그리고, 확인 버튼을 누르

 

그러면 일자가 한 줄씩 표시되기는 하는데, 한 열에 표시되지 않는 불편함이 있습니다.

 

따라서, 조건 열을 추가해서 일자가 비어 있을(null) 때는 일자.1로 하고, 아니면 일자로,

지점명이 null일 때는 지점명.1, 아니면 지점명으로,

매출액도 마찬가지로 null일 때는 매출액.1, 아니면 매출액으로 표시하도록 해서 열을 합쳐야 합니다.

 

추가 탭에서 조건 열 명령을 클릭합니다.

새 열 이름이 사용자 지정으로 되어 있는데, 일자결합으로 수정하고,

열 이름으로는 일자를 선택하고, 값에는 null을 입력하고, 출력에서는 ABC123으로 되어 있어 열 이름 선택이 안되므로, ABC123을 누른 후 값이 아닌 열 선택을 선택하고, 콤보 상자를 누른 후 일자. 1을 선택하고, 기타도 출력과 마찬가지로 열 선택으로 바꾼 다음 일자를 선택하고 확인 버튼을 누릅니다.

 

그러면 일자가 위, 아래로 연결돼서 표시되는데, 표2의 날짜가 표1의 날짜보다 먼저 표시됩니다. 그런데, 파워 쿼리에는 정렬 기능이 없으므로 워크시트에서 정렬하면 됩니다.

 

마찬가지로 지점명과 매출액도 조건 열 추가를 이용해 데이터를 합칩니다.

그러면 매출순위 오른쪽에 일자결합, 지점명결합, 매출액결합이 추가됩니다.

기존 일자, 일자.1, 지점명, 지점명.1, 매출액, 매출액.1은 필요 없으므로 Ctrl을 누른 상태에서 해당 열을 클릭해서 선택하고, 마우스 오른쪽 버튼을 누른 후 열 제거 메뉴를 누릅니다.

 

이제 열 이름을 더블 클릭해서 일자결합을 일자, 지점명결합을 지점명, 매출액결합을 매출액으로 수정하고, 열 이름 부분을 마우스로 끌어서 순서를 일자, 지점명, 매출액, 매출이익, 매출순위로 변경합니다.

 

 

일자 왼쪽 ABC123을 클릭한 후 날짜를 선택해서 날짜 시간 형식을 날짜형식으로 변경합니다.

 

이번에는 홈 탭에서 닫기 및 로드를 클릭해서 새 워크 시트에 표를 추가합니다.

병합1이란 시트 이름으로 표가 만들어졌습니다.

 

날짜 순으로 정렬하기 위해 일자 오른쪽 필터 아이콘을 클릭한 후 날짜/시간 오름차순 정렬을 클릭합니다.

 

그러면 날짜 순으로 표시돼서 쿼리 추가로 작성한 것과 결과가 동일한데 절차가 좀 복잡합니다.

 

파워쿼리 추가 열 이름이 다를 때(완성).xlsx

0.03MB