[Power BI] 엑셀 데이터 - 엑셀 데이터 변환 및 모델링 하기 (3)

 

"데이터 모델링을 한 후 테이블을 분리하여 운영할 필요가 있을때" 

오늘 알아볼것은  "데이터 모델링 후 하나의 원본 엑셀을 여러 테이블로 분리 하는 방법" 입니다. 

원본 엑셀데이터의 모든 항목이 BI 작업 시 필요는 하지만 하나의 큰 엑셀 시트에 모든 내용이 전부 들어 있고  그 양이 방대할 경우 우리는  데이터 모델링을 통해 시트의 내용을 기준에 맞게 분리하여 운영하여야 합니다. 

먼저 엑셀 시트를 기준으로 모델링 하려는 구조를 확정 한 후 "데이터 변환"을 진행 합니다. 
데이터 변환이 완료되면 쿼리 편집기에서" 매출정보" 테이블을 확인 하실 수 있을 것입니다. 우리는 "매출정보" 테이블을 "매출정보", " 매장정보","책정보" 이렇게 3개의 테이블로 분리하여 모델링을 기획하였기에 "매출정보"테이블을 기준으로 나머지 2개의 테이블을 생성하여야 합니다. 

아리의 1번과 같이 "매출 정보" 테이블에서 오른쪽 마우스를 클릭하고 "복제"를 선택하면 "매출정보(2)"의 명으로 동일한 정보의 테이블이 복제됩니다. 이 후 복제된 테이블에서 마우스 오른쪽을 클릭하고 2번 "이름바꾸기" 를 선택하여 원하는 이름으로 변경 할 수 있습니다. 

 

이런방식으로 3번 과 같이 "매출정보", " 매장정보" 그리고 "책정보" 등 3개의 동일한 정보를 가지고 있는 테이블을 구성 할 수 있습니다. 

생성된 3개의 테이블은 복제, 이름 바꾸기를 하였기에 모두 동일한 데이터를 가지고 있습니다. 아래와 같이 각각의 필요한 데이터만 가지고 있을 수 있도록 데이터를 수정 하여야 합니다

쿼리 편집기의 “매출정보”을 선택하면 쿼리 영역에 원본테이블에서 모든 데이터를 가져오는 쿼리가 작성되어 있습니다. 이 쿼리 중 우리가 매출정보 테이블에서 관리할 필드 만을 가지고 있도록 하여야 하기에  “매출번호“ , “판매금액”,”판매개수”, ＂매장 ID”  , “책 ID” 만 남겨두고 나머지 필드명을 삭제 하여야 합니다. 

그리고 필요 없는 열은 아래와  같이 필드 명에서 우측 마우스를 클릭 후 “제거 “ 해줍니다. 제거를 완료하면  쿼리마법사의 적용단계에 “제거된 열 수“ 항목이 추가 됩니다. 

이 단계를 나머지 복제된 테이블 “매장정보“ , “책 정보“ 테이블에서 동일하게 필요한 필드만 선택하여 진행합니다.

여기서 중요한 것은 우리가 구성하려고 한 “매장정보“,”책정보＂는 기준 데이터 입니다.
기준 데이터는 중복되는 데이터가 없어야 합니다. 즉 “책 정보“ 테이블의 책” ID”는 테이블에 1개만 존재하는 유일한 데이터야 합니다. 하지만 우리는 전체 정보가 들어있는 “매출정보” 테이블에서 데이터를 복제 하였기에 “책 ID” , “책 제목“,” 정가＂가 아래와 같이 중복으로 데이터가 들어가 있습니다

책 ID 별로 책 제목과 책의 정가는 항상 동일하고 “책 정보＂테이블은 기준정보 테이블로 사용할 목적이기에 우리는 중복된 테이블에 데이터에 대하여 중복 제거를 진행하여야 합니다.

1의 단계 추가 버튼을 클릭하면 자동으로 마지막 단계 기준으로 아래와 같은 수식이 생성됩니다.

이 수식을 기준으로 중복 제거 쿼리를 작성해 주어야 합니다.  Power Query의 열 중복 제거 함수는  Distinct 입니다.  아래와 같이 Table.Distinct 로 중복제거 쿼리를 만드실 수 있습니다.

#”제거된 열 수＂는 현재 보여지는 테이블의 마지막 쿼리 설정 단계로 해당 단계의 테이블 구조에서 “책 ID” 값을 기준으로 중복되는 데이터를 제거하라는 명령어 입니다.

 

 

중복 제거 쿼리를 실행 하면 그림과 같이 “책 ID” 기준으로 중복되었던 동일 데이터가 제거 되었음을 확인 할 수 있습니다. 같은 방법으로 매장 정보 테이블도 “매장 ID” 기준으로 중복을 제거 하여야 합니다.

이제 “매출정보“ 그리고 기준정보인 “ 매장정보“, “책 정보“ 테이블이 구성이 되었습니다. 이젠 데이터 원본(엑셀) 에서 데이터가 추가되거나 변경되었을 때  Power BI 상단 메뉴의 “새로 고침＂을 통해 데이터를 갱신할 경우 원본 엑셀의 “매출정보” 테이블의 변경된 정보가 Power BI 의 “매출정보“ , “매장 정보“, “책 정보“ 테이블로 자동으로 분리되어 업데이트 후 중복 제거가 진행될 것입니다.

 

엑셀에서 “매출정보“,”매장정보“,”책 정보＂테이블 구성이 완료되었으며 해당 테이블 간에 관계를 맺어 줘야 합니다. 

“매출정보” 테이블에는 “매장정보”의 고유 기본 키 인 “매장 ID” 그리고 “책 정보“ 테이블의  고유 기본 키 인 “책 ID”를 가지고 (외래 키) 있습니다. 해당 키 를 통해 서로 관계를 맺어주면 분리하였던 데이터를 시각화 할 시 하나의 테이블 처럼 사용할 수 있습니다. 

매출 정보와 매장 정보  :  다 대 1 관계 (키 : 매장 ID)

매출 정보와 책 정보 : 다 대 1 관계 (키 책 ID)

데이터 모델링의 자세한 설명은 “데이터 모델링“ 에서  자세한 설명할 예정입니다.