Excel은 다양한 Excel 수식과 앱 도구 덕분에 데이터를 변환하고 조작하는 데 강력한 도구입니다. Power Query를 추가하면 다양한 소스에서 데이터를 추출하고 실시간으로 변환하는 더 간편한 방법을 얻을 수 있습니다.
초보자라면 Power Query는 처음에는 혼란스러울 수 있습니다. 일반적인 수식이 더 이상 같은 방식으로 작동하지 않고 새로운 코딩 언어인 M 코드가 등장하기 때문에 완전히 낯선 Excel 섹션으로 이동합니다.
그러나 처음의 학습 곡선을 넘어서면 Power Query는 모든 데이터 변환 단계를 완벽히 추적하면서 대량 데이터에 대한 일괄 변환을 수행하는 가장 효과적인 도구 중 하나입니다. 여기서 Power Query를 시작하고 견고한 기반을 구축하는 가장 좋은 명령어에 대한 철저한 안내서가 있습니다.
Power Query의 기초에 대해 알아보자. 일반 Excel은 스프레드시트 자체에 테이블을 만들거나 붙여넣을 수 있지만 Power Query는 다양한 소스에 연결하여 데이터를 가져올 수 있습니다. 이것이 데이터에 액세스하는 첫 번째 단계인 “데이터 가져오기 및 변환” 섹션으로 이동하고 “데이터” 탭 아래에서 데이터 원본을 선택해야 하는 이유입니다. Excel은 데이터베이스, 웹페이지, CSV, Excel, PDF 및 다양한 파일 형식에서 데이터를 가져오는 것을 지원합니다. Power Query를 사용하면 여러 데이터 소스를 하나로 연결하고 병합할 수 있는 기능이 큰 장점입니다.
데이터 원본을 연결하면 데이터를 “로드”하거나 “변환”할 수 있습니다. 데이터 로드는 데이터를 Excel 워크시트나 Power BI 보고서로 가져오는 것을 의미합니다. 데이터 변환은 Power Query 편집기를 사용하여 데이터 조작을 수행하는 것을 의미합니다. 여기서 유용한 Power Query 명령어를 모두 활용할 수 있습니다. Power Query 편집기 상단에는 데이터 조작에 필요한 모든 중요한 기능이 통합된 다양한 탭이 있습니다. 왼쪽에는 편집기에 연결된 모든 데이터 원본을 나열하는 “쿼리” 섹션이 있습니다. 다양한 탭 아래에 있는 공식 바에는 M 언어의 공식이 포함되어 있습니다. 중앙에는 연결된 데이터의 미리 보기가 탭 형식으로 표시됩니다. 가장 오른쪽에는 데이터를 변환하는 동안 수행한 모든 조작 단계를 나열하는 “적용된 단계” 섹션이 있습니다.
처음 시작하는 경우 Power Query를 시작하고 강력한 기초를 구축하기 위한 최고의 명령어 중 일부는 다음과 같습니다.
열 필터링은 Excel 워크시트와 거의 동일한 방식으로 작동하지만 더 편리합니다. Excel은 먼저 열 머리글에 필터를 적용해야 하지만 Power Query에서는 필터가 기본으로 적용됩니다. 열을 필터링하려면 열 머리글 옆의 드롭다운을 클릭하여 열의 모든 값 목록을 엽니다. 필터링할 값에서 체크를 해제하고 확인을 클릭하여 필터링을 완료합니다.
텍스트 및 숫자 필터를 사용하여 미리 정의된 규칙에 따라 값의 필터링을 수행할 수도 있습니다. 예를 들어 18세에서 23세인 사람을 필터링해야 하는 경우 숫자 필터 탭에서 “사이” 필터를 적용할 수 있습니다.
필터를 적용하면 공식 바가 해당하는 M 쿼리로 업데이트됩니다. 또한 필터를 업데이트하기 위해 공식 바에서 코드를 수정할 수 있습니다. 또한 필터링 프로세스는 편집기의 “적용된 단계” 섹션에 적용된 단계로 표시되어야 합니다.
열 분할 기능은 구분 기호, 문자 수, 숫자 및 문자 유형에 따라 열을 분할하는 매우 강력한 도구입니다. 예를 들어 Excel 메일링 목록에 “주소, 우편번호”로 주소를 포함하는 열이 있는 경우 주소 열을 주소와 우편번호 열로 분할하려고 합니다.
“주소” 열의 모든 값에 대해 구분 기호가 “,”인 경우 구분 기호를 사용하여 열을 분할할 수 있습니다. 또는 우편번호 데이터에 일정한 문자 수가 있는 경우 문자 수에 따라 우편번호를 분할할 수도 있습니다.
열을 분할하려면 먼저 열 머리글을 클릭하여 전체 열을 선택합니다. 다음으로 홈 또는 변환 탭에서 “열 분할” 옵션을 클릭하고 구분 기호별로 선택합니다. 대화 상자에서 구분 기호를 지정하고 확인을 클릭하여 열을 분할합니다. 이제 “주소.1″과 “주소.2″라고 불리는 두 개의 열이 나타나며 이를 주소 및 우편번호로 다시 이름을 바꿀 수 있습니다.
데이터를 정리하고 서식을 맞추는 것은 시각적으로 매력적이고 일관된 데이터로 만들기 위해 데이터를 조작하는 것만큼 중요합니다. 이 목록에서 언급된 다른 명령어와 마찬가지로 서식 지정, 공백 제거 및 값 정리는 일반 Excel 시트에서는 다른 방식으로 작동하지만 Power Query에서는 이러한 필수 기능을 단일 메뉴 옵션인 “서식”으로 통합합니다. 변환 탭의 서식 옵션을 선택하면 소문자, 대문자 및 각 단어의 첫 글자를 대문자로 변환하는 기능이 가능합니다.
서식 옵션을 사용하려면 텍스트 값을 포함하는 열을 선택하고 서식 옵션으로 이동합니다. 소문자 또는 대문자 옵션을 클릭하면 전체 열이 해당 대소문자만 포함하도록 변환됩니다. 또한 각 단어의 첫 글자를 대문자로 만드는 기능은 Word의 제목 케이스와 유사하게 작동하여 각 단어의 첫 글자를 대문자로 만듭니다.
이러한 다소 기본적인 서식 기능 외에도 서식 기능은 데이터 트리밍 및 정리 기능을 제공합니다. 데이터에 셀 값의 시작 또는 끝에 문자 사이에 불필요한 공백이 포함된 경우 트리밍 함수를 사용하여 모든 이러한 공백을 자동으로 제거할 수 있습니다. 또한 클린 함수는 데이터에서 모든 인쇄할 수 없는 문자를 제거하여 인쇄할 수 있는 형식으로 만듭니다.
중복 및 원치 않는 행 제거는 데이터를 변환하는 동안 자주 수행해야 하는 일반 작업입니다. “중복 행 제거” 명령어는 각 값의 첫 번째 발생만 유지하고 나머지를 제거합니다. 또 다른 중요한 작업인 “빈 행 제거”는 데이터에 값이 없는 행을 제거하는 것을 의미합니다.
일반 Excel 워크시트에서 중복 행을 제거하고 빈 행을 제거하는 방법은 매우 다릅니다. Power Query는 다른 기준에 따라 데이터에서 행을 제거하고 모두 한 곳에 모아줍니다. 원치 않는 행을 제거하려면 중복을 제거할 열이나 열 그룹을 선택하십시오. 그런 다음 홈 탭의 “행 제거” 섹션으로 이동하여 드롭다운 화살표를 클릭하십시오. 이제 “중복 행 제거” 옵션을 선택하면 선택한 열에 각 행에 대해 고유한 값만 포함됩니다.
열에 중복 값을 포함하고 싶을 때는 어떻게 해야 할까요? Power Query 편집기에는 당신을 커버할 팁이 있습니다. “행 유지” 기능은 “행 제거” 기능과 정반대로 작동하며 “중복 유지” 옵션을 사용하여 모든 고유한 값을 제거할 수 있습니다.
데이터 분석에 관심이 있다면 Group By가 익숙할 것입니다. Group By는 기본 SQL 함수로 복잡하지만 유용한 기능을 다이얼로그 상자와 드롭다운을 사용하여 간단하고 상호작용적인 방식으로 제공합니다. Group By는 핵심 작업 중 하나를 기반으로 데이터를 변환하고 합계, 개수, 평균, 중앙값, 최소, 최대 및 모든 행에 대해 재평가합니다. 정의는 로켓 과학만큼 복잡하게 들리지만 Group By는 익힌 후에는 상대적으로 쉬운 명령어입니다. 조직의 다른 직원들의 이름, 나이, 국가 및 급여를 포함하는 데이터가 있는 경우 예를 들어 각 국가의 직원 수를 나타내는 기본 테이블에 국가 이름 및 직원 수를 포함해야 합니다. 이 테이블을 만들려면 홈 또는 변환 탭의 Group By 옵션을 클릭하고 Group By 대화 상자를 기다립니다.
이제 데이터를 그룹화할 열을 선택하십시오. 현재 문제에 대해 “국가” 열을 선택하고 “행의 수 세기” 작업을 선택하십시오. 이제 새 열을 “직원 수”로 이름을 바꾸고 확인을 클릭하십시오.
단일 Group By 열을 포함하는 이 기본 Group By 작업은 함수의 본질을 이해하는 데 매우 좋지만 Power Query는 여러 열을 함께 그룹화하고 더 복잡한 작업을 수행할 수 있는 고급 Group By도 제공합니다.
Power Query 편집기의 공식 바는 적용된 데이터 조작 단계에 해당하는 M 코드로 자동으로 업데이트됩니다. M 코드는 처음에는 혼란스러울 수 있지만 주의 깊게 살펴보면 구문을 쉽게 이해할 수 있습니다. 홈 탭의 고급 편집기 옵션은 데이터의 모든 적용된 단계에 대한 압축된 M 코드 편집기를 열어줍니다. 이 편집기에서 직접 M 코드를 작성하여 변환에 추가적인 단계를 추가할 수 있습니다. 그러나 시작 단계로 M 코드를 사용하여 전체 단계를 작성하면 특히 초보자로서 워크플로를 느리게 만들 수 있습니다. M 코드를 사용하는 가장 좋은 방법은 편집기 인터페이스를 사용하여 단계를 변경하지 않도록 코드를 수정하는 것입니다. 또한 유사한 M 코드를 복사하여 다른 단계에서 동일한 작업을 수행할 수 있습니다.
Power Query 편집기는 막대한 데이터 변환 기능을 갖춘 매우 강력한 도구입니다. 이러한 명령어는 좋은 시작을 제공할 수 있지만 테이블을 결합하고 다른 데이터 소스를 첨부하는 크로스 데이터 소스 조인을 허용하는 병합 기능으로 더 많은 작업을 수행할 수 있습니다.