XL 모수, 엑셀값 사용, Using Parameters in PowerQuery

20211016 2254 

 

REF

[1] "https://exceloffthegrid.com/power-query-using-parameters/"

[2] "https://support.microsoft.com/ko-kr/office/%EB%A7%A4%EA%B0%9C-%EB%B3%80%EC%88%98-%EC%BF%BC%EB%A6%AC-%EB%A7%8C%EB%93%A4%EA%B8%B0-power-query-5eb365bc-3982-4ab2-8830-b205a69e0f33" 셀 값을 사용하여 데이터 필터링

 

파일

20230520 1939.xlsx

0.02MB

 

개요

파워쿼리의 드릴다운** 기능을 이용해서 모수 parameter를 사용한다.

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

 

실습

(1) 테이블을 만든다 : t_salses

(1-1)위 테이블에서 쿼리를 만든다 : t_sales_q

(경로) 데이터>데이터가져오기>기타원본>테이블범위

 

(2) 필터링 테이블 만들기: t_soldby

(2-1)역시 위 테이블에서 쿼리를 만든다 : t_soldby_q

 

(3) 파워쿼리로 가서..

필터링 테이블을 드릴다운 하기

 

드릴다운 한 뒤 생성된 코드는 다음과 같다.

let     원본 = Excel.CurrentWorkbook(){[Name="t_soldby"]}[Content],     #"sold by" = 원본{0}[sold by] in     #"sold by"

(4) t_sales_q 쿼리로 가서

필터링 단계를 추가하고, 해당 코드를 다음과 같이 바꾼다.

= Table.SelectRows(#"변경된 유형", each [sold by] = t_soldby_q)

(5) 엑셀로 돌아가서

t_soldby 테이블의 값을 바꾼 뒤, refresh하면 필터조건을 반영하여 테이블이 업데이트 된다.

 

결과

 

 

(참고) [2]의 설명 -- 우리말 번역이 어색하지만..

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

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

   G

2. Excel 테이블에서 셀을 선택한 다음 데이터 > 테이블/범위에서 데이터 > 가져오기를 선택합니다.Power Query 편집기 나타납니다.
3. 오른쪽에 있는 쿼리 설정 창의 이름 상자에서 FilterCellValue와 같이 쿼리 이름을 더 의미 있는 것으로 변경합니다. 
4. 테이블 자체가 아닌 테이블의 값을 전달하려면 데이터 미리 보기에서 값을 마우스 오른쪽 단추로 클릭한 다음 드릴다운을 선택합니다.10단계에서 Excel Table을 필터로 사용하는 경우 Power Query 테이블 값을 필터 조건으로 참조합니다. Excel 테이블에 대한 직접 참조로 인해 오류가 발생합니다.
5. 수식이 = #"Changed Type"{0}[MyFilter] 변경되었습니다.
6. 홈 > 닫기 & 로드 > 닫기 & 로드를 선택합니다. 이제 12단계에서 사용하는 "FilterCellValue"라는 쿼리 매개 변수가 있습니다.
7. 데이터 가져오기 대화 상자에서 연결 만들기만을 선택한 다음 확인을 선택합니다.
8. 데이터에서 셀을 선택한 다음 쿼리> 편집을 선택하여 이전에 Power Query 편집기 로드한 FilterCellValue 테이블의 값으로 필터링하려는 쿼리 를 엽니다. 자세한 내용은 Excel에서 쿼리 만들기, 로드 또는 편집을 참조하세요.
9. 열 머리글에서 필터 화살표를 선택하여 데이터를 필터링한 다음 텍스트 필터> 시작과 같은 필터 명령을 선택합니다. 행 필터링 대화 상자가 나타납니다. 
10. 값 상자에 "G"와 같은 값을 입력한 다음 확인을 선택합니다. 이 경우 값은 다음 단계에서 입력하는 FilterCellValue 테이블의 값에 대한 임시 자리 표시자입니다.
11. 수식 입력줄의 오른쪽에 있는 화살표를 선택하여 전체 수식을 표시합니다. 다음은 수식
    
    의 필터 조건 예제입니다. = Table.SelectRows(#"변경된 형식", 각 Text.StartsWith([Name], "G"))
12. 필터의 값을 선택합니다. 수식에서 "G"를 선택합니다.
13. M Intellisense를 사용하여 만든 FilterCellValue 테이블의 처음 몇 글자를 입력한 다음, 표시되는 목록에서 선택합니다.
14. 홈 > 닫기 > & 로드 닫기를 선택합니다.

결과

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