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

 

파워쿼리는 엑셀 파일, CSV 파일 등과 데이터를 연결하여 쿼리를 생성한 후, 분석에 용이하게 가공해주는 프로그램입니다. 따라서, 쿼리를 생성하는 것은 파워쿼리 사용의 첫 단계입니다. 파워쿼리는 MS Access 등과 같은 데이터베이스나 웹 데이터도 가져오는 등 다양한 기능을 제공합니다. 하지만 회사의 보안정책, 그리고 엑셀 2016의 파워쿼리 편집기에는 최신 버전에 비해 정말 기본적인 기능만 들어있다는 한계 등의 이유로 저도 파워쿼리를 100% 활용하고 있지는 못합니다.

 

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

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

 

그래서 저는 쿼리를 생성하는 가장 기본적인 방법 다섯 가지를 소개해드릴 예정입니다. 이번 글에서는 테이블/범위에서 데이터 가져오는 방법을 알아보겠습니다.

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

 

<예시 파일>

20220701~20220802 삼성SDI 현대로템.xlsx

0.01MB

 

 

 

 

테이블/ 범위에서 쿼리 생성하는 과정

1) 테이블(표)이나 범위 선택 후 > 데이터 탭 > 테이블/범위에서 클릭하기

▲쿼리를 생성하고자 하는 테이블(표)나 데이터 범위를 선택합니다. 그 후 데이터 탭 > '테이블 범위에서'를 클릭합니다. 만약에 선택한 부분이 테이블(표)면 바로 파워쿼리 편집기가 시작되면서 쿼리가 생성됩니다. 

하지만 위의 사진처럼 일반 범위인 경우 '표 만들기' 팝업창이 생성됩니다. 표로 변환할 범위를 지정하면, 범위가 표로 변환되면서 파워쿼리 편집기가 시작됩니다. 

 

2) 파워쿼리 편집기에서 생성된 쿼리 확인 및 편집하기

▲파워쿼리 편집기 좌측에는 현재 파일에 연결되어 있는 쿼리 목록이 있습니다. 위의 사진처럼 범위에서 쿼리를 생성할 시 범위가 테이블로 변환되면서 부여받는 테이블명이 쿼리의 이름이 됩니다. 그래서 쿼리 이름이 '표2'입니다.

그리고 우측에는 현재 선택된 쿼리에 적용된 단계들을 볼 수 있고, 각 단계를 클릭해서 출력 결과와 수식 창 내의 코딩을 확인할 수 있습니다. 또는, 고급 편집기를 클릭하여 쿼리의 전체적인 코딩을 확인 및 편집할 수 있습니다. 고급 편집기를 보는 방법과 활용 방법은 다른 글을 통해 소개해 드리겠습니다. 

▲쿼리를 편집하면 위의 사진처럼 적용된 단계가 추가됩니다. 위의 사진의 경우 일자를 우선적으로 오름차순 한 후, 종목도 오름차순 하도록 데이터를 정렬한 것입니다. 이를 나타낸 코딩이 'Table.Sort(#"변경된 유형",{{"일자", Order.Ascending}, {"종목", Order.Ascending}})' 입니다. 

3) 닫기 및 로드 ▼ > 닫기 및 다음으로 로드

▲닫기 및 로드 버튼의 윗부분을 클릭하면 디폴트 옵션으로 쿼리가 새 워크시트에 표로 출력됩니다. 하지만 또 다른 쿼리를 편집하기 위해 쿼리를 만드는 경우가 많기 때문에, 이런 경우는 연결만 만들어 놓는 것이 시간과 용량에 유리합니다. 따라서 저는 닫기 및 로드 버튼 아랫부분의 화살표를 클릭하여, '닫기 및 다음으로 로드' 버튼을 활용하시는 걸 추천드립니다. 참고로 회사에서 활용하는 엑셀 2016 버전에서는 '데이터 가져오기' 옵션에 '데이터가 들어갈 위치'와 '연결만 만들기'만 있습니다. 

 

쿼리 생성 시 팁 #1. 되도록이면 데이터를 표(테이블)로 설정 

쿼리가 생성될 때 표(테이블) 이름을 쿼리 이름으로 설정하기 때문입니다. 미리 데이터를 표로 설정하고, 테이블 이름을 원하는 이름으로 바꿔 놓으면 파워쿼리 편집기에서 쿼리 이름을 바꿀 필요가 없습니다.

그리고 파워쿼리를 사용하면 사용할수록, 데이터를 미리 표로 바꿔놓는 게 정말 편리하다는 것을 알게 될 것입니다.

표(테이블)에 대한 더 자세한 내용은 아래의 글을 참고해주세요.

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

 

쿼리 생성 시 팁 #2. 쿼리 단계 중 '변경된 유형'은 삭제하는 것을 추천

▲'변경된 유형'은 쿼리가 생성될 때 자동적으로 적용되는 단계입니다. 이 단계에서는 파워쿼리 편집기가 알아서 각 열의 데이터에 데이터 유형을 부여하는 것입니다. 위의 사진에서는 '일자' 열이 정수 데이터 열이라고 파워쿼리 편집기가 유형을 정해버렸습니다.

 

하지만 각 열의 데이터가 어떤 유형인지 정해져 있지 않아도 쿼리 편집에 큰 문제는 없습니다. 오히려 파워쿼리 편집기가 데이터 유형을 정해버려서 문제가 되는 경우가 더 많이 생깁니다. 예를 들어, 일자를 '정수'로 인식해서 나중에 yyyy-mm-dd 형태로 바꿀 때 코딩이 더 복잡해지는 경우가 있습니다. 

 

관련 글

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

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

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