[파워쿼리기능소개] 2-1편 데이터 가져오기

파워쿼리를 사용하기 위해서 가장 먼저 해야 할 일은 데이터를 파워쿼리로 가져오는 것이죠. 방법은 크게 나누어 보면 파일, 데이터베이스, 엑셀 시트, 웹 정도로 볼 수 있겠네요.

Office 365 desktop 앱의 데이터 가져오기 리본메뉴

'데이터>데이터 가져오기 및 변환' 메뉴에 여러 출처를 통한 데이터 가져오기 기능을 사용할 수 있습니다. 

가장 많이 쓰는 방식. 몇가지를 소개합니다. 회사의 일반 사용자 입장에서는 데이터 베이스에 직접 연결하는 것 보다는 엑셀 파일로 내려받은 데이터를 많이 사용 하실 것 같네요. 

데이터베이스 연결도 접속 권한만 있다면 크게 어렵지 않습니다. 다만 지금 제가 직접 접속할 수 있는 환경이 없다보니 보여드릴 수 가 없네요. 특히 MS SQL은 쉽게 접속이 가능합니다.

이번 포스팅에서는 '텍스트/CSV 파일', '엑셀 통합문서', '테이블/범위', '폴더' 에서 가져오기를 다룹니다. 

MS 사이트에서 가져오기 관련 설명 페이지를 링크합니다. 링크

외부 데이터 원본에서 데이터 가져오기(파워 쿼리)

 

1. 텍스트/CSV 파일에서 가져오기 

먼저 연습 할 CSV 파일을 메모장으로 열어봅니다. 

사실 CSV 파일은 굳이 메모장으로 열어볼 필요가 없죠. 엑셀에서도 잘 열리니까요. CSV는 필드를 쉼표(,)로 구분한 텍스트 데이터입니다. 필드가 구분되어 있으므로 엑셀에서 열어도 각 필드별로 깔끔하게 열 수 있습니다. 일반 텍스트와는 다르죠. 

파워 쿼리로 데이터 가져오기를 합니다. '텍스트/CSV' 를 클릭하고 파일을 선택하고 가져오기를 하면 다음과 같이 가져온 데이터를 표시하고 쿼리를 편집 또는 바로 로드 할 수 있게 됩니다. 

가져온 데이터 미리보기 

① 로드 : 데이터를 새시트에 로드 할 수 있습니다. 표형식으로 데이터를 구성 합니다. '다음에 로드' 를 눌러 데이터를 표시할 방법과 위치 그리도 데이터 모델에 바로 추가 할 수 있습니다. 

② 데이터 변환 : 쿼리를 편집 할 수 있는 파워쿼리 편집기 창에서 변환 작업을 수행 할 수 있습니다. 

텍스트 파일도 동일한 방법으로 진행됩니다. 다만 CSV 와 다르게 구분자가 정의되어 있지 않다면 한개의 필드에 모든 값이 표시됩니다. CSV파일을 가져오게 되면 2가지 작업이 자동으로 수행됩니다. 하나는 가장 첫번재 행을 필드명(헤더로 승격)으로 만들어주는것이고 다른 하나는 날짜, 숫자, 문자 등의 데이터 형식을 구분해서 적합한 데이터 형태로 변경해줍니다. 

2. 엑셀 통합문서 가져오기  

엑셀 통합문서에서 데이터를 가져오는 방법도 파일에서 추출하는 것이므로 '텍스트/CSV' 와 동일합니다. '데이터가져오기>파일>엑셀통합문서' 를 클릭하여 윈도우 탐색기에서 파일을 선택하면 됩니다. 

'텍스트/CSV' 때와는 다르게 좌측에 파일과 시트명이 표시됩니다. 만약 시트가 여러개인 경우에는 모든 시트가 표시되며 해당 시트를 클릭하면 미리보기를 할 수 있습니다. 

가져올 시트를 클릭하고 로드와 데이터 변환을 클릭하면 '텍스트/CSV' 가져오기와 동일하게 처리됩니다. 하지만 엑셀 통합문서의 경우에는 여러 시트를 동시에 하나의 쿼리로 만들어 가져올 수 있습니다. 

예를 들어 다음과 같이 서울, 인천 등 지역별로 시트가 나누어 구성된 경우 하나의 쿼리로 합쳐서 가져올 수 있습니다.

여기서 먼저 '여러 항목 선택'을 클릭한뒤 가져올 시트를 클릭하게 되면 다음 과 같이 각각의 시트를 별도의 쿼리로 가져오게 됩니다.

때문에 전체 시트를 하나의 쿼리로 가져오려면 파일 이름(화살표)선택한 후 '데이터 변환' 을 클릭합니다. 

그런데 전체 시트를 다 가져와 보니 데이터가 표시되는 모양이 이상합니다. 'Data' 필드에 값 대신 Table 이라고 하이퍼링크가 달린 값이 들어와 있는데 이 셀을 클릭하면 위의 그림처럼 해당 데이터 내용을 하단에 보여줍니다. 이제 데이터를 확장하는 약간의 작업이 필요합니다. 

'Data' 헤더명 옆에 양쪽으로 꺽인 화살표 모양의 버튼을 클릭하면 

확장과 관련된 작업창이 뜹니다. 여기서 '확장' 선택된 상태로 모든 항목을 선택하고 확인을 클릭합니다. 그럼 각 시트의 내용이 확장되어 표 형태로 출력됩니다. 

이제 헤더승격을 시켜서 헤더(필드명)을 일괄 변경해 줍니다. 그전에 필요 없는 각 시트의 정보가 들어간 필드(name,item,kind,hidden) 를 제거 합니다. 그리고 각 시트의 헤더가 중복되어 있었을 것 이므로 중복된 행의 제거를 처리합니다. 

열제거는 해당 열을 선택한 뒤 열제거 버튼으로 처리하면 되고 중복된 각 필드명의 제거는 행제거의 중복된 항목 제거로 처리 합니다.

주의 ) '중복된 항목 제거' 시에는 반드시 전체 필드를 선택한 상태로 수행해 주세요. 특정 필드가 선택되면 그 필드 기준으로 중복 값을 제거하게 됩니다. 

이제 '첫 행을 머리글로 사용' 을 클릭하여 헤더로 승격 해줍니다. 이제 두개의 시트로 나누어진 파일을 하나의 쿼리로 가져오기가 완료되었습니다. '닫기 및 로드' 를 클릭하여 새시트에 결과를 로드 해봅니다. 

완성되었네요. 

활용) 여기서 여러 시트를 한개의 쿼리로 가져오는 방법을 실제 반복 업무에 대응 해볼까요. 이런류의 작업은 매우 빈번합니다.

• 같은 포멧의 자료를 팀별로 구성된 각 시트로 받아서 하나의 시트로 합치고 요약 

• 월별로 나누어진 실적 시트를 하나의 시트로 합쳐서 요약 

이것 외에도 각각 나누어진 자료를 합치는 작업은 셀 수 없이 많을 것입니다. 파워쿼리는 원본 파일만 변경해주면 수행된 작업은 다시 할 필요가 없죠. 허나 이 작업을 손으로 한다면 계속 파일을 열고 시트를 붙이고 더하는 일을 해야 합니다. 거기에 이렇게 파워쿼리로 구성된 데이터는 다음 작업인 변환 및 요약의 작업을 파워쿼리 또는 파워피벗 까지 한번에 처리 할 수 있습니다. 어떻게 구성하느냐에 따라서 거의 완벽한 자동화까지 가능한 셈이죠. 

한번 만들어 놓으면 원본의 파일을 변경해 주는 것 만으로도 작업을 마칠 수 있습니다. 

이와 유사한 개념으로 다음 번 가져오기 2-2편 포스팅의 '폴더'의 데이터 가져오기를 활용하면 시트가 아닌 파일 단위로 동일한 작업이 수행되도록 하는 예시를 들어보겠습니다. 이 기능이 더 유용할 것 같네요. 

예제 파일 첨부합니다. 오늘 예제파일은 공공데이터 포털에서 받았습니다. 파워쿼리기능소개2-1 파일이 작업된 문서이고 나머지는 데이터 파일입니다.

국가보훈처_보훈의료 위탁병원 코로나19 병원정보 현황(국민안심병원, 감염병전담병원)_20200427.csv

0.03MB

선별진료소목록.xlsx

0.05MB

선별진료소목록_여러시트.xlsx

0.02MB

파워쿼리기능소개_2-1.xlsx

0.06MB

 

 

* 계획은 1편으로 가져오기를 끝내야 하는데 길어졌습니다. /(ㄒoㄒ)/~~ 어쩔 수 없이 2편이 2-1과 2-2 로 나누어 집니다. 첫 포스팅 부터 이러니 앞날이 걱정입니다. 

**앗 오피스365에는 DB가 MS-SQL 만 가능하네요. 메뉴가 없어졌네요. 저는 오피스 2019 사용하다가 최근에 오피스365로 넘어왔는데 2019에서는 오라클 디비가 가능했었는데. 아쉽네요.😢