[ 엑셀 Tip ] 이중 헤더( Double Header ) 전처리 ( Feat. 파워쿼리 )

엑셀 이중 헤더 데이터를 정규화 하는 방법에 대해서 알아 보도록 하겠습니다.

최근 서울페이_업종별_지역화폐_매출_24년08월 데이터를 받았는데 데이터이 이중헤더로 되어 있어서 

작업하다가 소개해 드립니다.

※ 이번에 소개해 드릴 방법은 파워쿼리가 주로 사용 예정입니다 ( 엑셀 2019 이상 or Power BI 사용자 참고 )

열린데이터광장 메인

해당 참조 파일은 열린데이터 광장에서 다운로드 가능합니다.

---

① 데이터 탐색

데이터는 가로로 더 넓은 데이터이나 간단하게 3개의 카테고리를 가지고 전처리를 진행해 보도록 하겠습니다.

이런 데이터는 보기는 좋은데 분석하기에는 다소 어려운 상황이 발생할 수 있어 정규화가 필요한 경우가 있습니다.

② 데이터 불러오기

엑셀 데이터 - 파일에서 - 엑셀통합문서에서를 선택 후 다음과 같이 파워쿼리로 데이터를 가져옵니다.

Data 컬럼의 Table을 다음과 같이 데이터를 불러옵니다.

③ 헤더 전처리

행유지 - 상위 행 유지 - 행 수 3입 입력해 주도록 하겠습니다.

헤더를 하나로 만들어주기 위해서 해당 테이블의 가로 세로를 바꿔주도록 하겠습니다.

변환 - 행/열 바꿈을 클릭 후 가로 데이터를 세로로 변경해 주었습니다.

변환 - 채우기 - 아래로를 눌러 우측과 같이 null 값을 위의 값으로 채워 넣었습니다.

※ 엑셀에서 빈칸 채우기는 파워쿼리에서는 쉽게 진행할 수 있습니다.

각 컬럼의 값을 구분기호로 구분하여 합치기 위해서 해당 열을 List로 변경해 주도록 하겠습니다.

반응형

파워쿼리 M function 중 Table.ToRows 함수를 사용해서 Row를 List로 변경해 주었습니다.

각 Row의 값이 List의 값으로 결합되는 것을 볼 수 있습니다.

이 각 List에 있는 값들을 하나의 값으로 합쳐 보도록 하겠습니다.

List.Transform 함수 & Text.Combine 함수를 사용해서 List의 값을 "_" 구분기호로 결합해 주었습니다.

List.Transform( Table, each _ ) : 각 열을 값을 바꾸고 싶다
Text.Combine( _, "_" ) : 각열의 Text를 구분기호 "_"로 구분 후 합쳐줘 

해당 단계를 col이라고 정의 후 다시 원본을 불러와 줍니다.

④ 데이터 전처리

헤더는 만들어 놓은 것을 사용할 것이기 때문에 상위 3개의 항목을 지워 줍니다.

테이블의 헤더를 변경해 주기위해서 해당 테이블의 컬럼을 각각의 List로 변경해 주도록 하겠습니다.

Table.ToColumns 함수를 사용해서 각 Column을 List로 변경해 주었습니다.

예를 들어 Column1은 첫번째 행 List로, Column2는 두번째 행 List로,....... 변경해 주는 단계 입니다.

이제 컬럼을 다시 테이블로 변경하면서 아까 만들어 놓은 col을 헤더로 지정해 주도록 하겠습니다.

짜잔 헤더가 하나로 만들어 졌습니다. 이중헤더를 하나의 헤더로 변경이 가능합니다.

가로 데이터를 세로로 만들기 위해서 다른 열 피벗 해제를 클릭합니다.

다음과 같이 테이블이 나오면 발행처 중 "합계"를 필터 제외 후 특성 컬럼 값을 구분기호를 통해서 나눠 주도록 하겠습니다.

다음과 같이 데이터 테이블이 나왔습니다.

컬럼명 변경 후 값 중 "0"값을 필터링 하면 다음과 같이 전처리가 완료 되었습니다.

닫기 다음으로 로드에서 연결만 & 데이터 모델링에 추가 후 간단한 분석을 진행해 보도록 하겠습니다.

728x90

---

⑤ 데이터 분석 ( 시각화 )

지역화폐 결제 금액이 가능 많은 곳은 용산구 - 강남구 - 서초구 순으로 나오고 있습니다.

가구/인테리어에 가장 많은 지출을 하고 있고 건축 / 철물에 가장 작은 비용을 사용하고 있습니다.

Dax rankx를 사용해서 카테고리별 Top3만 표시해 줍니다.

= var r = rankx( all( 'Sheet0'[발행처] ), CALCULATE( sum( 'Sheet0'[값] ) ) )
var re = if( r <= 3, CALCULATE( sum( 'Sheet0'[값] ) ), BLANK() )
return re