[Power BI] 엑셀 데이터 가져오기 - 엑셀 변환 하기 (1)

 

엑셀에서 데이터를 가져오는 방법 중 이전에 설명한 “로드＂로 전체 데이터를 동일한 형식으로 가져오는 방법외에도  데이터 모델링 또는 원본 데이터 중 필요한 데이터만을 가져오게 하는 “데이터 변환＂방법이 있습니다.

우리가 BI로 인사이트를 얻으려고 하는 데이터의 양은 타 시스템에서 주기적으로 내려 받는 대용량의 데이터 이거나 오랜 기간 꾸준히 데이터를 업데이트한 대용량의 엑셀일 것입니다. 

이런 엑셀데이터는 엑셀로 관리하기 편하게 (한눈에 보기 편하게)  대부분 하나의 큰 시트로 구성되어 있으며 용량이 크기에 점점 속도는 느려지고 원하는 상세 내용만을 추출해서 보기에는 한계가 있습니다.

이런 데이터를 ＂로드”를 이용해서 똑같이 Power BI로 이동을 하여 관리를 하게 된다면 단점은 그대로 가지고 가실 수 밖에 없습니다.

그래서 우리는 “데이터 변환＂의 방법으로 대용량의 엑셀 데이터 중 꼭 필요한 필드들만 가져오고 원본 엑셀의 데이터가 업데이트 되었을 때 자동으로 원하는 데이터만 갱신되는 방식이 필요로 합니다.

또한 하나의 큰 시트에 포함된 데이터 관계를 분석하여 여러 테이블로 데이터 모델링을 하여 관계를 명확히 하고 속도를 향상 시키며 각각의 테이블의 데이터 집합 마다  빠른 분석을 할 수 있습니다.

데이터 모델링 방법은 “데이터 모델링“ 에서 예시를 다루도록 하겠습니다. 아래는 모델링을 하는 구조 이며 하나에 통합되어 있던 데이터 들을 각각 분리하여 관계를 맺는 모습 입니다.  

엑셀 에서 관리하는 "매출 상세 " 시트 에서는 매출정보와 매장정보, 그리고 책 정보가 모두 포함되어 있으며 이를 우리는 순수한 매출정보만 있는 테이블과, 기준정보인 매장정보, 책정보 테이블로 분리 하는 과정을 진행할 것입니다.

매출정보 테이블의 키는 "매출번호" 이며 기준 테이블인 매장정보 테이블의 기본 키는 "매장 ID", 그리고 책 정보 테이블의 기본키는 "책 ID" 이며, "매장ID"와 "책 ID"는 매출정보 테이블에 각각 외래키 로 등록됩니다. 

 

 

자..이젠 가져오려는 데이터의 선별 또는 모델링에 대한 계획을 다 준비하였다면 Power BI에서 데이터 가져오기 > 엑셀 을 선택 후 원하는 시트에 대하여 “데이터 변환” 버튼을 클릭 합니다.

“데이터 변환“을 클릭하면  Power Query  편집기가 아래와 같이 열릴 것입니다. 1인 테이블로 “변경된 유형”에 대한 쿼리 내용이며 2는 해당 쿼리로 가져온 데이터 입니다. 

우측의 쿼리 설정 탭에서 “적용된 단계"는 기본적으로 “원본“,”탐색“,”승격된 헤더“,”변경된 유형이 표기가 됩니다.

여기서 원본은 가져온 엑셀 파일의 물리적 위치를 나타내며, 탐색은 선택한 시트 그리고 승격된 헤더와 테이블로 변경된 유형에 대한 쿼리를 하나씩 클릭 하며 확인 할 수 있습니다. 추가로 쿼리를 변경하면 적용단계에 해당 쿼리 내역이 추가되어 관리 됩니다.

다음 포스팅에서는 2가지 케이스로 "쿼리 편집", "모델링" 방법에 대하여 자세히 설명 할 것입니다 .^^  오늘은 여기까지. !!