회사 업무를 하다 보면 다른 시트나 파일의 데이터를 하나로 합치는 경우가 생깁니다. 일회성으로 벌어지는 일이라면 하나의 시트에 복사/붙여넣기를 하여 이어 붙이는 것이 편할 수도 있습니다.
하지만, 주기적으로 데이터나 표를 합쳐야 한다면 파워쿼리의 '쿼리 추가' 기능을 활용하는 것이 훨씬 효율적입니다. 특히, 데이터를 합치기 전에 데이터 전처리 작업이 필요한 상황*이면 더욱 유용합니다.
이번 글에서는 데이터/표를 아래로 합치는 '쿼리 추가' 기능의 활용 방법에 대해서 소개해드리겠습니다.
* 만약에 데이터 열 이름과 서식이 동일한 데이터, 즉 데이터 내용만 다른 경우라면 '폴더에서 가져오기' 기능을 사용하는 것을 추천드립니다.
[파워쿼리- 쿼리 생성하는 5가지 방법] #4 폴더에서 가져오기 (여러 파일 취합하기)
<쿼리 생성하는 5가지 기초적인 방법> 1. 테이블/범위에서 가져오기 2. 엑셀 통합 문서(xlsx, xlsm, xlsb)에서 가져오기 3. 텍스트/CSV에서 가져오기 4. 폴더에서 가져오기 5. 빈쿼리 참고: 파워쿼리에
writingpjt.tistory.com
예시로 활용할 파일은 공공데이터포털에서 다운로드한 경기도 이천시와 강원도 속초시 전기차 충전소 현황 자료입니다.
경기도 이천시 전기차 충전소와 강원도 속초시 전기차 충전소 파일을 취합하는 상황을 가정해봅시다.
원본 데이터 분석하기
쿼리를 생성하기 앞서, 먼저 원본 데이터를 분석해 보겠습니다.
아래의 사진들을 보면, 경기도 인천시와 속초시 전기차 충전소 자료의 데이터 열의 수와 이름이 다르다는 것을 알 수 있습니다.
1) 취합 기준이 될 데이터 정하기
경기도 이천시 데이터가 데이터 열 수가 더 적으므로, 이천시 데이터를 기준으로 속초시 데이터를 추가해 보겠습니다.
2) 데이터 전처리 작업 계획하기: 데이터 차이점 분석
속초시 데이터를 이천시 데이터에 붙이기 위해서는 데이터 전처리 작업이 필요합니다. 왜냐하면, 속초시 데이터에는 이천시 데이터에는 없는 내용들도 있기 때문입니다. 데이터를 취합할 때 필요한 것은 공통적으로 갖고 있는 데이터입니다.
또한, 이천시의 '주소' 열과 속초시의 '소재지도로명주소' 열처럼 같은 내용의 데이터인데 데이터 열 이름이 다른 경우도 있습니다. 이러한 경우 데이터 전처리 과정 없이 데이터를 합칠 경우 '주소' 열과 '소재지도로명주소' 열 둘 다 생성됩니다. 즉, 데이터 취합에 의도와 다르게 열이 생긴다는 것이죠.
정리하자면, 파워쿼리 편집기를 통해 속초시 데이터에서 필요하지 않은 열은 삭제하고, 몇 개 열들은 이름을 수정할 것입니다.
파워쿼리 '쿼리 추가'기능으로 데이터 취합하기
1) '텍스트/CSV에서 가져오기'로 각 파일의 쿼리 생성하기
▲위의 사진처럼 각 원본 데이터 CSV 파일에서 쿼리를 생성합니다. CSV 파일에서 쿼리를 생성하는 방법은 아래의 글을 참고 부탁드립니다.
[파워쿼리- 쿼리 생성하는 5가지 방법] #3 텍스트/CSV에서 가져오기
<쿼리 생성하는 5가지 기초적인 방법> 1. 테이블/범위에서 가져오기 2. 엑셀 통합 문서(xlsx, xlsm, xlsb)에서 가져오기 3. 텍스트/CSV에서 가져오기 4. 폴더에서 가져오기 5. 빈쿼리 참고: 파워쿼리에
writingpjt.tistory.com
2) 데이터 전처리 작업하기
경기도 이천시 전기차 충전소 파일을 기준으로 데이터를 취합할 것이기 때문에, 속초시 데이터 파일이 이천시 파일과 동일한 데이터 열을 갖도록 수정해야 합니다.
▲우선 필요없는 열부터 제거합니다. 속초시와 이천시 파일 모두가 갖고 있는 데이터는 '충전소명(충전소명)', '충전기타입', '주소(소재지도로명주소)', '이용가능시간', '급속충전량(충전기용량)', '데이터기준일자'입니다.
따라서 위의 사진처럼 필요한 열들만 선택 후 마우스 우클릭 > '다른 열 제거'를 클릭합니다.
▲ '다른 열 제거' 결과, 이천시 전기차 충전소 데이터와 열 수와 데이터 내용이 똑같아졌습니다. 하지만, '충전소명'과 '소재지도로명주소', '충전기용량' 열 이름을 이천시 데이터와 동일하게끔 수정해야 합니다.
▲파워쿼리 편집기에서 각 열 이름을 더블 클릭 후, 충전소명 → 충전소, 소재지도로명주소→ 주소, 충전기용량 → 급속충전량으로 바꿔줍니다.
3) 이천시 데이터에 속초시 데이터 '쿼리 추가'하기
▲파워쿼리 편집기에서 취합 기준이 될 쿼리(이천시 데이터)를 선택 후, 홈 탭 > '쿼리 추가'를 클릭합니다. 추가할 테이블에 취합할 데이터 쿼리(속초시 데이터)를 선택 후 확인 버튼을 클릭합니다.
▲위의 사진처럼, 쿼리를 추가한 결과 이천시 데이터 아래에 속초시 데이터가 취합된 것을 알 수 있습니다.
이천시 전기차 충전소 데이터와 속초시 전기차 충전소 데이터를 취합하는 과정에서 알 수 있듯이, 사실 '쿼리 추가' 기능을 활용하는 방법은 굉장히 단순하고 쉽습니다. 오히려, 데이터 전처리 작업이 더 어렵습니다. 이처럼 데이터를 취합할 때 데이터 간 통일된 양식을 갖고 있지 않으면, 기준 데이터를 정하고 어떻게 데이터 열을 통일할지 고민해야 합니다.
향후 다른 글을 통해 '폴더에서 가져오기'와 '쿼리 추가하기' 기능의 차이점과 특성에 대해서 좀 더 자세하게 소개해 드리겠습니다.
<관련 글>
[파워쿼리- 쿼리 생성하는 5가지 방법] #1 테이블/범위에서 가져오기
[파워쿼리- 쿼리 생성하는 5가지 방법] #2 엑셀 통합 문서에서 가져오기
'엑셀, VBA, 파워쿼리 > 파워쿼리' 카테고리의 다른 글
| [파워쿼리] 데이터 행이 너무 많아 로드가 안될 때 해결방법: 연결만 만들기, 피벗테이블로 만들기 (2) | 2023.10.24 |
|---|---|
| [파워쿼리] 파워쿼리의 필터 기능(기본 사용법, And/Or 조건 추가) (5) | 2023.10.18 |
| [파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환) (1) | 2023.10.12 |
| [파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기 (2) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #5 빈 쿼리로 쿼리 생성하기 (3) | 2023.10.07 |
댓글