매개 변수 쿼리 만들기(Power Query)

매개 변수를 사용하여 쿼리의 값을 자동으로 변경하고 매번 쿼리를 편집하여 값을 변경하지 않도록 할 수 있습니다. 매개 변수 값을 변경하기만 하면 됩니다. 매개 변수를 만들면 Excel에서 직접 편리하게 변경할 수 있는 특수 매개 변수 쿼리에 저장됩니다.

1. 데이터 > 데이터 가져오기 > 기타 원본 > 시작 Power Query 편집기 선택합니다.

2. Power Query 편집기 홈 > 매개 변수 관리 > 새 매개 변수를 선택합니다.

3. 매개 변수 관리 대화 상자에서 새로 만들기를 선택합니다.

4. 필요에 따라 다음을 설정합니다.

   이름	매개 변수의 함수를 반영하지만 가능한 한 짧게 유지해야 합니다.
   설명	여기에는 사용자가 매개 변수를 올바르게 사용하는 데 도움이 되는 세부 정보가 포함될 수 있습니다.
   필수 항목	다음 중 하나를 수행합니다. 모든 값 매개 변수 쿼리에 데이터 형식의 값을 입력할 수 있습니다. 값 목록    작은 표에 값을 입력하여 값을 특정 목록으로 제한할 수 있습니다. 또한 아래의 기본값과 현재 값을 선택해야 합니다. 쿼리 쉼표로 구분되고 중괄호로 묶인 목록 구조화된 열과 유사한 목록 쿼리를 선택합니다. 예를 들어 문제 상태 필드에는 {"New", "Ongoing", "Closed"}의 세 가지 값이 있을 수 있습니다. 고급 편집기 열고(홈 > 고급 편집기 선택), 코드 템플릿을 제거하고 쿼리 목록 형식의 값 목록을 입력한 다음 완료를 선택하여 목록 쿼리를 미리 만들어야 합니다. 매개 변수 만들기를 마치면 목록 쿼리가 매개 변수 값에 표시됩니다.
   유형	매개 변수의 데이터 형식을 지정합니다.
   제안 값	원하는 경우 값 목록을 추가하거나 쿼리를 지정하여 입력에 대한 제안을 제공합니다.
   [기본값]	제안된 값이 값 목록으로 설정되고 기본값인 목록 항목을 지정하는 경우에만 나타납니다. 이 경우 기본값을 선택해야 합니다.
   현재 값	매개 변수를 사용하는 위치에 따라 비어 있으면 쿼리가 결과를 반환하지 않을 수 있습니다. 필수를 선택하면 현재 값을 비울 수 없습니다.

5. 매개 변수를 만들려면 확인을 선택합니다.

다음은 데이터 원본 위치에 대한 변경 내용을 관리하고 새로 고침 오류를 방지하는 방법입니다. 예를 들어 비슷한 스키마와 데이터 원본을 가정할 때 매개 변수를 만들어 데이터 원본을 쉽게 변경하고 데이터 새로 고침 오류를 방지합니다. 서버, 데이터베이스, 폴더, 파일 이름 또는 위치가 변경되는 경우가 있습니다. 데이터베이스 관리자가 서버를 교체하거나, 월별 CSV 파일 삭제가 다른 폴더로 이동하거나, 개발/테스트/프로덕션 환경 간에 쉽게 전환해야 할 수 있습니다.

1단계: 매개 변수 쿼리 만들기

다음 예제에서는 폴더 가져오기 작업(데이터 선택> 파일에서 데이터 가져오기 > 폴더 에서 > 가져오기)를 사용하여 가져오는 여러 CSV 파일이 C:\DataFilesCSV1 폴더 에 있습니다. 그러나 경우에 따라 다른 폴더가 파일 C:\DataFilesCSV2를 삭제하는 위치로 사용되는 경우가 있습니다. 쿼리에서 매개 변수를 다른 폴더의 대체 값으로 사용할 수 있습니다.

1. 홈 > 매개 변수 관리 > 새 매개 변수를 선택합니다.

2. 매개 변수 관리 대화 상자에 다음 정보를 입력합니다.

   이름	CSVFileDrop
   설명	대체 파일 삭제 위치
   필수 항목	예
   유형	텍스트
   제안 값	모든 값
   현재 값	C:\DataFilesCSV1

3. 확인을 선택합니다.

2단계: 데이터 쿼리에 매개 변수 추가

1. 폴더 이름을 매개 변수로 설정하려면 쿼리 설정의 쿼리 단계에서원본을 선택한 다음 설정 편집을 선택합니다.

2. 파일 경로 옵션이 매개 변수로 설정되어 있는지 확인한 다음 드롭다운 목록에서 방금 만든 매개 변수를 선택합니다.

3. 확인을 선택합니다.

3단계: 매개 변수 값 업데이트

폴더 위치가 변경되었으므로 이제 매개 변수 쿼리를 업데이트하기만 하면 됩니다.

1. 데이터 > 연결 & 쿼리 > 쿼리 탭을 선택하고 매개 변수 쿼리를 마우스 오른쪽 단추로 클릭한 다음 편집을 선택합니다.

2. 현재 값 상자에 C:\DataFilesCSV2와 같은 새 위치를 입력합니다.

3. 홈 > & 로드 닫기를 선택합니다.

4. 결과를 확인하려면 데이터 원본에 새 데이터를 추가한 다음 업데이트된 매개 변수를 사용하여 데이터 쿼리를 새로 고칩니다( 데이터 선택> 모두 새로 고침).

경우에 따라 쿼리를 편집하거나 동일한 쿼리의 복사본을 약간 다르게 만들지 않고도 쿼리 필터를 변경하여 다른 결과를 얻을 수 있습니다. 이 예제에서는 데이터 필터를 편리하게 변경하도록 날짜를 변경합니다.

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

2. 열 머리글에서 필터 화살표를 선택하여 데이터를 필터링한 다음 날짜 /시간 필터 > After와 같은 필터 명령을 선택합니다. 행 필터링 대화 상자가 나타납니다.
   
   

3. 값 상자 왼쪽에 있는 단추를 선택한 다음 다음 중 하나를 수행합니다.

   • 기존 매개 변수를 사용하려면 매개 변수를 선택한 다음 오른쪽에 표시되는 목록에서 원하는 매개 변수를 선택합니다.

   • 새 매개 변수를 사용하려면 새 매개 변수를 선택한 다음 매개 변수를 만듭니다.

4. 현재 값 상자에 새 날짜를 입력한 다음 홈 > & 로드 닫기를 선택합니다.

5. 결과를 확인하려면 데이터 원본에 새 데이터를 추가한 다음 업데이트된 매개 변수를 사용하여 데이터 쿼리를 새로 고칩니다( 데이터 선택> 모두 새로 고침). 예를 들어 필터 값을 다른 날짜로 변경하여 새 결과를 확인합니다.

6. 현재 값 상자에 새 날짜를 입력합니다.

7. 홈 > & 로드 닫기를 선택합니다.

8. 결과를 확인하려면 데이터 원본에 새 데이터를 추가한 다음 업데이트된 매개 변수를 사용하여 데이터 쿼리를 새로 고칩니다( 데이터 선택> 모두 새로 고침).

이 예제에서 쿼리 매개 변수의 값은 통합 문서의 셀에서 읽습니다. 매개 변수 쿼리를 변경할 필요가 없으며 셀 값을 업데이트하기만 하면 됩니다. 예를 들어 첫 글자를 기준으로 열을 필터링하지만 값을 A에서 Z로 쉽게 변경하려고 합니다.

1. 필터링하려는 쿼리가 로드된 통합 문서의 워크시트에서 머리글과 값이라는 두 개의 셀이 있는 Excel 테이블을 만듭니다.
    

   MyFilter
   G

2. Excel 테이블에서 셀을 선택한 다음 데이터 > 테이블/범위에서 데이터 > 가져오기를 선택합니다.Power Query 편집기 나타납니다.

3. 오른쪽에 있는 쿼리 설정 창의 이름 상자에서 FilterCellValue와 같이 쿼리 이름을 더 의미 있는 것으로 변경합니다. 

4. 테이블 자체가 아닌 테이블의 값을 전달하려면 데이터 미리 보기에서 값을 마우스 오른쪽 단추로 클릭한 다음 드릴다운을 선택합니다.

   수식이 = #"Changed Type"{0}[MyFilter] 변경되었습니다.

   10단계에서 Excel Table을 필터로 사용하는 경우 Power Query 테이블 값을 필터 조건으로 참조합니다. Excel 테이블에 대한 직접 참조로 인해 오류가 발생합니다.

5. 홈 > 닫기 & 로드 > 닫기 & 로드를 선택합니다. 이제 12단계에서 사용하는 "FilterCellValue"라는 쿼리 매개 변수가 있습니다.

6. 데이터 가져오기 대화 상자에서 연결 만들기만을 선택한 다음 확인을 선택합니다.

7. 데이터에서 셀을 선택한 다음 쿼리> 편집을 선택하여 이전에 Power Query 편집기 로드한 FilterCellValue 테이블의 값으로 필터링하려는 쿼리 를 엽니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.

8. 열 머리글에서 필터 화살표를 선택하여 데이터를 필터링한 다음 텍스트 필터> 시작과 같은 필터 명령을 선택합니다. 행 필터링 대화 상자가 나타납니다. 

9. 값 상자에 "G"와 같은 값을 입력한 다음 확인을 선택합니다. 이 경우 값은 다음 단계에서 입력하는 FilterCellValue 테이블의 값에 대한 임시 자리 표시자입니다.

10. 수식 입력줄의 오른쪽에 있는 화살표를 선택하여 전체 수식을 표시합니다. 다음은 수식
    
    의 필터 조건 예제입니다. = Table.SelectRows(#"변경된 형식", 각 Text.StartsWith([Name], "G"))

11. 필터의 값을 선택합니다. 수식에서 "G"를 선택합니다.

12. M Intellisense를 사용하여 만든 FilterCellValue 테이블의 처음 몇 글자를 입력한 다음, 표시되는 목록에서 선택합니다.

13. 홈 > 닫기 > & 로드 닫기를 선택합니다.

결과

이제 쿼리는 사용자가 만든 Excel 테이블의 값을 사용하여 쿼리 결과를 필터링합니다. 새 값을 사용하려면 1단계에서 원래 Excel 표의 셀 내용을 편집하고 "G"를 "V"로 변경한 다음 쿼리를 새로 고칩니다.

매개 변수 쿼리가 허용되는지 여부를 제어할 수 있습니다.

1. Power Query 편집기 파일 > 옵션 및 설정 > 쿼리 옵션 > Power Query 편집기 선택합니다.

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

3. 오른쪽 창의 매개 변수 아래에서 데이터 원본 및 변환 대화 상자에서 항상 매개 변수화 허용을 선택하거나 선택 취소합니다.