Excel에서 파워 쿼리 수식 만들기

이 Power Query 편집기 많은 데이터 원본의 데이터를 재구성하는 데 사용할 수 있는 Excel용 데이터 쿼리 및 셰이핑 환경을 제공합니다. Power Query 편집기 창을 표시하려면 Excel 워크시트의 외부 데이터 원본에서 데이터를 가져오고 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 다음은 기본 구성 요소에 대한 요약입니다.

1. 데이터를 셰이프하는 데 사용하는 Power Query 편집기 리본 메뉴

2. 데이터 원본 및 테이블을 찾는 데 사용하는 쿼리 창

3. 리본 메뉴의 명령에 대한 편리한 바로 가기 메뉴

4. 데이터에 적용된 단계의 결과를 표시하는 데이터 미리 보기

5. 쿼리의 속성과 각 단계를 나열하는 쿼리 설정 창

백그라운드에서 쿼리의 각 단계는 수식 입력줄에 표시되는 수식을 기반으로 합니다.

수식을 수정하거나 만들려는 경우가 있을 수 있습니다. 수식은 Power Query 수식 언어를 사용합니다. 이 수식은 간단하고 복잡한 식을 빌드하는 데 사용할 수 있습니다. 구문, 인수, 설명, 함수 및 예제에 대한 자세한 내용은 M 수식 언어 Power Query 참조하세요.

예를 들어 축구 선수권 대회 목록을 사용하여 Power Query 사용하여 웹 사이트에서 찾은 원시 데이터를 가져와서 형식이 좋은 테이블로 바꿉니다. 쿼리 설정 창의 적용된 단계 및 수식 입력줄에서 각 작업에 대해 쿼리단계 및 해당 수식을 만드는 방법을 확인합니다.

절차

1. 데이터를 가져오려면 웹에서데이터 > 선택하고 URL 상자에 "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"를 입력한 다음 확인을 선택합니다.

2. 탐색기 대화 상자의 왼쪽에 있는 결과 [편집] 테이블을 선택한 다음, 아래쪽에서 데이터 변환을 선택합니다. Power Query 편집기가 나타납니다.

3. 기본 쿼리 이름을 변경하려면 쿼리 설정 창의 속성에서 "결과 [편집]"을 삭제한 다음 "UEFA 챔피언"을 입력합니다.

4. 원치 않는 열을 제거하려면 첫 번째, 네 번째 및 다섯 번째 열을 선택한 다음 홈 > 열 제거 > 기타 열 제거를 선택합니다.

5. 원치 않는 값을 제거하려면 Column1을 선택하고 홈 > 값 바꾸기를 선택하고 찾을 값 상자에 "세부 정보"를 입력한 다음 확인을 선택합니다.

6. "Year'라는 단어가 포함된 행을 제거하려면 Column1에서 필터 화살표를 선택하고 "Year" 옆에 있는 검사 상자의 선택을 취소한 다음 확인을 선택합니다.

7. 열 머리글의 이름을 바꾸려면 각 머리글을 두 번 클릭한 다음 "Column1"을 "Year"로, "Column4"를 "Winner"로, "Column5"를 "최종 점수"로 변경합니다.

8. 쿼리를 저장하려면 홈 > 닫기 & 로드를 선택합니다.

결과

다음 표는 적용된 각 단계와 해당 수식에 대한 요약입니다.

쿼리 단계 및 작업	수식
원본 웹 데이터 원본에 연결	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
탐색 연결할 테이블 선택	=Source{2}[Data]
변경된 형식 데이터 형식 변경(Power Query 자동으로 수행)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
제거된 다른 열 수 다른 열을 제거하여 관심 있는 열만 표시	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
대체된 값 선택한 열의 값을 클린 값 바꾸기	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
필터링된 행 열의 값 필터링	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
이름이 바뀐 열 열 머리글이 의미 있는 것으로 변경됨	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

중요     원본, 탐색  및 변경된 형식 단계는 데이터 원본을 정의하고 설정하기 위해 Power Query 만들어지므로 신중하게 편집해야 합니다.

수식 입력줄 표시 또는 숨기기

수식 입력줄은 기본적으로 표시되지만 표시되지 않으면 다시 표시할 수 있습니다.

• 보기 > 레이아웃 > 수식 입력줄을 선택합니다.

수식 입력줄에서 수식을 디트합니다.

1. 쿼리를 열려면 Power Query 편집기 이전에 로드한 쿼리를 찾고 데이터에서 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

2. 쿼리 설정 창의 적용된 단계에서 편집할 단계를 선택합니다.

3. 수식 입력줄에서 매개 변수 값을 찾아 변경한 다음 입력 아이콘을 선택하거나 Enter 키를 누릅니다. 예를 들어 이 수식을 변경하여 Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. 입력 아이콘을 선택하거나 Enter 키를 눌러 데이터 미리 보기에 표시되는 새 결과를 확인합니다.

5. Excel 워크시트의 결과를 보려면 홈 > 닫기 & 로드를 선택합니다.

수식 입력줄에서 수식 만들기

간단한 수식 예제의 경우 Text.Proper함수를 사용하여 텍스트 값을 적절한 대/소문자로 변환해 보겠습니다.

1. 빈 쿼리를 열려면 Excel에서 데이터 > 다른 원본에서 데이터 > 가져오기 > 빈 쿼리를 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

2. 수식 입력줄에서=Text.Proper("text value")입력한 다음, 입력 아이콘을 선택하거나 Enter 키를 누릅니다.
   
   결과는 데이터 미리 보기 에 표시됩니다.

3. Excel 워크시트의 결과를 보려면 홈 > 닫기 & 로드를 선택합니다.

결과:

 수식을 만들 때 Power Query 수식 구문의 유효성을 검사합니다. 그러나 쿼리에서 중간 단계를 삽입, 다시 정렬 또는 삭제하면 쿼리가 중단될 수 있습니다.  데이터 미리 보기에서 항상 결과를 확인합니다.

중요     원본, 탐색  및 변경된 형식 단계는 데이터 원본을 정의하고 설정하기 위해 Power Query 만들어지므로 신중하게 편집해야 합니다.

대화 상자를 사용하여 수식 편집

이 메서드는 단계에 따라 달라지는 대화 상자를 사용합니다. 수식의 구문을 알 필요가 없습니다.

1. 쿼리를 열려면 Power Query 편집기 이전에 로드한 쿼리를 찾고 데이터에서 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

2. 쿼리 설정 창의 적용된 단계에서 편집하려는 단계의 설정 편집 아이콘을 선택하거나 단계를 마우스 오른쪽 단추로 클릭한 다음 설정 편집을 선택합니다.

3. 대화 상자에서 변경한 다음 확인을 선택합니다.

단계 삽입

데이터를 재구성하는 쿼리 단계를 완료하면 현재 쿼리 단계 아래에 쿼리 단계가 추가됩니다. 하지만 단계 중간에 쿼리 단계를 삽입하면 후속 단계에서 오류가 발생할 수 있습니다. Power Query 새 단계를 삽입하려고 할 때 삽입 단계 경고가 표시되고 새 단계에서는 삽입된 단계 다음 단계에서 사용되는 열 이름과 같은 필드를 변경합니다.

1. 쿼리 설정 창의 적용된 단계에서 새 단계 바로 앞에 표시할 단계와 해당 수식을 선택합니다.

2. 수식 입력줄 왼쪽에 있는 단계 추가 아이콘을 선택합니다. 또는 단계를 마우스 오른쪽 단추로 클릭한 다음, 단계 후 삽입을 선택합니다. = <nameOfTheStepToReference>형식(예:
   
   =Production.WorkOrder)으로 새 수식이 만들어집니다.

3. 형식을 사용하여 새 수식을 입력
   
   합니다
   
   =Class.Function(ReferenceStep[,otherparameters]) 예를 들어 Gender 열이 있는 테이블이 있고 값이 "Ms"인 열을 추가하려는 경우를 가정합니다. 또는 "Mr.", 사람의 성별에 따라 다릅니다. 수식은 다음과 같습니다=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")
   
   

단계 순서 다시 지정

• 적용된 단계 아래의 쿼리 설정 창에서 단계를 마우스 오른쪽 단추로 클릭한 다음 위로 이동 또는 아래로 이동을 선택합니다.

단계 삭제

• 단계 왼쪽에 있는 삭제 아이콘을 선택하거나 단계를 마우스 오른쪽 단추로 클릭한 다음 , 삭제 또는 종료될 때까지 삭제를 선택합니다. 수식 입력줄 왼쪽에는 삭제 아이콘도 사용할 수 있습니다.

이 예제에서는 고급 편집기 수식 조합을 사용하여 열의 텍스트를 적절한 대/소문자로 변환해 보겠습니다. 

예를 들어 적절한 대/소문자로 변환하려는 ProductName 열이 있는 Orders라는 Excel 테이블이 있습니다. 

변경 전:

변경 후:

고급 쿼리를 만들 때 let 식에 따라 일련의 쿼리 수식 단계를 만듭니다. let 식을 사용하여 이름을 할당하고 In 절에서 참조하는 값을 계산합니다. 그러면 단계가 정의됩니다. 이 예제에서는 "수식 입력줄에서 수식 만들기" 섹션의 결과와 동일한 결과를 반환합니다.

let  
    Source = Text.Proper("hello world")
in  
    Source  

각 단계는 이름별로 단계를 참조하여 이전 단계에 따라 빌드되는 것을 볼 수 있습니다. 미리 알림으로 Power Query 수식 언어는 대/소문자를 구분합니다.

1단계: 고급 편집기 열기

1. Excel에서 데이터 > 데이터 가져오기 > 기타 원본 > 빈 쿼리를 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

2. Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 let 식의 템플릿이 열립니다.

2단계: 데이터 원본 정의

1. 다음과 같이 Excel.CurrentWorkbook 함수를 사용하여 let 식을 만듭니다.
   
   let#x1in
   
   
       
   
   Source#x4

2. 워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.

결과:

3단계: 첫 번째 행을 헤더로 승격

1. 쿼리를 열려면 워크시트에서 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집(Power Query)을 참조하세요.

2. Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 2단계: 데이터 원본 정의에서 만든 문과 함께 열립니다.

3. let 식에서 #"첫 번째 행을 헤더로" 및 Table.PromoteHeaders 함수를 다음과 같이 추가합니다let
   
   
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. 워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.

결과:

4단계: 열의 각 값을 적절한 대/소문자로 변경

1. 쿼리를 열려면 워크시트에서 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

2. Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 3단계: 첫 번째 행을 머리글로 승격에서 만든 문과 함께 열립니다.

3. let 식에서 Table.TransformColumns 함수를 사용하여 각 ProductName 열 값을 적절한 텍스트로 변환하고, 이전의 "첫 번째 행을 헤더로" 쿼리 수식 단계를 참조하고 ,#"대문자 각 Word"를 데이터 원본에 추가한 다음 결과에 #"대문자 각 Word"을 할당합니다.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. 워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.

결과:

모든 통합 문서에 대한 Power Query 편집기 수식 입력줄의 동작을 제어할 수 있습니다.

수식 입력줄 표시 또는 숨기기

1. 파일 > 옵션 및 설정 > 쿼리 옵션을 선택합니다.

2. 왼쪽 창의 전역 아래에서 Power Query 편집기 선택합니다.

3. 오른쪽 창의 레이아웃에서 수식 표시줄 표시를 선택하거나 선택을 취소합니다.

M Intellisense 켜기 또는 끄기

1. 파일 > 옵션 및 설정 > 쿼리 옵션을 선택합니다.

2. 왼쪽 창의 전역 아래에서 Power Query 편집기 선택합니다.

3. 오른쪽 창의 수식에서 수식입력줄, 고급 편집기 및 사용자 지정 열 대화 상자에서 M Intellisense 사용을 선택하거나 선택 취소합니다.

참고:   이 설정을 변경하면 다음에 Power Query 편집기 창을 열 때 적용됩니다.