파워쿼리를 이용하여 엑셀에 입력한 조건으로 DB 쿼리하기

구글링을 해서 해외 블로그에서 찾은 방법으로 요약하면 엑셀 시트에서 입력한 값을 SQL WHERE 절에 넣어서 데이터를 가져오는 방법입니다. 

DB에서 데이터를 가져올 때 SQL문을 사용하면 필요한 만큼 데이터를 가져올 수 있죠. 게다가 필요한 정보를 SQL로 쿼리 하여 아예 구성해서 가져올 수 있기 때문에 파워쿼리 또는 파워피벗이 해야 하는 일을 나누어 최적화가 가능합니다. 요즘 읽고 있는 책에도 이 방법을 1순위로 추천하고 있네요. 

특히 MS에서 다운로드 받아 구성한 Contoso DB가 양이 너무 많아 줄이지 않으면 제 PC에서 감당이 안되네요. 그래서 이왕 하는 김에 이것도 까먹지 않기 위해 포스팅 합니다. 

1. 먼저 DB에서 데이터를 가져오기 부터 시작합니다. 

데이터 가져오기 에서 SQL Server 를 선택하면 아래와 같이 팝업이 뜹니다. 

여기에 서버와 DB명을 입력하고 '고급옵션'을 클릭하면 SQL문을 입력하여 실행된 결과를 파워쿼리로 가져 올 수 있게 됩니다. 노란색 칸에 SQL 문을 입력하면 됩니다. 

예시로 몇 줄만 가져와 봅니다. 일단 로드 합니다. 

SELECT * 
FROM V_ProductForecast A 
WHERE A.CalendarMonth = '200904'

위 쿼리가 적용되어 데이터를 가져온 것이죠.  V_ProductForecast 테이블에서 CalendarMonth 가 '200904' 인 데이터를 가져온 것입니다.

여기에서 다른 월의 데이터를 가져오려면 계속 SQL문을 수정해야하는 번거로움이 생깁니다. CalendarMonth의 조건을 엑셀에 입력된 값을 사용하도록 변수를 만들어주는 작업을 이제부터 진행합니다. 

먼저 엑셀에 조건 값을 입력할 수 있게 표를 만듭니다. 

만들어볼 조건은 CalendarMonth 와 ProductCategoryName 2개입니다. 

일단 CalendarMonth 만 해보죠. 

작성된 표를 파워쿼리로 생성합니다. 우클릭하여 '표/범위에서 데이터 가져오기'를 사용하면 편하게 생성이 가능합니다. 

만들어진 쿼리를 P_input 라고 이름을 붙이고 이를 참조하는 쿼리를 하나 더 만듭니다. 

그리고 '조회조건' 열을 필터하여 'CalendarMonth ' 만 남깁니다. 

다른 열은 다 지우고 'WHERE 절 적용' 필드만 남긴 상태에서 해당 필드를 선택하고 '변환>목록으로 변환' 을 클릭하여 목록(LIST)형태로 변경합니다. 

여기까지 하면 위와 같은 모양으로 테이블이 아닌 문자값으로 변환이 됩니다. 새로 참조하여 만든 쿼리 이름은 CalendarMonth 로 변경합니다. 

이제 이 문자값을 '쿼리1'에 반영해주면 됩니다. 

쿼리1 의 원본 단계의 설정을 클릭하여 SQL문을 볼 수 있도록 합니다. 자 이제 WHERE 절을 수정해주어야 하는데 문제가 있습니다. 지금 만들어놓은 'CalendarMonth' 쿼리를 SQL문에 넣어줘야 하는데 이 쿼리문에서는 'CalendarMonth'가무엇인지 알수가 없습니다.

원본의 앞단계에 'CalendarMonth' 를 포함하는 SQL문을 만들어줘야 하는데 원본의 앞단계를 그냥 추가 할 수 없기 때문에 '고급편집기' 를 통해 작성이 필요합니다. 

그냥 넣어보면 에러가.. 

고급편집기는 보기 탭에 있습니다. 클릭하여 창을 띄워 봅니다. 

고급편집기에서 원본 앞에 단계를 직접 작성한 하였습니다. 

2가지 단계를 추가하였는데 먼저 

첫번째 단계는 

P_CalendarMonth = CalendarMonth ,

CalendarMonth 쿼리의 값을 가져오는 단계로 단계명을 "P_CalendarMonth" 라고 명명 하었습니다. 이러면 이제 이 쿼리에서 P_CalendarMonth단계를 통해서 엑셀에서 입력한 CalendarMonth의 값을 인식할 수 있게됩니다.

두번째 단계는 SQL 문장을 만들어 주는일입니다.

query01 = "SELECT * #(lf)FROM V_ProductForecast A #(lf)WHERE A.CalendarMonth "& P_CalendarMonth &"",

query01 이라는 이름을 붙여서 SQL 문을 적어줍니다. 여기에 WHERE 절에 앞 단계를 포함 시켜줍니다. 

여기서 맨 끝에 "" 를 주의하세요. 이유를 잘 모르겠는데 꼭 붙여줘야 됩니다. ㅜㅜ; 저도 문돌이다 보니... 

마지막으로 원본 단계를 수정합니다. 

원본 = Sql.Database("127.0.0.1", "ContosoRetailDW", [Query=query01])

원래는 [Query=query01] 부분에 전단계의 SQL 문이 들어가 있지만 이 부분을 앞단계의 이름으로 대체 합니다. 

이러면 완료되었습니다. 

그런데 에러가 나는 분들이 있을 겁니다. 이게 가장 골치 아픈데... 보안 설정과 관련된 사항이라 합니다. 

파워쿼리 편집기의 파일탭으로 가서 '옵션 및 설정> 쿼리 옵션'을 눌러서 위처럼 보완과 개인정보의 옵션을 변경해주세요. 

그리고 실행해보면 에러가 안납니다. 이유는 저도 ㅡㅡ 모르겠습니다. 

이제~ 엑셀 조회조건 시트에서 값을 '200910'으로 변경하고 모두 새로고침을 눌러봅니다. 

그럼 한참을 돌아서.. 아.. DB가 너무 무거워요.. 

짜잔.. 

끝~ 

과정을 설명하기 위해서 조금 돌아 돌아 진행했지만 과정이 이해가 된다면 여러가지로 응용하여 더 간단하고 다양하게 적용이 가능합니다. 

이를 테면 추출 테이블이나 SELECT 절의 내용도 동적으로 표현이 가능합니다. 조회 조건도 저는 '=' 이지만 'LIKE' 나 'BETWEEN' 도 얼마든지 가능합니다. 

예시 파일 첨부합니다. 하지만 제 PC의 DB 이므로 수행은 안될 것 같네요

파워쿼리_SQL문 동적으로 사용 예시.xlsx

0.02MB

설정 변경이나 팁은 아래 링크를 참고하시면 좋겠네요. 

www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

Pass Parameters to SQL Queries - The Excelguru Blog