[파워쿼리- 쿼리 생성하는 5가지 방법] #4 폴더에서 가져오기 (여러 파일 취합하기)

<쿼리 생성하는 5가지 기초적인 방법>
1. 테이블/범위에서 가져오기
2. 엑셀 통합 문서(xlsx, xlsm, xlsb)에서 가져오기
3. 텍스트/CSV에서 가져오기
4. 폴더에서 가져오기
5. 빈쿼리  

참고: 파워쿼리에 대한 간단한 소개글

[엑셀] 엑셀 주요기능 세 가지: 스프레드시트, 매크로/VBA, 파워쿼리

이번 글에서는 '폴더에서 가져오기' 기능을 소개드리겠습니다.

'폴더에서 가져오기' 기능은 동일한 양식의 여러 파일을 한 번에 취합할 때 유용합니다.

또한, 폴더에 파일 개수가 변경되거나 원본 데이터가 변경되어도, 새로고침만 누르면 데이터가 업데이트됩니다. 따라서 특정 데이터를 지속 관리할 때도 굉장히 편리한 기능입니다.

 

예시로 쓰인 파일 출처는 공공데이터포털입니다. 아래의 사이트에는 CSV 파일로 업로드되어 있지만, 데이터를 갖고 오는 코딩이 엑셀 파일이 더 편리하여 xlsx 파일로 변환해서 업로드했습니다. 다른 글에서 여러 CSV 파일을 xlsx로 변환하는 매크로를 소개해 드리겠습니다. 

 

https://www.data.go.kr/data/15017319/standard.do

전국자전거대여소표준데이터

 

서울특별시_광진구_자전거대여소_20220705.xlsx

0.01MB

서울특별시_노원구_자전거대여소_20220817.xlsx

0.01MB

서울특별시_서초구_자전거대여소_20220810.xlsx

0.01MB

---

폴더에서 여러 .xlsx 파일 가져오기

1) 데이터 탭 > 데이터 가져오기 > 파일에서 >폴더에서

▲데이터 탭 > 파일에서> 폴더에서를 클릭합니다. 이후 '찾아보기' 팝업창에서 파일들을 가져올 폴더를 선택합니다.

 

▲가져올 폴더를 선택하면 위의 사진과 같이 쿼리가 생성됩니다. 저의 경우 CSV 파일과 xlsx 파일이 섞여있는데요. Extension 열에서 xlsx 파일만 갖고 오도록 필터를 걸어보도록 하겠습니다. 

 

2) Content 열 외 다른 열 제거하기 (혹은 Name과 Content 외 다른 열 제거)

▲Content열의 Binary는 파워쿼리가 파일에서 데이터를 가져올 때 필요한 정보라고 이해하시면 됩니다. 여러 파일을 취합할 때 각 파일의 이름이 필요해 Name열도 남길 때도 있지만, 이번 글에서는 Content열만 사용해 보겠습니다. 

 

3) 열 추가 >  사용자 지정 열 > '= Excel.Workbook([Content])' 구문 입력

▲파워쿼리 편집기 탭에서 열 추가 >  사용자 지정 열을 클릭합니다. 팝업 창에서 '= Excel.Workbook([Content])' 구문 입력합니다. 여기서 [Content]는 팝업 우측 '사용 가능한 열'의 Content를 의미하며, 하단의 삽입 버튼을 클릭 시 수식에 '[Content]'가 추가됩니다. 확인을 누를 시 아래의 사진처럼 '사용자 지정' 열이 생성됩니다.

 

 

▲주의할 점은, 엑셀이나 VBA와 달리 파워쿼리 수식은 대소문자 하나하나 정확하게 입력해야 합니다. 

 

4) '사용자 지정' 열 확장 및 '사용자 지정.Data' 열 (Table 열) 외 다른 열 제거

'사용자 지정' 열 오른쪽의 단추를 클릭 후 확인을 누르면, 위의 사진처럼 파일 이름, 데이터 테이블, 데이터 개체 종류 등의 열이 추가로 생깁니다. 여기서 각 파일의 데이터가 들어있는 곳은 '사용자 지정.Data'의 'Table' 입니다.

따라서 위의 사진처럼 '사용자 지정.Data' 열 외의 다른 열은 제거해줍니다. 

 

5) '사용자 지정.Data' 열 (Table 열) 확장 

▲'사용자 지정.Data' 열(Table이 있는 열) 오른쪽의 단추를 클릭 후 확인을 누릅니다. 그러면 위의 사진처럼 폴더 안의 파일들이 취합되어 로드됩니다. 

하지만, 보시다시피 열 이름이 Column1, Column2 등으로 되어 있으며, 정작 진짜 데이터 열의 이름인 '자전거대여소명' 등은 첫 행을 비롯해 데이터 부분에 파일 개수만큼 존재해 있습니다(*). 따라서, 데이터를 클렌징할 필요가 있습니다.

(*) 각 엑셀 파일이 표(테이블)로 되어 있으면 이 문제가 해결됩니다. 이에 대해서는 추후 다른 글에서 소개해 드리겠습니다.

 

6) 첫 행 머리글 설정 및 필터 기능으로 데이터 클렌징하기

▲변환 > 첫 행을 머리글로 사용을 클릭하면, 첫 행에 있던 '자전거대여소명', '자전거대여소구분' 등 데이터 열 이름으로 쓰일 행이 머리글로 변환됩니다. 하지만 데이터 부분에 여전히 '자전거대여소명', '자전거대여소구분' 등이 들어있는 행들이 있습니다. 이럴 때 필터 기능을 이용해서 해당 행들을 제거할 수 있습니다. 

▲데이터 열을 하나 골라서, 데이터 목록 중에서 해당 데이터 열 이름과 동일한 것만 제외시켜 필터링 걸면 됩니다. 예를 들면, 자전거대여소명에서 데이터 값이 '자전거대여소명'인 행만 제외하면 됩니다. 그러면 위의 사진처럼 온전히 데이터 부분만 제대로 남게 됩니다. 

 

7) 결과값 로드 및 업데이트해 보기

▲닫기 및 로드를 하면 폴더 안의 파일들이 취합되어 데이터가 엑셀 시트에 로드됩니다.

 

서초구 자전거대여소 파일을 복사하여 파일 수를 늘려보고, 노원구 자전거대여소 파일 경우 자전거대여소 명을 'TEST'로 바꾼 후 데이터를 새로고침 해보겠습니다. 

 

▲'모두 새로고침'을 클릭하면 별도의 쿼리 수정 없이도 데이터가 업데이트됩니다. 

 

 

폴더에서 여러 CSV 파일 가져올 때의 구문 차이점

1) 열 추가 >  사용자 지정 열 > '= Csv.Document([Content],[Delimiter=",", Columns=17, Encoding=949, QuoteStyle=QuoteStyle.None])' 구문 입력

▲엑셀 파일을 열 때 사용하는 Excel.Workbook([Content])보다 구문이 더 복잡합니다. 앞서 말씀드렸다시피, 대소문자 모두 정확해야 하기 때문에 저는 보통 스티키 메모 같은 어플에 Csv.Document([Content],[Delimiter=",", Columns=17, Encoding=949, QuoteStyle=QuoteStyle.None]) 구문을 복사해 두는 편입니다.

 

2) 생성된 '사용자 지정 열'은 Table 열 → 확대하면 데이터가 로드됨

▲Excel.Workbook([Content])구문과는 달리, 사용자 지정 열 단계에서 바로 Table 열이 생성됩니다. 사용자 지정 열의 셀을 클릭해 보면, 위의 사진처럼 각 테이블의 데이터를 미리 볼 수 있습니다.

사용자 지정 열에 이미 데이터가 들어있기 때문에 열을 확장한 후, 데이터를 클렌징하면 취합이 완료됩니다. 

 

쿼리생성 팁: 원본 데이터를 표(테이블)로 먼저 변환하는 것을 추천

쿼리 원본 데이터를 폴더에서 가져올 때 엑셀 파일의 데이터가 범위이거나 원본 파일이 csv인 경우, 데이터 항목명이 데이터 행에 포함되어 있어서 데이터 클렌징 작업이 필요합니다. 그러나 원본 데이터들이 모두 표(테이블)로 설정되어 있으면, 데이터를 합칠 때도 데이터 항목명이 반복되지 않아 별도의 데이터 클렌징 작업이 필요하지 않습니다.

하지만 폴더 안에 파일이 많은 경우 일일이 데이터를 표(테이블)로 바꾸기 번거로울 것입니다. 이 문제는 매크로를 통해 해결할 수 있는데요. 향후 여러 csv 파일, xls 파일 등을 파워쿼리 원본 데이터로 쓰기 편하게 한 번에 변환해 주는 매크로를 소개해 드리도록 하겠습니다.  

 

관련 글

[파워쿼리- 쿼리 생성하는 5가지 방법] #2 엑셀 통합 문서에서 가져오기

[파워쿼리- 쿼리 생성하는 5가지 방법] #1 테이블/범위에서 가져오기

[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)

[파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기

[엑셀 기능] 표(테이블)과 피벗테이블