[ 파워쿼리 ] 엑셀 시트 데이터 합치기

엑셀의 한계를 극복하기 위한 방법을 알아보도록 하겠습니다.

다음과 같이 데이터를 준비해 보도록 하겠습니다.

11/1일	11/2일
합계 : 529,419,156	합계 : 529,635,427

 

이전에 이야기를 드린 것과 같이 엑셀의 입력 한계는 1,048,576열입니다.

그래도 예전보다는 많이 늘어나기는 했는데 아직도 더 많은 다루기에는 어려움이 있어 보입니다.

이 한계를 극복하는 방법으로 엑셀 내의 시트의 데이터를 합치는 방법을 알아보도록 하겠습니다.

---

 

엑셀의 데이터를 파워쿼리로 불러와 보도록 하겠습니다.

불러오는 방법은 아래를 참조해 주십시오.

[ 파워쿼리 ] 데이터 불러오기( with. Excel )

 

아래와 같이 파워쿼리 창에 데이터를 불러온 것을 볼 수 있습니다.

 

값을 잘 불러왔는지 알아보기 위해서 value 컬럼을 선택 후 통계의 합계를 눌러 줍니다.

합계가 529,419,156으로 잘 불러온 것을 알 수 있습니다.

 

그런데 이 데이터는 하루의 데이터만을 나타 냅니다.

매일매일 데이터가 쌓이는데 엑셀 하나의 시트는 하루치의 데이터를 남아내기 힘든 경우가 있습니다.

 

 

이제 아까 준비한 11/2일 자 데이터를 해당 파일 내로 이동해 보도록 하겠습니다.

Sheet1과 Sheet2가 같은 파일 내에 존재하도록 이동 후 저장을 눌러줍니다.

이 데이터를 옮기는데 엑셀이 살짝 버벅거리는 거 같습니다. 

( 이 상황에서 sumif, vlookup 등 다양한 함수를 쓰면 어떨지 끔찍할 거 같습니다 )

 

 

이제 다시 [ 데이터 ] - [ 쿼리 및 연결 ]에서 파워 쿼리 창을 열어 Sheet1을 더블클릭해 보도록 하겠습니다. 

 

 

하지만 아무 일도 일어나지 않았습니다.

이때 적용된 단계의 원본을 눌러보도록 하겠습니다.

 

 

여기 보니까 Sheet2가 추가되어 있는 것을 볼 수 있습니다.

그런데 왜 처음과 달라지는 것 없이 그대로였을까요?

 

바로 아래의 적용된 단계의 탐색을 눌러보도록 하겠습니다.

아래 보시면 선택을 Sheet1만 한 상태이기 때문에 두 번째 시트는 가져오지 않기 때문에 그렇습니다.

그럼 이제 Sheet2를 가져와서 합치는 작업을 해 보도록 하겠습니다.

 

 

다시 적용된 단계의 원본을 눌러 줍니다.

Data 컬럼 우클릭 후 다른 열 제거를 눌러 줍니다.

그리고 Data 컬럼의 우측 상단에 조그맣게 있는 확장 버튼을 눌러 주도록 하겠습니다.

 

테이블 왼쪽 상단의 버튼을 눌러 첫 행을 머리글로 사용을 눌러 헤더를 만들어 주도록 하겠습니다.

 

date 컬럼을 눌러 날짜를 확인해 보도록 하겠습니다.

11/2일도 추가된 것을 볼 수 있는데 불필요한 date 항목도 들어 있습니다.

 

오류 발생 원인

첫 행을 머리글로 할 때 Sheet1의 항목만 헤더로 올라오고 Sheet2의 헤더는 그대로 남기 때문입니다.

고급 처리 방법은 아래의 원본에서 null을 true로 변경하는 방법이 있습니다.

 

 

간단하게 진행하기 위해서 date 컬럼의 date 항목의 필터를 걸어 값이 나오지 않게 해 줍니다.

컬럼의 값 형식을 지정해 주면 다음과 같이 데이터 처리가 끝난 것을 알 수 있습니다.

 

데이터 검증을 위해서 date 항목을 그룹화 후 value 값을 더해 보도록 하겠습니다.

이 방법은 추후에 다시 이야기드리도록 하겠습니다.

엑셀의 sumif와 피벗테이블을 사용하는 방식과 동일합니다.

 

 

 

그룹화의 값을 다양한 방법이 있는데 표로 가져와 보도록 하겠습니다.

 

처음에 있는 값과 동일한 결과가 나오는 것을 볼 수 있습니다.

추천하는 방법은 아니지만 Sheet를 계속 추가하면 일자별 데이터를 합쳐서 관리가 가능합니다.

11/1일	11/2일
합계 : 529,419,156	합계 : 529,635,427

 

하지만 제가 추천하는 방법은 시트를 합치는 것이 아닌 파일을 합치는 방법입니다.

아래의 방법이 더 효율이니 참고해 주시고 간단한 데이터를 합치는 상황에 시트를 합치는 방법을 사용해 주십시오.

[ 파워 BI ] 파워 쿼리를 이용한 데이터 합치기 ( 파일 합치기 )