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

이번 포스팅은 지난 2-1편 데이터 가져오기에 이어서 테이블/범위에서 가져오기와 폴더에서 가져오기를 진행합니다.

데이터 가져오기 2-1 편 포스팅 

2020/06/26 - [엑셀/파워쿼리기능소개] - [파워쿼리기능소개] 2-1편 데이터 가져오기

1. 텍스트/CSV 파일
2. 엑셀 통합문서

 

1. 테이블/범위 에서 가져오기 

말 그대로 현재 엑셀 파일에 있는 테이블(표) 나 범위 데이터를 원본으로 쿼리를 구성하는 것을 의미 합니다. 제가 기존의 포스팅에서 기능의 예시를 들때 사용하는 가장 일반적인 방법이죠. 실제 업무에서 사용은 주로 분석용 데이터 구성을 위한 전처리 작업이 될 것 같네요. 저는 다양한 출처의 데이터를 수집하는 일 보다는 이미 가지고 있는 데이터의 변경 또는 파라메터 쿼리로 구성하는 목적으로 사용하고 있습니다. 

방법은 간단합니다. 원하는 테이블이나 범위를 지정하고 '테이블/범위에서'를 클릭하면 됩니다.

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

예시를 위해 빈 엑셀시트에 네이버증권 웹 페이지의 표를 복사해서 붙여넣기 했습니다. 

노란색 박스를 긁어서 붙여넣기

쿼리할 범위를 지정하고 '테이블/범위에서'를 클릭하면 바로 표로 전환하는 창이 떠서 전환을 시킨 후에 바로 쿼리로 구성합니다. 가져오기는 이것으로 끝입니다. 외부 데이터를 사용가능 할 경우 엑셀에 굳이 붙여넣고 다시 그내용을 쿼리로 만들 필요가 없으므로 사용 빈도가 적을 수 있습니다. 

하지만 저는 데이터 모델링을 하는 것이 업무의 많은 부분을 차지하기 때문에 데이터 구조를 직접 엑셀에 만들어가면서 하나하나 쿼리로 구성하여 작업을 하는 편입니다. 저에게는 매우 중요한 기능입니다. 

그외에도 시뮬레이션 항목 값을 적용할때나 데이터 필터링 조건으로 사용하는 경우 이 기능은 필수입니다. 아마 파워 피벗의 포스팅이나 파워쿼리소개 편의 마지막인 쿼리 병합에서 이 내용을 다룰 수 있을 것 같네요. 그때 자세하게 다루어 보고 일단 소개할 마지막 방법으로 넘어가겠습니다. 

2. 폴더 에서 가져오기 

지난 2-1 편에서 엑셀 파일에서 가져올때 여러 시트를 합쳐서 하나의 쿼리로 구성하는 것을 소개 했습니다. 이번에는 같은 폴더 내에 있는 여러 파일을 쿼리로 가져오는 기능입니다.

'Data' 폴더에 원본1,2,3 의 동일한 형식을 가진 엑셀 파일이 3개 있습니다. 이것을 폴더 가져오기로 쿼리를 구성합니다. 

폴더에서 가져오기를 클릭하고 

폴더 경로를 지정하고 확인을 누릅니다. 

이제 가져오기 미리보기가 생성되었습니다. 이전까지와 다른 점은 '결합' 이라는 단추가 추가되었네요. 일단 데이터 변환을 눌러 쿼리 편집기로 가봅니다. 

쿼리 편집창에 로드된 모습을 보면 일단 하나의 쿼리(Data)로 구성이 되었고 폴더내에 각 파일에 대한 정보가 쿼리 내용으로 구성되어 있음을 확인 할 수 있습니다. 각 파일의 내용은 맨 앞에 있는 Content 필드의 'Binary' 를 클릭하고 다시 Data 필드의 'Table'을 클릭하면 확인 할 수 있습니다. 물론 각각 눌러서 하나씩 로드 할 수도 있습니다만 한번에 처리 할 수 있다면 더 좋겠죠. 

이제 Data를 꺼냅니다. Content 필드 전체를 선택하고 파워쿼리 편집기의 리본메뉴의 '홈>결합>파일병합' 을 클릭합니다. 그럼 파일 병합창이 열리면서 샘플파일에 대한 미리보기가 수행됩니다. 

확인을 눌러 넘어갑니다. 

완성이네요. 분명 우리는 아무런 작업을 하지 않았는데 알아서 작업을 해놓았습니다.

간단하게 작업내용을 볼까요. 먼저 좌측에 쿼리가 여러개 생겼습니다. 분명 Data 쿼리 하나였는데 파일 병합을 수행하니 매개변수,리스트,함수 까지 도우미쿼리가 자동생성되었음을 확인 할 수 있습니다. 그리고 결과 쿼리인 Data 쿼리에는 적용된 단계가 6 단계 추가되었습니다. 

저도 더 연구가 필요한 부분이라 쉽게 설명하기 어렵지만 수행된 코드를 살펴 보면 Content에 들어있는 데이터를 탐색하는 과정을 사용자함수로 만들고 거기에 파일이름을 매개변수로 넣어서 파일을 탐색하여 구성한 것으로 이해 됩니다. 

함수 만들기를 직접 해보면 조금 이해가 될 것 같네요. ㅜㅜ; 

참고로 고급편집기로 살펴보면 M코드는 아래와 같습니다. 

 

let 
원본 = Folder.Files("E:\50_Study\Tera incognito\Power query posting\2-2 가져오기\Data"), 
#"필터링된 숨겨진 파일1" = Table.SelectRows(원본, each [Attributes]?[Hidden]? <> true), 
#"사용자 지정 함수 호출1" = Table.AddColumn(#"필터링된 숨겨진 파일1", "파일 변환", each #"파일 변환"([Content])), 
#"이름을 바꾼 열 수1" = Table.RenameColumns(#"사용자 지정 함수 호출1", {"Name", "Source.Name"}), 
#"제거된 다른 열 수1" = Table.SelectColumns(#"이름을 바꾼 열 수1", {"Source.Name", "파일 변환"}), 
#"확장된 테이블 열1" = Table.ExpandTableColumn(#"제거된 다른 열 수1", "파일 변환", Table.ColumnNames(#"파일 변환"(#"샘플 파일"))), 
#"변경된 유형" = Table.TransformColumnTypes(#"확장된 테이블 열1",{{"Source.Name", type text}, {"종목명", type text}, {"현재가", Int64.Type}, {"전일비", type any}, {"등락률", type number}, {"매수호가", Int64.Type}, {"매도호가", Int64.Type}, {"거래량", Int64.Type}, {"거래대금", Int64.Type}, {"전일거래량", Int64.Type}, {"토론실", type text}}) 

in 
#"변경된 유형"

예제는 완벽하게 일치된 형태를 가진 파일을 묶어서 처리 했기 때문에 별 문제없이 쉽게 처리가 되었지만 실제 업무에서 활용하려면 아무래도 각 파일에 대한 신뢰가 필요합니다. 

시스템에서 내려받은 자료가 아닌 사람이 작성한 자료를 이와 같은 방식으로 취합하려면 먼저 취합에 적합한 양식이어야 하며 그리고 그 양식에 어긋나는 수정을 할 수 없도록 유효성 검사나 시트보호 등 엑셀 문서 기능을 활용해서 사전에 문제를 차단하는 것이 좋겠습니다. 

 

※ 생성된 쿼리 및 연결 목록 

이렇게 가져오기로 생성된 쿼리는 로드를 통해서 엑셀 시트에 표나 피벗 테이블 형태로 출력할 수 있고 데이터의 출력없이 연결만 구성할 수 도 있습니다. 그리고 생성된 각 쿼리는 데이터>쿼리 및 연결 보기를 클릭하여 확인할 수 있습니다. 다시 쿼리 편집창으로 이동하거나 쿼리를 삭제,변경하기 위해서는 이 작업창을 이용하면 됩니다. 

쿼리 및 연결 관리 창

파워쿼리기능소개_2-2가져오기.xlsx

0.03MB

원본1.xlsx

0.01MB

원본2.xlsx

0.01MB

원본3.xlsx

0.01MB

끝으로 MS 사이트에 작성되어 있는 외부 데이터 연결에 대한 텍스트를 추가합니다. 관심 있으신 분을 한번 읽어보시면 좋을 것 같네요. 

외부 데이터 연결 작업

다음 섹션에서는 외부 데이터 연결이 작동 하는 방법과 다른 프로그램 및 사용자와의 연결 정보 편집, 관리 및 공유 방법에 대해 설명 합니다.

데이터 연결의 기본 사항 이해

Excel 통합 문서의 데이터는 서로 다른 두 위치에서 만들 수 있습니다. 데이터는 통합 문서에 직접 저장 되거나, 텍스트 파일, 데이터베이스 또는 OLAP (온라인 분석 처리) 큐브와 같은 외부 데이터 원본에 저장 될 수 있습니다. 이 외부 데이터 원본은 외부 데이터 원본을 찾고, 로그인 하 고, 액세스 하는 방법을 설명 하는 정보 집합인 데이터 연결을 통해 통합 문서에 연결 됩니다.

외부 데이터에 대 한 연결의 주요 이점은 시간을 많이 소모 하 고 오류를 발생 시킬 수 있는 작업 인 데이터를 반복적으로 통합 문서에 복사 하지 않고 데이터를 주기적으로 분석할 수 있다는 것입니다. 외부 데이터에 연결한 후에 데이터 원본이 새 정보로 업데이트 될 때마다 원본 데이터 원본에서 자동으로 Excel 통합 문서를 새로 고치거 나 업데이트할 수도 있습니다.

연결 정보는 통합 문서에 저장 되며, ODC (Office Data Connection) 파일 (.odc) 또는 데이터 원본 이름 파일 (dsn) 등의 연결 파일에 저장할 수도 있습니다.

외부 데이터 를 Excel로 가져오려면 데이터에 대 한 액세스 권한이 있어야 합니다. 액세스 하려는 외부 데이터 원본 로컬 컴퓨터에 없는 경우에는 데이터베이스 관리자에 게 암호, 사용자 권한 또는 기타 연결 정보를 문의 해야 할 수 있습니다. 데이터 원본이 데이터베이스인 경우 데이터베이스가 단독 모드에서 열리지 않도록 합니다. 데이터 원본이 텍스트 파일 이거나 스프레드시트 인 경우에는 다른 사용자가 단독으로 액세스할 수 있도록 열려 있지 않도록 해야 합니다.

Excel, 연결 파일 및 데이터 원본 간에 데이터 흐름을 조정 하는 데 ODBC 드라이버나 OLE DB 공급자가 필요한 데이터 원본에도 많습니다.

다음 다이어그램에는 데이터 연결에 대 한 주요 사항이 요약 되어 있습니다.

1. Analysis Services, SQL Server, Microsoft Access, 다른 OLAP 및 관계형 데이터베이스, 스프레드시트 및 텍스트 파일에 연결할 수 있는 다양 한 데이터 원본이 있습니다.

2. 많은 데이터 원본에는 연결 된 ODBC 드라이버 또는 OLE DB 공급자가 있습니다.

3. 연결 파일은 데이터 원본에서 데이터에 액세스 하 고 검색 하는 데 필요한 모든 정보를 정의 합니다.

3. 연결 정보는 연결 파일에서 통합 문서로 복사 되며 연결 정보는 쉽게 편집할 수 있습니다.

5. 통합 문서에 직접 저장 된 데이터를 사용 하는 것 처럼 사용할 수 있도록 데이터를 통합 문서에 복사 합니다.

연결 찾기

연결 파일을 찾으려면 기존 연결 대화 상자를 사용 합니다. ( 데이터 탭의 외부 데이터 가져오기 그룹에서 기존 연결을 클릭 합니다.) 이 대화 상자를 사용 하 여 다음과 같은 유형의 연결을 볼 수 있습니다.

• 통합 문서의 연결    

  이 목록에는 통합 문서의 현재 연결이 모두 표시 됩니다. 목록은 이미 정의한 연결 로부터 만들거나, 데이터 연결 마법사의 데이터 원본 선택 대화 상자를 사용 하 여 만들었거나, 이전에이 대화 상자에서 연결로 선택한 연결에서 만들어집니다.

• 컴퓨터의 연결 파일    

  이 목록은 일반적으로 내 문서 (windows XP) 또는 문서 (windows Vista) 폴더에 저장 된 my Data Sources 폴더에서 만들어집니다.

• 네트워크의 연결 파일    

  이 목록은 다음에서 만들 수 있습니다.

  • 로컬 네트워크에 있는 폴더 집합으로, Microsoft Office 그룹 정책 배포의 일부로 네트워크를 통해 위치를 배치할 수 있습니다.

  • SharePoint Foundation 사이트의 DCL ( Excel Services 데이터 연결 라이브러리) 

연결 속성 편집

Excel을 연결 파일 편집기로 사용 하 여 통합 문서 또는 연결 파일에 저장 된 외부 데이터 원본에 대 한 연결을 만들고 편집할 수도 있습니다. 원하는 연결을 찾지 못한 경우 에는 더 찾아보기 를 클릭 하 여 데이터 원본 선택 대화 상자를 표시 한 다음 새 원본을 클릭 하 여 데이터 연결 마법사를 시작 하 여 연결을 만들 수 있습니다.

연결을 만든 후 데이터 탭의 외부 데이터 가져오기 그룹에서 속성을 클릭 하 여 연결 속성 대화 상자를 사용할 수 있습니다. 외부 데이터 원본에 대 한 연결의 다양 한 설정을 제어 하 고 연결 파일을 사용, 재사용 또는 전환할 수 있습니다.

연결 파일을 사용 하 여 데이터 원본에 연결 하는 경우 Excel에서 연결 파일의 연결 정보를 Excel 통합 문서에 복사 합니다. 연결 속성 대화 상자를 사용 하 여 변경 하는 경우 연결을 만드는 데 사용 된 원본 데이터 연결 파일 ( 정의 탭의 연결 파일 속성에 표시 되는 파일 이름으로 표시 됨)이 아니라 현재 Excel 통합 문서에 저장 된 데이터 연결 정보를 편집 하는 것입니다. 연결 정보를 편집 하면 ( 연결 이름 및 연결 설명 속성을 제외한) 연결 파일에 대 한 링크가 제거 되 고 연결 파일 속성이 지워집니다.

데이터 원본을 새로 고칠 때 연결 파일이 항상 사용 되도록 하려면 항상이 파일 사용 을 클릭 하 여 정의 탭에서이 데이터를 새로 고칩니다. 이 확인란을 선택 하면 연결 파일에 대 한 업데이트가 해당 연결 파일을 사용 하는 모든 통합 문서에서 항상 사용 되며,이 속성 집합도 설정 해야 합니다.

연결 관리

통합 문서 연결 대화 상자를 사용 하 여 이러한 연결을 만들고, 편집 하 고, 삭제 하는 등 쉽게 관리할 수 있습니다. ( 데이터 탭의 외부 데이터 가져오기 그룹에서 연결을 클릭 합니다.) 이 대화 상자를 사용 하 여 다음을 수행할 수 있습니다.

• 통합 문서에서 사용 중인 연결을 만들고, 편집 하 고, 새로 고치고, 삭제할 수 있습니다.

• 외부 데이터의 원본을 확인 합니다. 다른 사용자가 연결을 정의한 경우이 작업을 할 수 있습니다.

• 현재 통합 문서에서 각 연결이 사용 되는 위치를 표시 합니다.

• 외부 데이터로의 연결에 대 한 오류 메시지를 진단 합니다.

• 연결을 다른 서버 또는 데이터 원본으로 리디렉션하거나 기존 연결에 대 한 연결 파일을 바꿉니다.

• 사용자와 연결 파일을 간편 하 게 만들고 공유할 수 있습니다.