파워비아이에서 여러 엑셀 파일을 불러와 합치는 방법 (Excel files import in Power BI)

파워 비아이에서는 엑셀, SQL, 웹 등 여러 가지 데이터 소스를 불러올 수 있습니다. 일반적인 개인 사용자들은 엑셀을 자주 불러올 것입니다. 하나의 엑셀 파일은 물론 폴더에 같은 포맷의 여러 엑셀 파일들을 한꺼번에 불러와서 하나의 테이블로 합칠 수도 있습니다. 참으로 유용한 기능이라고 할 수 있는데요, Power BI에서 같은 포맷의 여러 Excel files를 불러와 합치는 방법을 정리해 보겠습니다.

 

예제 엑셀 데이터 설명

여러 엑셀 파일을 한꺼번에 불러와서 합쳐지는 동작을 보여드리기 위해서 간단한 예제 엑셀 파일을 만들었습니다. 김영희, 김철수, 홍길동 3명의 팀원의 이번 주 야근 기록을 하나의 파일로 합치려는 상황입니다. 합치고자 하는 파일은 XXX.xlsx의 파일명으로 ‘엑셀 합치기’라는 폴더 안에 있습니다. 3개의 파일만 합치려고 하는데 해당 폴더에 pbix 파일도 있고, 포맷이 다른 오류파일.xlsx 파일이 섞여 있는 경우를 가정해보겠습니다.

예제 엑셀

 

작업 순서

① [파워쿼리] – [쿼리] 영역에서 마우스 우클릭을 합니다.

② [새쿼리] 를 클릭합니다.

③ [추가] 를 선택합니다.

④ [폴더] 를 선택합니다.

⑤ 파일들이 모여있는 폴더 경로를 지정해 줍니다.

폴더 지정

 

⑥  새로운 창이 뜨면 내용 확인 후 [데이터 변환]을 선택합니다. 이번 예제에서는 폴더 내에 pbix 파일도 걸러내야 하고, 오류 파일도 정리해야 하기 때문입니다. 만약 폴더 내에 잘 정리된 엑셀 파일만 모여 있다면 바로 [데이터 결합 및 변환]을 선택하면 됩니다.

⑦ Extension 열에 파일 확장자가 나타나는데, 엑셀 파일만 불러오기 위해서 pbix는 필터링합니다.

⑧ Content 열에 두 개의 아래 화살표 모양의 아이콘을 선택합니다.

필터링

 

⑨ 파일을 병합하는 샘플 파일을 선택합니다. 폴더 안의 엑셀 파일이 표시되는데 오류파일.xlsx를 제외한 임의의 파일을 선택합니다.

⑩ [표 1]을 선택합니다. Sheet1을 선택할 수도 있겠지만 만약 테이블 영역 밖의 원치 않는 데이터들이 들어가 있을 경우 제외하고 불러오기 위해서는 표의 형태로 불러오는 게 좋습니다.

⑪ 엑셀 파일을 불러와 하나의 데이터로 합쳐진 결과가 나타납니다. 오류 파일은 데이터의 포맷이 다르기 때문에 null 값으로 표시됩니다. 따라서 샘플 파일을 잘 선택하는 게 중요하겠습니다.

샘플 파일 선택

 

참고 사항

합쳐진 테이블에 각 엑셀 파일의 Total 행도 합쳐져 있습니다. 합쳐진 상태에서 Total 행을 필터링을 할 수도 있고, [샘플 파일 변환]에서 [행 제거] – [하위행 제거]를 적용해도 됩니다. 즉, 샘플 파일을 변환하면 다른 파일을 불러와서 합칠 때도 적용된다는 의미로 이해하시면 되겠습니다.

샘플 파일 변환

 

요약 및 정리

파워 비아이에서 같은 포맷의 엑셀 파일들이 한 폴더 안에 있을 때 한꺼번에 불러와서 합치는 방법을 정리했습니다. 또한 같은 폴더 안에 원치 않는 확장자의 파일들을 걸러내는 방법과 다른 포맷의 엑셀 파일은 null 값으로 불러와진다는 내용도 확인했습니다. 

Power BI에서 여러 excel file의 데이터를 취합하여 분석하는데 용이하게 활용할 수 있겠습니다. 또는 분석까지는 아니더라도 여러 엑셀 파일을 하나의 파일로 합쳐야 할 필요가 있을 때 파워비아이를 사용하면 쉽게 작업이 가능하겠습니다.