파워 쿼리(Power Query)란? 데이터 분석의 강력한 도구

□ 파워 쿼리(Power Query)

파워 쿼리(Power Query)는 다양한 데이터 소스에서 데이터를 가져와 원하는 형태로 변환하고 분석하기 위한 강력한 도구입니다. 마치 레고 블록을 조립하듯이, 데이터를 자유롭게 가공하고 결합하여 의미 있는 정보를 얻을 수 있습니다.

▷ 파워 쿼리(Power Query) 맛보기

□ 파워 쿼리의 주요 기능

• 다양한 데이터 소스 연결: 엑셀 파일, CSV 파일, 데이터베이스, 웹 페이지 등 다양한 형태의 데이터를 쉽게 연결할 수 있습니다.
• 데이터 변환: 연결된 데이터를 원하는 형태로 변환할 수 있습니다. 불필요한 열 삭제, 데이터 형식 변경, 오류 값 수정, 행 병합 등 다양한 변환 작업이 가능합니다.
• 데이터 결합: 여러 개의 데이터 소스를 하나로 결합하여 통합된 데이터를 만들 수 있습니다.
• 데이터 모델링: 데이터를 분석하기 위한 모델을 생성하고 관리할 수 있습니다.
• 고급 기능: M 언어를 사용하여 더욱 복잡한 데이터 처리 작업을 수행할 수 있습니다.

▷ 다양한 데이터 소스 연결

비즈니스 환경에서 데이터가 흩어져 저장되는 현실을 반영하여 다양한 데이터 소스를 연결하는 기능을 제공합니다기업은 일반적으로 다양한 시스템(ERP, CRM, 마케팅 자동화 등)을 사용하며, 각 시스템마다 데이터가 분리되어 저장됩니다. 이러한 데이터를 하나로 통합하여 분석해야 전체적인 그림을 파악하고 의미 있는 인사이트를 얻을 수 있습니다.

인사이트는 단순한 정보를 넘어, 데이터나 현상을 분석하여 얻은 심층적인 이해와 통찰력을 의미합니다. 
즉, 겉으로 드러나는 현상뿐만 아니라 그 이면에 숨겨진 의미와 원인을 파악하여 새로운 관점이나 문제 해결 방안을 
제시하는 것을 의미합니다.

1.인사이트의 특징
 1)새로운 시각: 기존에 알려지지 않았던 사실이나 관계를 발견합니다.
 2)심층적인 이해: 현상의 표면적인 의미를 넘어 근본적인 원인을 파악합니다.
 3)실용적인 가치: 문제 해결이나 의사 결정에 도움이 되는 정보를 제공합니다.
 4)창의적인 아이디어: 새로운 아이디어나 비즈니스 기회를 발굴합니다.

2.인사이트를 얻는 방법
 1)데이터 분석: 대량의 데이터를 수집하고 분석하여 패턴과 관계를 찾아냅니다.
 2)전문가 인터뷰: 관련 분야 전문가의 의견을 수렴하여 심층적인 이해를 얻습니다.
 3)시장 조사: 고객의 니즈와 트렌드를 파악하여 새로운 시장 기회를 발굴합니다.
 4)브레인스토밍: 다양한 사람들의 아이디어를 모아 창의적인 해결책을 찾습니다.

데이터는 엑셀 파일, CSV 파일, 데이터베이스, 웹 페이지 등 다양한 형식으로 존재합니다. 파워 쿼리는 이러한 다양한 형식의 데이터를 통합하여 분석할 수 있는 유연성을 제공합니다.

파워 쿼리를 통해 데이터를 정제하고 변환하여 데이터 품질을 향상시킬 수 있습니다. 오류 데이터를 제거하고, 데이터 형식을 일관되게 변경하며, 필요한 계산을 수행할 수 있습니다.

1. Text 파일 가져오기
2. Excel 파일 가져오기
3. 데이터베이스에서 가져오기
4. WEB에서 가져오기

1. Text 파일들에서 가져오기

 

예시:

엑셀 파워 쿼리로 txt 파일 취합하기[엑셀강사_김경자]

2. Excel 파일들에서 가져오기

엑셀 파일을 파워 쿼리로 만들면 데이터 변환, 정제, 통합 등 다양한 작업을 직관적으로 수행할 수 있습니다.

예시: 파워 쿼리를 활용한 데이터 정제

• 문제: 엑셀 파일의 날짜 데이터가 다양한 형식으로 입력되어 있습니다.
• 해결: 파워 쿼리의 변형 기능을 사용하여 모든 날짜 데이터를 일관된 형식으로 변경합니다.

 

예시: 여러 엑셀 파일 데이터 합치기

• 문제: 
  매달 다른 엑셀 파일로 매출 데이터가 저장되어 있습니다. 모든 데이터를 하나의 파일로 합쳐 분석하고 싶습니다.
• 해결:
  데이터 가져오기 > 파일에서 > 폴더에서를 선택하여 매출 데이터가 저장된 폴더를 지정합니다.
  각 파일에서 동일한 구조의 테이블을 선택합니다.
  병합 또는 합치기 기능을 사용하여 모든 파일의 데이터를 하나의 테이블로 합칩니다.

예시 : 조건에 맞는 데이터 추출

• 문제: 특정 지역의 매출 데이터만 추출하고 싶습니다.
• 해결:
  필터 기능을 사용하여 "지역" 열에서 원하는 지역을 선택합니다.
  추가 열을 사용하여 특정 조건을 만족하는 데이터에 대한 새로운 열을 생성합니다. 
  (예: 매출액이 100만원 이상인 데이터에 "고매출" 표시)

3. 데이터베이스에  가져오기

파워 쿼리를 이용하면 엑셀에서 직접 다양한 종류의 데이터베이스에 연결하여 데이터를 가져올 수 있습니다. 이를 통해 데이터 분석 및 활용의 폭을 넓힐 수 있습니다.

예시: SQL Server 데이터베이스에서 고객 정보 가져오기

1. 데이터베이스 연결: SQL Server Database를 선택하고 서버 이름, 데이터베이스 이름, 인증 정보를 입력합니다.
2. 테이블 선택: "Customers"라는 테이블을 선택합니다.
3. 데이터 변환:
   1) "CustomerID" 열을 기준으로 정렬합니다.
   2) "Country" 열에서 "Korea"인 데이터만 필터링합니다.
   3) 데이터 로드: 닫기 & 로드를 클릭하여 엑셀 시트에 한국 고객 정보만 가져옵니다.

 

 

예시: SQL 쿼리 사용하여 특정 데이터 추출

         1. 데이터베이스 연결: 위와 동일하게 SQL Server Database에 연결합니다.
         2. SQL 쿼리 입력: 다음과 같은 SQL 쿼리를 입력합니다.

SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'Korea'

        3. 데이터 변환 및 로드: 위와 동일하게 데이터를 변환하고 엑셀 시트에 로드합니다.

4. WEB에서 가져오기

Power Query를 사용하여 웹에서 데이터를 가져와 실생활에서 활용할 수 있는 몇 가지 예제 입니다. 

예제: 환율 정보 가져오기

시나리오:
수출입 업무 담당자가 매일 최신 환율 데이터를 가져와 보고서를 작성한다고 가정 합니다.

단계:
1. URL 입력

• 환율 데이터를 제공하는 사이트 사용:
• 예시) https://www.x-rates.com/table/?from=USD&amount=1

2. Power Query로 테이블 가져오기

• URL을 입력하고 데이터를 가져옵니다.
• 원하는 환율 테이블을 선택합니다.

3. 데이터 변환

• 주요 환율(예: USD → EUR, USD → KRW)만 남깁니다.
• 소수점 자리수 설정 및 필터링.

4. Excel에 저장

• 매일 업데이트된 데이터를 자동으로 불러오도록 설정합니다.

 

엑셀 웹 정보 자동으로 불러오기 (1) 파워쿼리 - 환율편

 

---

Power Query M 언어는 Microsoft의 Power Query 기능을 뒷받침하는 함수형 프로그래밍 언어입니다. 이 언어는 데이터를 가져오고, 변환하며, 다양한 소스에서 데이터를 통합하는 과정을 정의하는 데 사용됩니다. M 언어는 Power BI, Excel 및 기타 Microsoft 제품에서 Power Query 편집기를 통해 사용됩니다.

▷ Power Query M 언어의 특징

1. 함수형 프로그래밍 언어

• M은 함수형 언어로, 모든 작업이 함수 기반으로 처리됩니다.
• 상태 변화 없이 입력 데이터를 변환하여 결과를 생성합니다.

2. 데이터 변환에 특화

• 데이터를 가져오고, 정리하고, 변환하는 작업에 최적화되어 있습니다.
• 여러 소스(웹, 데이터베이스, 파일 등)에서 데이터를 연결 및 통합하는 데 사용됩니다.

3. 직관적인 구조

• 읽기 쉬운 코드 구조로 설계되어 있어 데이터 변환 작업을 간결하게 정의할 수 있습니다.

4. 쿼리 작성 및 편집

• Power Query 편집기에서 UI를 사용해 데이터를 변환하면 자동으로 M 코드가 생성됩니다.
• 생성된 코드를 직접 수정하거나 새 코드를 작성해 더 복잡한 변환 작업을 수행할 수 있습니다.

▷ Power Query M 언어 기본 구조

M 언어의 코드는 일반적으로 단계적(단계=step)으로 구성됩니다. 각 단계는 이전 단계의 결과를 기반으로 데이터를 변환하며, 마지막 단계에서 최종 데이터를 반환합니다.

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter=",", Columns=3, Encoding=65001]),
    FilteredRows = Table.SelectRows(Source, each [Sales] > 1000),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"Sales", "Total Sales"}})
in
    RenamedColumns

let 블록:
모든 단계 정의를 시작하는 부분입니다.
각 단계는 변수처럼 이름을 지정하며 결과를 저장합니다.

Source:
데이터 소스(예: CSV 파일)에서 데이터를 가져오는 단계입니다.

FilteredRows:
특정 조건(예: Sales > 1000)을 만족하는 행만 선택합니다.

FilteredRows:
특정 조건(예: Sales > 1000)을 만족하는 행만 선택합니다.

in 블록:
최종 출력 데이터를 반환합니다.

RenamedColumns:
열 이름을 변경합니다.

파워 쿼리(Power Query)는 다양한 , 여러곳에 흐터진 데이터를 기계가 이해 할수 있도록 데이터를 정렬합니다. 중요한것은 한번 정렬된 파워 쿼리(Power Query) 데이터는  연결된 데이터들이 변경 되면, 자동으로 업데이트를 할수 있습니다.  이러한 데이터는 데이터 시각화에 사용 됩니다. 또는 기계 학습에 사용 될것 입니다.

□ 데이터 시각화 학습