Excel에서 파워 쿼리를 사용하는 방법: 전체 가이드

파워 쿼리는 필요에 따라 데이터를 가져오고, 정리하고, 변환할 수 있는 Excel의 강력한 비즈니스 인텔리전스 도구입니다. 데이터를 정리하는 데 몇 시간을 소비했거나(저희도 있음) 다양한 소스의 데이터를 병합하는 데 어려움을 겪은 적이 있다면(저희도 있음) 이 기능을 사용하면 삶이 더 쉬워질 것입니다.

Excel에서 파워 쿼리를 사용하는 방법을 배우는 가장 좋은 방법은 데이터 가져오기 및 파워 쿼리 편집기를 사용하여 결과 테이블을 변환하고 데이터를 결합하는 것과 같은 몇 가지 일반적인 작업을 수행하는 것입니다.

이 문서에서는 단계별 가이드와 함께 명확한 파워 쿼리 예제를 보여줌으로써 시작할 수 있습니다 . 파워 쿼리를 마스터하면 작업 수준을 한 단계 높일 수 있습니다.

준비 되었나요? 해보자.

목차

• 파워 쿼리에 액세스할 수 있는지 확인하는 방법
• 파워 쿼리로 데이터를 가져오는 방법
• 파워 쿼리 편집기를 사용하는 방법
  • 데이터 미리보기
  • 속성
• 여러 데이터 소스로 작업하는 방법
• 파워 쿼리로 데이터를 추가하는 방법
• 파워 쿼리로 데이터를 병합하는 방법
• 파워 쿼리로 데이터를 변환하는 5가지 방법
  • 1. 데이터 확장 및 압축
  • 2. 데이터 정렬
  • 3. 데이터 포맷
  • 4. 열 추가 및 제거
  • 5. 데이터 유형 변경
• 수식 및 M 언어 작업
  • 수식 바
  • M 코드 작성 및 수정
• M 언어 구문이란 무엇입니까?
• 데이터 연결을 새로 고치는 방법
• 데이터 모델과 파워 쿼리를 통합하는 방법
• 파워 쿼리 및 VBA로 자동화하는 방법
• 파워 쿼리 고급 기능
• 협업 및 공유
• 마무리하자

파워 쿼리에 액세스할 수 있는지 확인하는 방법

파워 쿼리를 사용하기 전에 해당 기능이 있는 Excel 버전이 있는지 확인해야 합니다.

버전별 파워 쿼리에 대한 간략한 개요는 다음과 같습니다.

• Excel 2010 및 Excel 2013: 다운로드하여 설치해야 하는 추가 기능으로 사용할 수 있습니다.

• Excel 2016: "가져오기 및 변환"으로 Excel에 통합되었습니다.

• Excel 2019: 향상된 성능 및 기능.

• Microsoft 365용 Excel 2021 및 Excel: 클라우드 데이터 원본과 통합.

• Mac용 Excel: Microsoft 365 버전 16.69 이상에서만 사용할 수 있습니다.

도구가 Excel 통합 문서(2016 이상)에 통합되었는지 확인하려면 데이터 탭으로 이동하여 이 그림에서 "데이터 가져오기 및 변환" 섹션을 찾으십시오.

Windows에 Excel 2010 또는 2013이 있는 경우 파워 쿼리 추가 기능을 다운로드 , 설치 및 활성화할 수 있습니다.

파워 쿼리로 데이터를 가져오는 방법

파워 쿼리 도구를 사용하면 다음과 같은 다양한 데이터 원본을 가져오고 연결할 수 있습니다.

• CSV, XML 및 JSON 형식을 포함한 파일

• 웹 URL 또는 API 끝점

• Access, SQL Server, Oracle 및 최신 데이터베이스를 포함한 데이터베이스

• Azure 서비스, SharePoint 파일 및 OData 피드

시작하기에 가장 쉬운 데이터 원본은 여러 줄의 데이터가 포함된 간단한 CSV 파일입니다. 텍스트 파일을 만들고 다음 4개의 성과 이름을 쉼표로 구분하여 추가합니다.

• 조, 블로그

• 앤, 라이언

• 제임스, 스튜어트

• 메리, 브라운

.CSV 확장자로 파일을 저장해야 합니다. 파일은 다음과 같습니다.

파워 쿼리를 사용하여 데이터를 로드하려면 다음 단계를 따르세요.

1. 데이터 탭으로 이동합니다.

2. 데이터 가져오기 및 변환 탭에서 "파일 가져오기" 버튼을 클릭합니다.

3. 샘플 파일을 선택합니다.

도구가 데이터를 미리 보기 창으로 가져올 때까지 몇 초 정도 기다려야 할 수 있습니다. 파워 쿼리는 작은 가져오기에도 상당히 많은 컴퓨터 리소스를 사용하므로 인내심을 가지십시오.

팁: 성능을 높이려면 다른 응용 프로그램을 종료하십시오.

파워 쿼리는 데이터 미리 보기와 함께 "텍스트/CSV 가져오기" 창을 표시합니다.

데이터가 올바르면 계속해서 데이터를 로드하거나 변환할 수 있습니다.

• 로드 버튼은 데이터를 Excel로 직접 로드합니다.

• 데이터 변환 버튼을 사용하면 데이터를 로드하기 전에 수정할 수 있습니다.

샘플 파일에 헤더가 없다는 것을 눈치채셨나요? Excel에 바로 로드되는 경우 헤더는 "열 1"과 "열 2"가 됩니다.

대신 데이터를 파워 쿼리 편집기로 가져와 일부 변경할 수 있습니다. 그렇게 하려면 "데이터 변환" 버튼을 클릭하십시오.

이제 Power Query 편집기가 열리고 다음 섹션으로 이동합니다.

파워 쿼리 편집기를 사용하는 방법

이 섹션에서는 Power Query 편집기 작업의 몇 가지 필수 측면을 살펴보겠습니다.

데이터 미리보기

데이터 미리보기 영역에서 가져온 데이터를 보고 상호 작용할 수 있습니다. 행을 필터링하고, 열 데이터 유형을 수정하고, 필요에 맞게 데이터를 재구성할 수 있습니다.

완료되면 Excel에서 변환된 데이터를 워크시트로 로드합니다.

샘플 파일을 사용하여 이 창에서 직접 열 이름을 편집할 수 있습니다.

이렇게 하려면:

1. 열 헤더를 마우스 오른쪽 버튼으로 클릭합니다.

2. 드롭다운 메뉴에서 "이름 바꾸기"를 클릭합니다.

3. 머리글 상자에 원하는 이름을 입력합니다.

변경하면 미리보기 창 오른쪽에 있는 "적용된 단계" 섹션이 업데이트되어 각 변환을 반영하므로 필요에 따라 데이터 처리 단계를 추적하고 수정할 수 있습니다.

속성

쿼리에 대한 속성을 설정하는 것은 작업을 구성하고 추적하는 데 필수적입니다.

쿼리 설정 창은 파워 쿼리 창의 오른쪽에 있습니다.

1. "모든 속성" 링크를 클릭합니다.

2. 설명이 포함되도록 검색어 이름을 수정합니다.

3. 자세한 정보를 제공하려면 설명을 추가하십시오.

지난주의 쿼리가 수행하도록 설정한 내용을 잊어버리기 쉽습니다. 이 경우 쿼리 이름이 변경되고 쿼리의 목적을 설명하는 설명이 추가되었습니다.

사용 가능한 옵션에 대해 자세히 알아보려면 이 Power Query 편집기 개요를 확인하세요 .

여러 데이터 소스로 작업하는 방법

Power Query 편집기는 여러 원본의 데이터를 결합하는 두 가지 기본 방법인 추가 및 병합을 제공합니다.

추가는 테이블을 수직으로 조인하여 서로의 위에 쌓입니다. 병합을 사용하면 공통 열을 기반으로 테이블을 조인할 수 있습니다.

이것이 어떻게 작동하는지 보려면 첫 번째와 동일한 구조로 두 번째 작은 CSV 파일을 만드십시오. 이번에는 두 개의 이름(이름과 성)을 파일에 입력합니다.

• 조, 블로그

• 로라, 케인

이러한 이름 중 하나는 이전 파일에서 반복됩니다.

이제 다음 단계에 따라 데이터를 Microsoft Excel 통합 문서로 가져옵니다.

1. 파워 쿼리 편집기 창을 닫고 변경 내용을 저장합니다.

2. 새 워크시트를 추가합니다.

3. 이전 섹션의 단계에 따라 두 번째 파일을 가져옵니다.

4. 변환 버튼을 클릭합니다.

5. 열 머리글의 이름을 "First Name" 및 "Last Name"으로 바꿉니다.

Excel 워크시트의 오른쪽 창에는 현재 쿼리 및 연결이 표시됩니다. 이제 창에 두 개의 쿼리가 표시됩니다.

파워 쿼리로 데이터를 추가하는 방법

이 섹션에서는 첫 번째에 두 번째 샘플 CSV 파일을 추가합니다.

데이터를 추가하려면 다음 단계를 따르세요.

1. 쿼리 및 연결 창에서 첫 번째 테이블(고객 이름)을 마우스 오른쪽 단추로 클릭합니다.

2. "편집"을 선택하여 파워 쿼리 편집기에서 테이블을 엽니다.

3. 결합 섹션에서 쿼리 추가를 클릭합니다.

4. 추가할 테이블의 드롭다운 목록에서 두 번째 테이블을 선택합니다.

결과는 첫 번째 테이블에 두 개의 추가 행이 됩니다.

하나의 행이 반복된다는 점에 유의하십시오. 이것이 추가 작업이 작동하는 방식입니다. 데이터가 이미 있는지 여부에 관계없이 한 테이블의 데이터를 다른 테이블에 추가합니다.

다음 단계에 따라 Power Query 편집기에서 중복 데이터를 제거 할 수 있습니다 .

1. "이름" 열 머리글을 클릭합니다.

2. "Ctrl" 키(또는 Mac의 경우 "Cmd" 키)를 누릅니다.

3. 두 열 머리글을 모두 클릭합니다.

4. 파워 쿼리 편집기 리본의 홈 탭에서 "행 제거"를 클릭합니다.

5. 드롭다운 메뉴에서 "중복 제거"를 선택합니다.

파워 쿼리로 데이터를 병합하는 방법

데이터를 병합할 때 쿼리 간에 일치하는 열을 선택해야 합니다.

하나는 고객 전화 번호를 포함하고 다른 하나는 주소를 포함하는 두 개의 쉼표로 구분된 파일이 있다고 가정합니다. 두 파일은 다음과 같습니다.

이전 섹션의 지침에 따라 각 파일을 자체 파워 쿼리에 로드합니다. 그런 다음 테이블 병합을 연습할 수 있습니다.

샘플 데이터의 경우 일치하는 열로 두 열(이름 및 성)을 모두 선택해야 합니다. Ctrl 키를 누른 상태 에서 여러 열을 선택합니다.

다음과 같이하세요:

1. 쿼리 및 연결 창에서 첫 번째 테이블을 마우스 오른쪽 단추로 클릭합니다.

2. "편집"을 선택하여 파워 쿼리 편집기에서 테이블을 엽니다.

3. "결합" 그룹에서 "쿼리 병합"을 선택합니다.

4. 테이블 드롭다운 목록에서 두 번째 테이블을 선택합니다.

5. 첫 번째 테이블의 첫 번째 열을 클릭합니다.

6. Ctrl 키를 누른 상태에서 첫 번째 테이블의 두 번째 열을 클릭합니다.

7. Ctrl 키를 누른 상태에서 두 번째 테이블의 두 열을 모두 클릭합니다.

8. 기본 조인 연산자를 "Left Outer"로 유지합니다.

9. 변경 사항을 적용하려면 "확인"을 클릭하십시오.

"Left Outer" 조인 연산자는 중복 데이터를 제거합니다.

파워 쿼리 편집기에서 두 번째 쿼리에서 병합된 데이터를 포함하는 "테이블" 또는 "중첩 테이블"이라는 새 열을 볼 수 있습니다.

1. 이 열의 헤더에 있는 확장 아이콘(화살표 2개)을 클릭합니다.

2. "원본 열을 접두사로 사용" 상자를 선택 취소합니다(긴 열 이름을 방지함).

3. "확인"을 클릭합니다.

4. "닫기 및 로드"를 클릭합니다.

병합된 테이블에는 전화 및 주소 세부 정보가 병합된 4개의 열이 있습니다. 아직 약간의 정리 작업이 남아 있을 수 있습니다.

예를 들어 아래 그림에서 테이블 이름(Merge1)과 주소 열을 편집할 수 있습니다.

파워 쿼리로 데이터를 변환하는 5가지 방법

파워 쿼리를 사용하면 Excel 파일의 데이터를 쉽게 변환할 수 있으므로 데이터를 효과적으로 정리, 구성 및 조작할 수 있습니다.

데이터를 변환하는 가장 일반적인 방법은 다음과 같습니다.

• 데이터 확장 및 압축

• 데이터 정렬

• 데이터 포맷

• 열 추가 및 제거

• 데이터 유형 변경

1. 데이터 확장 및 압축

데이터 확장 및 압축은 데이터 세트를 보다 쉽게 ​​관리할 수 있도록 만드는 데 중요합니다. 파워 쿼리를 사용하면 보다 체계적으로 보기 위해 테이블을 확장하거나 축소할 수 있습니다.

이전 섹션에서는 관련 테이블의 데이터를 포함하여 확장하는 방법을 보여줍니다.

합계, 개수 또는 백분율과 같은 선택한 집계를 기반으로 요약된 데이터만 포함하도록 압축할 수도 있습니다.

2. 데이터 정렬

데이터를 정렬하려면 값을 오름차순 또는 내림차순으로 빠르게 순서를 변경할 수 있습니다.

열 이름 옆의 아래쪽 화살표를 클릭하고 "오름차순 정렬" 또는 "내림차순 정렬"을 선택합니다.

3. 데이터 포맷

Power Query 편집기의 변환 탭은 많은 옵션을 제공합니다.

예를 들어 형식 드롭다운 메뉴를 사용하면 열 또는 전체 테이블의 모든 데이터를 대문자 또는 소문자로 변경할 수 있습니다.

4. 열 추가 및 제거

파워 쿼리는 열 추가, 삭제 및 수정 프로세스를 간소화합니다.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

예를 들어 더 쉬운 계산 및 비교를 위해 소수점을 백분율로 변환하거나 텍스트 기반 백분율을 숫자로 변환해야 할 수 있습니다.

수식 및 M 언어 작업

Excel에서 파워 쿼리를 사용할 때 고급 수식 만들기, 데이터 조작 및 강력한 쿼리 생성의 핵심인 M 언어를 만나게 됩니다. 이 섹션에서는 다음에 대해 알아봅니다.

• 공식 막대

• M 코드

• M 언어 구문의 필수 요소

수식 바

Excel 파워 쿼리 편집기의 수식 입력줄은 수식과 M 코드를 입력하는 영역입니다.

이를 사용하려면 쿼리 및 연결 창에서 쿼리를 선택하여 파워 쿼리 편집기로 이동합니다. 수식 입력줄은 데이터 미리 보기 창 위에 있습니다.

M 코드 작성 및 수정

Excel에서 다양하고 강력한 쿼리를 만들려면 M 코드로 알려진 파워 쿼리 언어를 작성하고 수정해야 합니다. 다음은 시작하는 방법에 대한 몇 가지 팁입니다.

• 적용된 단계 창을 사용하여 단계별 코드를 작성하면 쿼리 논리를 쉽게 추적하고 수정할 수 있습니다.

• M 코드를 Excel 테이블, 피벗 테이블, Azure 및 텍스트 파일과 같은 외부 데이터 소스와 결합하여 동적이고 자동화된 보고서를 생성합니다.

• 쿼리 옵션을 조정하여 데이터 로드 환경 설정을 구체화하고 성능을 개선하십시오.

M 코드에 익숙해지면 많은 프로세스를 자동화하고 최적화하여 Excel의 데이터 분석을 보다 효율적이고 강력하게 만들 수 있음을 알게 될 것입니다.

쿼리 뒤의 코드를 보려면 다음 단계를 따르세요.

1. 파워 쿼리 편집기에서 쿼리를 엽니다.

2. 홈 탭에서 "고급 편집기"를 클릭합니다.

다음은 샘플 병합 테이블의 코드입니다(실제로는 다르게 보일 수 있음).

M 언어 구문이란 무엇입니까?

M 언어 구문을 이해하는 것은 효과적인 파워 쿼리 수식을 만드는 데 중요합니다. 다음 사항에 유의하십시오.

• M 언어는 대소문자를 구분하므로 대문자 사용에 주의하세요.

• 열, 레코드 및 목록을 참조하려면 대괄호 []를 사용하십시오.

• 쿼리에서 이전 단계를 참조하려면 해당 변수 이름(예: #"Step Name")을 사용하십시오.

• "in" 문을 사용하여 쿼리 수식 단계를 출력합니다.

파워 쿼리 언어 흐름 에 대한 경험을 쌓으면서 데이터를 변환하고 분석하는 새로운 방법을 발견하여 Excel 통합 문서를 훨씬 더 귀중한 자산으로 만들 수 있습니다.

아래 비디오를 확인하십시오. 수세미를 꺼내 지저분한 데이터를 정리할 때입니다.

데이터 연결을 새로 고치는 방법

파워 쿼리의 주요 기능 중 하나는 파워 쿼리 연결을 쉽게 새로 고칠 수 있는 기능입니다.

데이터 원본이 업데이트되었고 통합 문서에 최신 정보가 필요한 경우 쿼리 및 연결 창에서 데이터 연결을 새로 고칠 수 있습니다.

워크시트 오른쪽에 창이 표시되지 않으면 데이터 테이블로 이동하여 "쿼리 및 연결"을 클릭하여 열 수 있습니다.

쿼리 및 연결 창이 열리면 모든 쿼리가 나열됩니다. 다음과 같이하세요:

1. 새로 고칠 쿼리를 마우스 오른쪽 버튼으로 클릭합니다.

2. 상황에 맞는 메뉴에서 새로 고침을 선택합니다.

이렇게 하면 연결된 데이터 원본의 최신 데이터로 통합 문서가 업데이트됩니다.

데이터 모델과 파워 쿼리를 통합하는 방법

Excel의 데이터 모델은 데이터 관계를 기반으로 피벗 테이블과 같은 비즈니스 인텔리전스 도구를 만들 수 있는 강력한 기능입니다.

데이터 모델은 기본적으로 가져온 데이터와 테이블 간의 관계를 보유하는 숨겨진 데이터베이스입니다.

현재 버전의 Excel에 기본 제공되는 Power Pivot 기능의 일부입니다. 이전 버전에서 액세스하려면 Power Pivot을 추가 기능으로 설치해야 할 수 있습니다.

다음 단계를 통해 파워 쿼리 데이터 연결을 데이터 모델과 통합할 수 있습니다.

1. 이전 섹션에서 설명한 대로 파워 쿼리를 사용하여 데이터를 로드합니다.

2. 통합 문서에 직접 로드하는 대신 로드 메뉴에서 로드 대상을 선택합니다.

3. 데이터 가져오기 대화 상자에서 이 데이터를 데이터 모델에 추가 확인란을 선택합니다.

파워 쿼리 및 VBA로 자동화하는 방법

VBA(Visual Basic for Applications) 통합을 통해 사용자 지정 데이터 연결을 만들고 파워 쿼리에서 데이터 변환 단계를 자동화할 수 있습니다.

기존 VBA 기술을 활용하여 Microsoft Access, SharePoint 및 Analysis Services와 같은 플랫폼의 데이터를 통합하여 고급 데이터 모델을 구축할 수 있습니다.

파워 쿼리는 VBA를 기본적으로 지원하지 않지만 VBA 코드를 사용하여 파워 쿼리 쿼리를 간접적으로 열고, 새로 고치고, 조작할 수 있습니다.

이를 통해 강력하고 자동화된 데이터 워크플로를 생성하고 데이터 분석 프로세스를 간소화할 수 있습니다.

다음은 현재 통합 문서에서 파워 쿼리를 새로 고치는 몇 가지 샘플 코드입니다.

:

Dim wb를 통합 문서로, pq를 객체로

설정 wb = ThisWorkbook

pq = wb.Queries("고객 이름")으로 설정

 pq.새로 고침

다음은 Excel VBA 매크로 내의 코드입니다.

파워 쿼리 고급 기능

Excel의 파워 쿼리는 데이터 분석 프로세스를 단순화하고 향상시키는 다양한 고급 기능을 제공합니다. 이러한 기능을 효과적으로 사용하는 방법을 배우면 데이터 분석 기술을 완전히 새로운 수준으로 끌어올릴 수 있습니다.

기본 제공 데이터 분석 도구에는 다음이 포함됩니다.

• 데이터 변환에 복잡한 논리를 적용하기 위한 조건부 열입니다.

• 데이터를 의미 있는 방식으로 요약하는 그룹화 및 집계 기능.

• 더 쉬운 분석을 위해 데이터를 재구성하는 Pivot 및 Unpivot 작업.

Power Query 편집기의 변환 탭에서 이러한 많은 기능을 찾을 수 있습니다.

또한 고급 데이터 분석 및 시각화 옵션을 위해 파워 쿼리를 LuckyTemplates와 같은 비즈니스 인텔리전스 도구에 연결할 수 있습니다 .

협업 및 공유

팀 구성원과 데이터 연결, 쿼리 및 변환을 공유하는 것은 효과적인 공동 작업에 필수적입니다.

통합 문서를 네트워크 드라이브, SharePoint 또는 기타 공유 저장소 위치 에 저장하여 쿼리와 함께 통합 문서를 공유할 수 있습니다 .

다음 단계에 따라 단일 쿼리 뒤의 M 코드를 내보낼 수도 있습니다.

1. 파워 쿼리 편집기에서 쿼리를 편집합니다.

2. 홈 탭에서 "고급 편집기"를 클릭합니다.

3. 고급 편집기 창에서 전체 M 코드를 복사합니다.

4. 텍스트 편집기(예: 메모장)를 열고 복사한 M 코드를 텍스트 편집기에 붙여넣습니다.

5. 텍스트 파일을 저장하고 동료에게 보냅니다.

마무리하자

다양한 기능과 사용자 친화적인 인터페이스를 갖춘 파워 쿼리는 외부 데이터로 효과적으로 작업하려는 Excel 사용자에게 필수적인 도구입니다.

이 유용한 기능은 보고서 작성, 데이터 정리 및 데이터 테이블에 대한 복잡한 분석 수행 프로세스를 간소화하는 데 도움이 됩니다. 사용

파워 쿼리는 Excel 파일로 작업할 때 궁극적으로 시간을 절약하고 효율성을 높입니다.