마우스 클릭 몇 번으로 데이터 정리 끝! 파워 쿼리 핵심 기능 4가지

1. 구분 기호를 기준으로 셀 값 분리하기

데이터를 여러 열로 나누는 가장 직관적이고 강력한 방법은 파워 쿼리 편집기의 [열 분할] 기능을 사용하는 것입니다. 원본 데이터를 훼손하지 않고도 클릭 몇 번으로 깔끔하게 데이터를 쪼갤 수 있습니다.

방법은 간단합니다. 분할할 열 머리글을 우클릭한 뒤 [열 분할] - [구분 기호 기준]을 선택하세요. 파워 쿼리가 자동으로 대시(-)나 쉼표(,) 같은 기호를 감지하여 제안해 줍니다.

(1) [위키백과]에서 파워 쿼리를 사용하여 변환하려는 표를 찾았다고 가정해 보겠습니다. [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [데이터 가져오기] > [기타 원본에서] > [웹]을 선택합니다.

대화상자의 URL 입력란에 [링크 주소]를 붙여넣고 [확인]을 클릭합니다.

(3) [웹 콘텐츠 액세스] 대화상자에서 [연결]을 클릭합니다.

(4) 탐색 창 왼쪽 영역에서 변환하고자 하는 테이블을 선택하고 [데이터 변환]을 클릭합니다.

Power Query 편집기에서 테이블을 로드할 때 Career Span 열에 두 가지 데이터(시작 연도, 종료 연도)가 포함되어 있는 것을 확인했습니다. 각 선수가 경력을 시작한 해와 마지막으로 경기를 했던 해입니다. 이 두 데이터를 별도의 열에 넣고자 합니다.

(1) 열 머리글을 우버튼으로 클릭하고 [열 분할] - [구분 기호 기준]을 선택합니다.

(2) Power Query 편집기는 열의 데이터를 검토하여 잠재적 구분 기호를 감지할 수 있는지 확인합니다. 이 경우, 각 셀에 대시가 포함되어 있음을 확인하고, 이 지점이 셀을 분할해야 할 지점이라고 올바르게 가정합니다. 하지만 필요한 경우 첫 번째 드롭다운 메뉴에서 다른 구분 기호를 선택할 수 있습니다.

(3) 이 예시에서는 선택된 열의 각 셀에 구분 기호가 하나만 있으므로 대화 상자의 옵션을 추가로 변경할 필요가 없습니다. 따라서 [확인]을 클릭합니다. 두 데이터가 별도의 열로 분할됩니다.

(4) 새 열의 이름을 바꾸려면 열 머리글을 두 번 클릭하고 새 데이터 레이블을 입력합니다(필요 시).

(5) Power Query 편집기를 사용하여 데이터를 성공적으로 분할했습니다. 그런데 두 개의 새 열의 셀이 서로 다르게 정렬되어 있습니다. 앞의 열은 숫자인 반면, 뒷 열은 텍스트로 되어 있습니다. 이 문제를 해결하려면 해당 열 머리글의 [ABC] 아이콘을 클릭하고 [정수]를 선택합니다.

(6) [열 형식 변경] 대화상자에서 [현재 전환 바꾸기]를 클릭합니다.

(7) 두 번째 열도 숫자 형식으로 변경되었습니다. 하지만 Error가 보이는군요. 이 문제는 이어서 해결하겠습니다.

2. 오류 표시를 의미 있는 값으로 변경하기

데이터 형식을 변경하다 보면 텍스트와 숫자가 섞여 있어 Error가 발생하곤 합니다. 파워 쿼리는 이런 오류를 일괄 처리하는 데 매우 유능합니다. 오류가 있는 셀을 그대로 두면 나중에 필터링이나 수식 계산 시 문제가 생길 수 있으므로 미리 조치하는 것이 좋습니다.

(1) 열 머리글을 우클릭하고 [오류 바꾸기]를 선택합니다. 이 때 [오류 제거]를 선택하지 않도록 주의하세요. [오류 제거]를 선택하면 오류뿐만 아니라 행 전체가 삭제됩니다.

(2) [오류 바꾸기] 대화상자에서 오류 대신 표시할 값을 입력합니다. 이 경우, 셀에 "present"라고 표시된 부분에 오류가 표시되었으므로 현재 년도를 입력합니다.

(3) [확인]을 클릭하면 Error가 (2)에서 입력한 년도로 대체됩니다.

가장 오른쪽에 있는 두 열의 모든 셀에는 오류 없이 동일한 데이터 유형이 모두 포함되어 있으므로 각 선수의 총 경기 연도를 계산하는 새 열을 만들 수 있습니다.

(1) [열 추가] 탭 > [사용자 지정 열]을 클릭하고, 열 이름을 "활동년수"로 지정합니다.

(2) [사용 가능한 열] 메뉴의 열 목록을 사용하여 간단한 뺄셈 수식을 작성합니다.

(3) [확인]을 클릭합니다. Career2에서 Career1이 차감된 "활동년수"가 자동으로 구해집니다.

(4) Power Query 편집기의 홈 탭에서 [닫기 및 로드]를 클릭하면 해당 테이블이 Excel 통합 문서의 워크시트에 생성됩니다.

3. '넓은 표'를 분석하기 좋은 '긴 표' 형태로 만들기

다음과 같이 연도가 가로로 나열된 '넓은 표'는 보기엔 편하지만 데이터 분석에는 적합하지 않습니다.

위의 표를 다음 이미지와 비교해 보세요. 파워 쿼리의 [열 피벗 해제] 기능을 사용하면 가로로 펼쳐진 데이터를 세로로 길게 세워 피벗 테이블 분석에 최적화된 형태로 바꿀 수 있습니다.

(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [테이블/범위에서](또는 버전에 따라 [표/범위에서])를 클릭합니다.

데이터가 엑셀 표 형식이 아닌 경우, 이를 수정하라는 대화상자가 나타납니다. 이것은 Power Query 편집기가 데이터를 더 쉽게 처리할 수 있도록 하기 위한 것이므로 엑셀 표 형식으로 변환해야 합니다.

(2) [Power Query 편집기]에서 피벗 해제할 모든 열의 머리글을 선택합니다. 여기서는 "2022년" 머리글을 클릭하고 Shift 키를 누른 상태에서 "2025년" 머리글을 클릭합니다.

(3) [변환] 탭 > [열 피벗 해제] 드롭다운 > [선택한 열만 피벗 해제]를 선택합니다.

(4) 테이블 형태가 바뀌었습니다. 열 머리글을 더블 클릭하여 새로운 열 이름을 입력할 수 있습니다.

'특성'은 '년도', '값'은 '영업이익'으로 각각 수정하고 엔터를 누르면 열 머리글이 변경됩니다.

(5) [홈] 탭 > [닫기 및 로드]를 클릭하면 피벗 해제된 테이블이 엑셀 워크시트에 표시됩니다. 이 데이터를 이용하면 필터나 파워 피벗을 비롯한 엑셀의 강력한 분석 도구를 자유롭게 사용할 수 있습니다.

4. 빈칸 자동으로 채우기

데이터 집합 중간중간 비어 있는 셀은 정렬이나 필터링을 방해합니다.

파워 쿼리의 [채우기] 기능을 사용하면 위에 있는 팀 이름이나 번호를 아래 빈칸(null)에 번개처럼 복사해 넣을 수 있습니다.

(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [테이블/범위에서]를 선택합니다. 데이터가 엑셀 표 형식이 아니라면 표 형식으로 변환합니다.

(2) [Power Query] 편집기가 열립니다. 원본 데이터에서 빈 셀이던 곳에는 'null'로 표시되어 있습니다.

(3) 이 예제에서는 값을 채울 열이 2개입니다. 열 머리글을 이용하여 2개의 열(여기서는 열1, 열2)을 선택하고 [변환] 탭 > [채우기] 드롭다운 > [아래쪽]을 선택합니다.

(4) 빈 셀(null)이 모두 위에 있는 값으로 채워집니다.

[홈] 탭 > [닫기 및 로드]를 클릭하면 채워진 데이터가 엑셀 워크시트에 표시됩니다.

MVP TIP

1. 원본 데이터가 바뀌면 어떻게 하나요?

파워 쿼리의 가장 큰 장점은 '재사용성'입니다. 원본 데이터에 새로운 행이 추가되어도 상단 [데이터] 탭 - [모두 새로 고침]만 클릭하면, 이전에 설정한 열 분할이나 피벗 해제 작업이 새 데이터에 즉시 다시 적용됩니다.

2. 적용한 단계를 취소하고 싶을 때

파워 쿼리 편집기 오른쪽에는 [적용된 단계] 목록이 있습니다. 작업 중 실수했다면 단계 앞의 'X' 아이콘을 클릭하여 이전 상태로 되돌릴 수 있습니다. 엑셀의 '실행 취소(Ctrl+Z)'보다 훨씬 강력한 이력 관리 기능을 제공합니다.

3. 데이터 형식 아이콘을 확인하세요

열 머리글 왼쪽의 [ABC] 또는 [123] 아이콘을 확인하세요. 파워 쿼리는 텍스트, 숫자, 날짜를 엄격히 구분합니다. 계산이 필요하다면 반드시 [정수]나 [통화] 등 올바른 데이터 형식으로 설정되어 있는지 체크해야 합니다.

자주 묻는 질문 (FAQ)

마치며

파워 쿼리는 엑셀 전문가들만 사용하는 도구가 아닙니다. 반복되는 데이터 정리 작업에 지친 분들이라면 누구나 환영할 만한 도구입니다. 오늘 소개해 드린 4가지 핵심 기능을 실무에 바로 적용해 보세요. 드래그하고 메뉴를 찾아 헤매던 시간을 획기적으로 줄여, 더 빠르고 스마트한 엑셀 라이프를 즐기실 수 있을 것입니다.