Power Query 편집기 사용하여 Power Query 수식을 만들었습니다. Power Query 어떻게 작동하는지 살펴보겠습니다. Power Query 편집기 작동하는 모습을 확인하여 수식을 업데이트하거나 추가하는 방법을 알아볼 수 있습니다. 고급 편집기 직접 수식을 롤할 수도 있습니다.
이 Power Query 편집기 많은 데이터 원본의 데이터를 재구성하는 데 사용할 수 있는 Excel용 데이터 쿼리 및 셰이핑 환경을 제공합니다. Power Query 편집기 창을 표시하려면 Excel 워크시트의 외부 데이터 원본에서 데이터를 가져오고 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 다음은 기본 구성 요소에 대한 요약입니다.
-
데이터를 셰이프하는 데 사용하는 Power Query 편집기 리본 메뉴
-
데이터 원본 및 테이블을 찾는 데 사용하는 쿼리 창
-
리본 메뉴의 명령에 대한 편리한 바로 가기 메뉴
-
데이터에 적용된 단계의 결과를 표시하는 데이터 미리 보기
-
쿼리의 속성과 각 단계를 나열하는 쿼리 설정 창
백그라운드에서 쿼리의 각 단계는 수식 입력줄에 표시되는 수식을 기반으로 합니다.
수식을 수정하거나 만들려는 경우가 있을 수 있습니다. 수식은 Power Query 수식 언어를 사용합니다. 이 수식은 간단하고 복잡한 식을 빌드하는 데 사용할 수 있습니다. 구문, 인수, 설명, 함수 및 예제에 대한 자세한 내용은 M 수식 언어 Power Query 참조하세요.
예를 들어 축구 선수권 대회 목록을 사용하여 Power Query 사용하여 웹 사이트에서 찾은 원시 데이터를 가져와서 형식이 좋은 테이블로 바꿉니다. 쿼리 설정 창의 적용된 단계 및 수식 입력줄에서 각 작업에 대해 쿼리단계 및 해당 수식을 만드는 방법을 확인합니다.
절차
-
데이터를 가져오려면 웹에서데이터 > 선택하고 URL 상자에 "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"를 입력한 다음 확인을 선택합니다.
-
탐색기 대화 상자의 왼쪽에 있는 결과 [편집] 테이블을 선택한 다음, 아래쪽에서 데이터 변환을 선택합니다. Power Query 편집기가 나타납니다.
-
기본 쿼리 이름을 변경하려면 쿼리 설정 창의 속성에서 "결과 [편집]"을 삭제한 다음 "UEFA 챔피언"을 입력합니다.
-
원치 않는 열을 제거하려면 첫 번째, 네 번째 및 다섯 번째 열을 선택한 다음 홈 > 열 제거 > 기타 열 제거를 선택합니다.
-
원치 않는 값을 제거하려면 Column1을 선택하고 홈 > 값 바꾸기를 선택하고 찾을 값 상자에 "세부 정보"를 입력한 다음 확인을 선택합니다.
-
"Year'라는 단어가 포함된 행을 제거하려면 Column1에서 필터 화살표를 선택하고 "Year" 옆에 있는 검사 상자의 선택을 취소한 다음 확인을 선택합니다.
-
열 머리글의 이름을 바꾸려면 각 머리글을 두 번 클릭한 다음 "Column1"을 "Year"로, "Column4"를 "Winner"로, "Column5"를 "최종 점수"로 변경합니다.
-
쿼리를 저장하려면 홈 > 닫기 & 로드를 선택합니다.
결과
다음 표는 적용된 각 단계와 해당 수식에 대한 요약입니다.
쿼리 단계 및 작업 |
수식 |
---|---|
원본 웹 데이터 원본에 연결 |
= 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 만들어지므로 신중하게 편집해야 합니다.
수식 입력줄 표시 또는 숨기기
수식 입력줄은 기본적으로 표시되지만 표시되지 않으면 다시 표시할 수 있습니다.
-
보기 > 레이아웃 > 수식 입력줄을 선택합니다.
수식 입력줄에서 수식을 디트합니다.
-
쿼리를 열려면 Power Query 편집기 이전에 로드한 쿼리를 찾고 데이터에서 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
-
쿼리 설정 창의 적용된 단계에서 편집할 단계를 선택합니다.
-
수식 입력줄에서 매개 변수 값을 찾아 변경한 다음 입력 아이콘을 선택하거나 Enter 키를 누릅니다. 예를 들어 이 수식을 변경하여 Column2: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"}) After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})
Before: -
입력 아이콘을 선택하거나 Enter 키를 눌러 데이터 미리 보기에 표시되는 새 결과를 확인합니다.
-
Excel 워크시트의 결과를 보려면 홈 > 닫기 & 로드를 선택합니다.
수식 입력줄에서 수식 만들기
간단한 수식 예제의 경우 Text.Proper함수를 사용하여 텍스트 값을 적절한 대/소문자로 변환해 보겠습니다.
-
빈 쿼리를 열려면 Excel에서 데이터 > 다른 원본에서 데이터 > 가져오기 > 빈 쿼리를 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
-
수식 입력줄에서=Text.Proper("text value")입력한 다음, 입력 아이콘을 선택하거나 Enter 키를 누릅니다. 결과는 데이터 미리 보기 에 표시됩니다.
-
Excel 워크시트의 결과를 보려면 홈 > 닫기 & 로드를 선택합니다.
결과:
수식을 만들 때 Power Query 수식 구문의 유효성을 검사합니다. 그러나 쿼리에서 중간 단계를 삽입, 다시 정렬 또는 삭제하면 쿼리가 중단될 수 있습니다. 데이터 미리 보기에서 항상 결과를 확인합니다.
중요 원본, 탐색 및 변경된 형식 단계는 데이터 원본을 정의하고 설정하기 위해 Power Query 만들어지므로 신중하게 편집해야 합니다.
대화 상자를 사용하여 수식 편집
이 메서드는 단계에 따라 달라지는 대화 상자를 사용합니다. 수식의 구문을 알 필요가 없습니다.
-
쿼리를 열려면 Power Query 편집기 이전에 로드한 쿼리를 찾고 데이터에서 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
-
쿼리 설정 창의 적용된 단계에서 편집하려는 단계의 설정 편집 아이콘을 선택하거나 단계를 마우스 오른쪽 단추로 클릭한 다음 설정 편집을 선택합니다.
-
대화 상자에서 변경한 다음 확인을 선택합니다.
단계 삽입
데이터를 재구성하는 쿼리 단계를 완료하면 현재 쿼리 단계 아래에 쿼리 단계가 추가됩니다. 하지만 단계 중간에 쿼리 단계를 삽입하면 후속 단계에서 오류가 발생할 수 있습니다. Power Query 새 단계를 삽입하려고 할 때 삽입 단계 경고가 표시되고 새 단계에서는 삽입된 단계 다음 단계에서 사용되는 열 이름과 같은 필드를 변경합니다.
-
쿼리 설정 창의 적용된 단계에서 새 단계 바로 앞에 표시할 단계와 해당 수식을 선택합니다.
-
수식 입력줄 왼쪽에 있는 단계 추가 아이콘을 선택합니다. 또는 단계를 마우스 오른쪽 단추로 클릭한 다음, 단계 후 삽입을 선택합니다. = <nameOfTheStepToReference>형식(예:=Production.WorkOrder)으로 새 수식이 만들어집니다.
-
형식을 사용하여 새 수식을 입력 =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단계: 고급 편집기 열기
-
Excel에서 데이터 > 데이터 가져오기 > 기타 원본 > 빈 쿼리를 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
-
Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 let 식의 템플릿이 열립니다.
2단계: 데이터 원본 정의
-
다음과 같이 Excel.CurrentWorkbook 함수를 사용하여 let 식을 만듭니다.let#x1in Source#x4
-
워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.
결과:
3단계: 첫 번째 행을 헤더로 승격
-
쿼리를 열려면 워크시트에서 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집(Power Query)을 참조하세요.
-
Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 2단계: 데이터 원본 정의에서 만든 문과 함께 열립니다.
-
let 식에서 #"첫 번째 행을 헤더로" 및 Table.PromoteHeaders 함수를 다음과 같이 추가합니다let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source)#x3 #"First Row as Header"
-
워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.
결과:
4단계: 열의 각 값을 적절한 대/소문자로 변경
-
쿼리를 열려면 워크시트에서 데이터의 셀을 선택한 다음 쿼리 > 편집을 선택합니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
-
Power Query 편집기 홈 > 고급 편집기 선택합니다. 그러면 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"
-
워크시트에 쿼리를 로드하려면 완료를 선택한 다음 홈 > 닫기 & 로드 >& 로드 닫기를 선택합니다.
결과:
모든 통합 문서에 대한 Power Query 편집기 수식 입력줄의 동작을 제어할 수 있습니다.
수식 입력줄 표시 또는 숨기기
-
파일 > 옵션 및 설정 > 쿼리 옵션을 선택합니다.
-
왼쪽 창의 전역 아래에서 Power Query 편집기 선택합니다.
-
오른쪽 창의 레이아웃에서 수식 표시줄 표시를 선택하거나 선택을 취소합니다.
M Intellisense 켜기 또는 끄기
-
파일 > 옵션 및 설정 > 쿼리 옵션을 선택합니다.
-
왼쪽 창의 전역 아래에서 Power Query 편집기 선택합니다.
-
오른쪽 창의 수식에서 수식입력줄, 고급 편집기 및 사용자 지정 열 대화 상자에서 M Intellisense 사용을 선택하거나 선택 취소합니다.
참고: 이 설정을 변경하면 다음에 Power Query 편집기 창을 열 때 적용됩니다.