Excel에서 파워 쿼리를 사용하는 방법

Microsoft Excel은 데이터 조작을 위한 여러 도구와 기능을 제공하며 파워 쿼리는 최고의 도구 중 하나입니다. 이 비즈니스 분석 도구를 사용하면 다양한 소스에서 데이터를 가져와 필요에 따라 Excel에서 쉽게 변환하고 조작할 수 있습니다. 기본적으로 반복적인 작업을 없애고 노력을 줄이고 시간을 절약하는 데 도움이 됩니다.

파워 쿼리의 가장 큰 장점은 이를 사용하는 데 코딩 전문 지식이나 지식이 필요하지 않다는 것입니다. 이를 사용하여 Microsoft Excel에서 데이터를 조작하는 방법을 살펴보겠습니다.

파워 쿼리에 액세스

파워 쿼리는 Excel 2010부터 모든 버전의 Microsoft Excel에서 사용할 수 있습니다. Excel 2016부터는 응용 프로그램에 직접 적용되었습니다.

Excel 2016 이상에서

• 새 Excel 워크시트를 실행하고 메뉴 표시줄에서 ‘데이터’ 탭을 클릭하세요.

• ‘데이터’ 탭의 옵션에서 메뉴 표시줄 아래 왼쪽 상단에 있는 ‘데이터 가져오기’ 옵션을 클릭하세요.

• 여기에는 데이터 가져오기 및 변환을 위한 모든 파워 쿼리 도구와 옵션이 포함되어 있습니다.

엑셀 2013과 2010에서

Excel 버전 2013 및 2010의 경우 파워 쿼리는 Microsoft 웹 사이트에서 다운로드할 수 있는 무료 추가 기능으로 제공됩니다.

• 파워 쿼리 다운로드 페이지 로 이동하여 ‘다운로드’ 버튼을 클릭하여 도구 다운로드를 시작합니다.

• ‘다운로드’ 버튼을 클릭하면 시스템에 따라 적절한 옵션을 선택할 수 있는 몇 가지 옵션이 표시됩니다.

• 올바른 옵션을 선택한 후 ‘다운로드’ 버튼을 클릭하여 도구를 다운로드하세요.

파워 쿼리 도구 사용

Excel 워크시트를 연 상태에서 ‘데이터’ 탭과 ‘데이터 가져오기’ 옵션에서 파워 쿼리 도구에 액세스할 수 있습니다.

데이터 가져오기

• ‘데이터 가져오기’ 옵션을 클릭하면 데이터를 가져올 수 있는 다양한 소스가 표시됩니다. 여기에는 Excel 통합 문서, 텍스트 또는 CSV 파일, XML 및 JSON 파일이 포함됩니다 . 이 외에도 SQL Server, Microsoft Access 등의 온라인 데이터베이스에서 데이터를 가져올 수 있습니다. 데이터를 가져올 수 있는 다른 소스로는 Microsoft Azure와 Salesforce 및 Facebook과 같은 온라인 서비스가 있습니다.
• 데이터를 가져오려면 ‘파일에서’, ‘데이터베이스에서’, ‘Azure에서’, ‘온라인 서비스에서’ 및 ‘기타 소스에서’와 같은 옵션 중 하나를 클릭합니다.

• 데이터를 가져올 때 Excel은 로드될 데이터의 미리 보기를 표시하는 팝업을 표시합니다. 하단의 ‘Load’ 버튼을 클릭하면 데이터 가져오기가 완료됩니다.

• 이제 Excel 워크시트에서 데이터를 확인하고 다양한 변환을 적용할 수 있습니다.

파워 쿼리 편집기의 구성 요소

• 가져온 데이터를 필요에 따라 변환하려면 파워 쿼리 편집기가 필요합니다. ‘데이터 가져오기’ 버튼을 클릭한 후 ‘파워 쿼리 편집기 시작’을 클릭하세요.

• 그러면 6개의 주요 구성 요소로 구성된 ‘파워 쿼리 편집기’가 시작됩니다. 상단에는 다양한 탭 아래에 다양한 명령이 포함된 ‘쿼리 편집기 리본’이 있습니다.

• 왼쪽 ‘쿼리 편집기 리본’ 아래에는 통합 문서의 모든 쿼리를 보여주는 ‘쿼리 목록’이 있습니다. 중앙에는 데이터에 적용된 모든 변환을 보여주는 ‘데이터 미리보기’ 섹션도 있습니다.

• ‘Formula Bar’를 사용하면 변환 단계의 M 코드를 편집할 수 있습니다. 모든 변환은 ‘적용된 단계’ 영역에 기록되고 단계로 표시됩니다.

• ‘속성’ 섹션에서는 이름이 포함된 쿼리를 제공할 수 있습니다.

변환 적용

파워 쿼리 편집기에서 가져온 데이터에 다양한 변환을 적용할 수 있습니다. 여기에는 텍스트 형성, 트리밍, 전치 등이 포함됩니다.

텍스트 변환

텍스트를 편집기로 가져온 후 대문자 또는 소문자로 변환할 수 있습니다.

• 파워 쿼리 편집기에서 상단의 ‘변환’ 탭으로 이동하면 ‘전치’, ‘값 바꾸기’ 등과 같은 여러 옵션이 표시됩니다.

• ‘형식’ 옵션은 ‘열 분할’ 옵션 옆 중앙에 있습니다. 사용 가능한 서식 옵션을 보려면 클릭하세요.

• 선택한 열의 텍스트를 소문자 또는 대문자로 변환하려면 ‘소문자’ 또는 ‘대문자’와 같은 옵션을 클릭하세요. 마찬가지로, 다른 옵션을 클릭하면 그에 따라 텍스트가 변환됩니다.

• ‘형식’ 옵션에서는 ‘트림’ 옵션을 사용하여 모든 공백을 제거할 수도 있습니다. ‘Trim’ 버튼을 클릭하면 텍스트에서 추가 공백이 모두 제거됩니다.

열 분할

텍스트 변환 외에도 파워 쿼리 편집기를 사용하면 다양한 방법으로 열을 분할할 수 있습니다.

• 데이터를 파워 쿼리 편집기로 가져온 후 열 제목을 클릭하여 전체 열을 선택합니다.

• 그런 다음 ‘서식’ 버튼 왼쪽에 있는 ‘열 분할’ 버튼을 클릭하세요. 그러면 선택한 열을 다양한 방법으로 분할할 수 있는 옵션 목록이 제공됩니다.

• 열을 구분 기호로 분할하려면 해당 옵션을 클릭합니다. 그러면 쉼표, 콜론, 등호 등과 같은 구분 기호를 선택할 수 있는 구분 기호로 분할 팝업이 표시됩니다.

• ‘확인’ 버튼을 클릭하면 원하는 대로 컬럼이 분할되며, 컬럼이 분할된 것을 확인할 수 있습니다.

데이터 전치

‘전치’ 옵션을 사용하면 사용자는 데이터를 행에서 열로 또는 그 반대로 전환할 수 있습니다. 이렇게 하려면 먼저 앞서 설명한 대로 데이터를 파워 쿼리 편집기로 가져옵니다.

• 데이터를 로드한 후 상단의 ‘변환’ 탭으로 이동하면 ‘전치’ 옵션이 있습니다.

• 행을 열로 변환하려면 ‘전치’ 옵션을 클릭하세요.

쿼리 결합

파워 쿼리를 사용하면 ‘병합’ 및 ‘추가’ 옵션을 사용하여 여러 데이터 세트를 쉽게 결합할 수 있습니다.

병합 옵션 사용

병합 작업을 사용하면 기존 쿼리를 결합하여 새 쿼리를 만들 수 있습니다.

• 먼저 파일, 데이터베이스 또는 기타 소스에서 Excel 워크시트로 데이터를 가져옵니다. 이 경우 데이터를 파워 쿼리 편집기에 로드할 필요는 없지만 여러 데이터 세트를 가져와야 합니다.

• 데이터 가져오기 옵션 아래에 ‘쿼리 결합’이라는 또 다른 옵션이 표시됩니다. 이 옵션에 커서를 놓으면 추가와 병합이라는 두 가지 옵션을 사용할 수 있습니다.

• ‘병합’ 버튼을 클릭하면 병합해야 하는 데이터세트를 선택할 수 있는 새로운 팝업이 표시됩니다.

• 데이터세트를 선택하면 미리보기가 표시됩니다. 왼쪽 하단에서 ‘확인’ 버튼을 클릭하기 전에 데이터세트를 병합할 방법을 선택할 수 있습니다.

추가 옵션 사용

‘추가’ 옵션을 사용하면 이전 쿼리의 행을 결합하여 새 테이블을 만들 수 있습니다.

• 위와 동일한 절차에 따라 Excel 워크시트에 데이터세트를 추가한 다음 ‘쿼리 결합’ 섹션의 ‘추가’ 옵션으로 이동합니다.

• 나타나는 팝업에서 데이터를 결합해야 하는 테이블을 선택하고 ‘확인’ 버튼을 클릭하세요. 사용자는 두 테이블 또는 세 개 이상의 테이블의 데이터를 결합할 수 있습니다.

• 결합된 데이터는 파워 쿼리 편집기 창에 표시되며, 여기에서 왼쪽 상단에 있는 ‘닫기 및 로드’ 버튼을 사용하여 워크시트로 가져올 수 있습니다.

워크시트에 데이터 로드

파워 쿼리 편집기에서 모든 작업이 완료되면 데이터를 Excel 워크시트에 로드해야 합니다.

• 변환된 데이터를 Excel 워크시트(예: 피벗 차트, 피벗 테이블, 테이블 또는 쿼리 연결)에 로드하는 방법에는 여러 가지가 있습니다. 왼쪽 상단의 ‘닫기 및 로드’ 옵션을 클릭하면 ‘닫고 로드’와 ‘닫고 로드’의 두 가지 옵션이 표시됩니다.

• 두 번째 옵션을 클릭하면 데이터를 워크시트에 로드하기 위한 다양한 옵션이 표시됩니다.

• Excel에서는 기존 워크시트의 셀이나 자동으로 생성되는 새 시트 등 위치를 선택할 수 있습니다. ‘이 데이터를 데이터 모델에 추가’ 옵션도 있습니다.

공식 및 함수 사용

파워 쿼리를 사용하면 Excel 워크시트와 유사한 수식 및 함수를 사용할 수도 있습니다. 이를 위해서는 수식과 함수를 추가할 수 있는 맞춤 열을 추가해야 합니다.

• ‘데이터 가져오기’ 탭에서 파워 쿼리 편집기를 실행하고 상단의 ‘열 추가’ 탭으로 이동합니다.

• 왼쪽에는 검색어가 표시됩니다. 하나를 클릭하여 선택하면 ‘사용자 정의 열’이 활성화됩니다. ‘맞춤 열’ 옵션을 클릭하여 새 열을 만듭니다.

• 사용자 정의 열을 생성하는 대화 상자에서 열 이름을 입력합니다.

• ‘사용자 정의 열 수식’ 섹션에서 열 생성을 위한 수식을 추가합니다. 예를 들어 다음과 같은 수식을 사용합니다 [First Name]&""&[Last Name]. 파워 쿼리 편집기는 수식에 오류가 있는지 확인합니다.

• 오류가 없으면 ‘확인’ 버튼을 클릭하면 편집자가 컬럼을 생성합니다.

• 기능을 사용하려면 ‘사용자 정의 열’ 팝업이 나타날 때까지 단계를 반복하세요. ‘사용자 정의 열 수식’ 섹션에서 Text.Upper([Full Name])모든 이름을 대문자로 만드는 함수(예: )를 추가합니다.

• 컬럼 추가를 완료하려면 ‘확인’ 버튼을 클릭하여 이름이 대문자인 컬럼을 생성하세요.

이것이 파워 쿼리를 시작하기 위해 알아야 할 전부입니다. 이 도구를 사용하면 필요에 따라 Microsoft Excel에서 데이터를 매우 쉽게 변환할 수 있으므로 최소한의 노력으로 분석하고 결론을 도출할 수 있습니다. 다양한 데이터 세트를 결합하고, 형식을 변경하고, 다른 작업을 수행하는 데 사용할 수 있습니다. 그리고 편집기에서 Excel 함수와 수식을 사용할 수도 있으므로 더욱 유용합니다.