파워쿼리로 문자 검색기 만들기(복수의 단어)

이번 포스팅에서는 파워쿼리를 이용해서 검색 기능을 만들어 봅니다. 

최근 프로젝트에서 품목 속성, 특히 명칭의 정비하는 일을 하다보니 부쩍 문자 검색과 관련된 작업에 관심이 많아졌네요. 예전 직장에서도 이와 비슷한 일을 한적이 있었는데 정말 힘들었던 기억이 납니다. 엄청난 노가다 작업이기도 한데요. 다행히 그당시에는 TFT 내에 VBA를 잘 다루는 형님이 계셔서 좀 수월 했죠. 

하지만 엑셀도 발전해서 최근에 나온  FILTER 함수는 아주 기가 막히게 좋습니다. 이걸 보면 앞으로 VBA를 쓸일이 있을까 하는 생각이 들정도로 간편하고 뛰어나다는 생각이 듭니다. 단, 이 함수는 구독형인 Office 365버젼에서만 지원됩니다. 아무래도 Microsoft가 수익성이 좋은? 구독형 비지니스로 아예 오피스를 다 넘기지 않을까 하는 생각도 듭니다. 그럼 중국,인도 등 라이센스 구매 없이 오피스를 사용하는 수많은 인구를 가진 나라의 사람들이 모두 유료 고객이 되는 셈인데... MS 주식 사야 겠어요. ^^; 

1. 오늘의 데이터~ 

시스템에서 관리되는 Material_ID 와 그 품명(명칭)이 있는 데이터 입니다. 이 데이터를 검색하여 리스트를 출력하는 파워쿼리를 만들어봅시다. 

먼저 데이터를 파워쿼리로 생성합니다. 

2. 먼저 결과물~ 

먼저 완성된 형태를 볼까요. 

'검색어' 필드에 검색어를 입력하고 새로 고침하면 바로 옆에 위치한 표에 해당 리스트가 출력됩니다. 파워쿼리의 필터링 기능을 사용했습니다. 

그리고 파란색 표는 검색되어 출력된 리스트를 참조하여 수정할 새 이름을 넣는 표이고 이것을 다시 파워쿼리로 넘겨 원본 쿼리와 병합하여 수정된 이름과 해당 Material_ID 값을 새로운 표에 출력하게 됩니다. 

그럼 이 리스트를 가지고 DB에서 처리할 update문장을 만들어 실행시키면 되는것이죠. 

검색은 6개 항목까지로 제한하였습니다. 이걸 동적으로 만들어 낼 방법을 몰라서 하드코딩 했습니다. ㅜㅜ; 

검색어와 정확히 일치 하는 리스트라면 병합(join)을 하면 되는데 검색어로 입력한 값들이 포함된 리스트를 얻어야 하다보니 방법이 없네요. SQL로 치면 Where 절에 Like를 동적으로 or 조건으로 엮어야 하는건데요. 모르겠어요..

3. 작업 과정

1) 검색테이블을 만들고 텍스트 쿼리를 생성한다. 

먼저 검색어를 파워쿼리로 생성합니다. 

검색테이블 쿼리를 복사하여 단일 값을 갖는 텍스트 형태로 변경합니다. 

만드는 방법은 우선 빈값이 입력될 것을 대비해서 검색어가 빈값일 경우 데이터 없을 만한 텍스트로 변경하는 조건열을 추가하고 필터링을 동적으로 하기 위해서 인덱스를 부여 합니다. 

그다음 인덱스 필드를 1 부터 6 까지 하나씩 쿼리를 추가하면서 검색어(SearchWord1~6)쿼리를 6개 만들어 줍니다. 

적용된 단계는 첨부파일을 참조하세요. 

2) 출력할 쿼리를 만들어 저장한다. 

이제 원본데이터를 참조하여 'MATERIAL_NAME' 필드만 남기고 중복제거를 합니다. 

그리고 이상태에서 필터링을 하는데 우선 한개만 필터링을 한후에 수식입력창에 쿼리내용을 수정하여 여러 조건으로 필터링되도록 만들어줍니다. 

쿼리를 직접 수정하는 단계를 설명하면 먼저 텍스트 필터를 '포함' 옵션으로 실행합니다. 

필터링을 하게 되면 아래와 같은 식으로 적용이 됩니다. 여기서 each 뒤의 Text.Contains 함수 부분을 or 조건으로 여러개 만들어 주면 됩니다. 

이 때 검색단어 값을 앞에서 만들어 놓은 텍스트들을 하나씩 넣어 줍니다. 

그럼 검색 기능은 완료입니다. 

처음 결과물을 설명할 때 처럼 실제 업무에서는 동일한 명칭을 가진 자재ID 까지 모두 찾아서 변경된 이름으로 DB 에서 수정 할 수 있도록 SQL Up-date 문장을 만드는 것 까지 진행했습니다. 하지만 포스팅에서는 검색 까지만 ~

4. 주요 팁 ~ 

다시 강조하지만 단계별로 구성한 후 해당 쿼리의 M-Code 문장을 수정하는 방법은 매우 추천합니다. 가장 안정적인 꼼수 같아요. 

그리고 한계... 하드 코딩했어요.. ㅜㅜ; 

참고로 바로 필터 함수에 find 함수를 조합해서 사용해도 동일한 검색이 됩니다. 하지만 이후 작업이 불편합니다. 왜냐하면 파워쿼리로 할때는 처음부터 중복값을 제거해서 수정 작업을 했는데 필터 함수는 중복값 제거가 안되더라구요. 아니면 제가 방법을 모르는 것일 수도 있습니다. 게다가 저는 수정한 값을 다시 파워쿼리로 넘겨서 수정할 자재ID를 모두 찾는 것까지 진행 할 수 있었습니다. 

이왕 쓰는 김에 검색 이후 저의 작업 과정을 모두 요약하면

 - 정비하고 싶은 항목은 중복값이 포함되므로 양이 많아 검토가 어렵다. >>> 양을 줄이자 (중복값을 제거하고 작업)

 - 수정된 데이터는 시스템에서 수정할 수 있도록 다시 원래의 키 값(자재ID)을 찾아야 한다. >>> 수정된 결과를 파워쿼리로 생성하여 원본과 수정 전 값으로 병합하여 수정할 대상의 자재ID를 찾는다. 

 - 대상 ID 와 수정된 명칭을 리스트로 작성하면 엑셀로 서식을 걸어 up-date 문을 자동을 만든다. 그리고 바로 DB에서 수행하여 완료~ 

파워쿼리_단어 검색기_6개 하드코딩 버젼.xlsx

0.28MB