내용
정규 표현식에 대해 약간이라도 알고 있다면 광고할 필요가 없습니다. 주제에 익숙하지 않은 경우 정규식(Regular Expressions = RegExp = "regexps" = "regulars")은 특수 문자와 규칙을 사용하여 텍스트에서 필요한 부분 문자열을 검색하고 추출하는 언어입니다. 또는 다른 텍스트로 대체됩니다. 이것은 매우 강력하고 아름다운 도구로, 다른 모든 텍스트 작업 방식보다 훨씬 뛰어납니다.
나는 이미 간단한 매크로를 사용하여 Excel에 정규식 지원을 추가할 수 있는 방법과 실제 사례를 통해 자세히 설명했습니다. 이 기사를 읽지 않았다면 계속하기 전에 이 기사를 읽는 것이 좋습니다. 당신은 많은 새로운 것을 발견하게 될 것입니다, 나는 보장합니다 🙂
그러나 질문은 여전히 열려 있습니다. 파워 쿼리에서 정규식을 사용하는 기능을 추가하는 방법은 무엇입니까? 물론 파워 쿼리는 그 자체로 훌륭하고 텍스트로 많은 작업(잘라내기, 붙이기, 청소 등)을 할 수 있지만 정규식의 힘으로 그것을 넘을 수 있다면 그것은 단지 폭탄이 될 것입니다.
불행히도 파워 쿼리에는 RegExps 작업을 위한 기본 제공 기능이 없으며 공식 Microsoft 도움말 및 기술 지원에서는 이 질문에 대해 부정적인 답변을 제공합니다. 그러나이 제한을 피할 수있는 방법이 있습니다 🙂
방법의 본질
주요 아이디어는 욕하기 쉽습니다.
기본 제공 파워 쿼리 기능 목록에는 다음과 같은 기능이 있습니다. 웹 페이지. 공식 Microsoft 도움말 사이트에서 이 기능에 대한 설명은 매우 간결합니다.
번역하면 다음과 같습니다. "구성 요소 구조로 분류된 HTML 문서의 내용과 태그가 제거된 후 전체 문서 및 본문의 표현을 반환합니다." 솔직히 그렇게 설명합니다.
일반적으로 이 기능은 웹에서 데이터를 가져올 때 사용되며 예를 들어 탭에서 선택할 때 자동으로 대체됩니다. Data 명령 인터넷에서 (데이터 — 웹에서). 우리는 함수 웹 페이지를 인수로 제공하고 이전에 모든 태그를 지운 테이블 형식으로 내용을 반환합니다.
도움말에서 말하지 않는 것은 HTML 마크업 언어 외에 기능 웹 페이지 JavaScript 스크립트 지원, 이제 인터넷의 웹사이트에서 흔히 볼 수 있습니다. 그리고 JavaScript는 항상 정규식으로 작업할 수 있었고 RegExps를 위한 내장 기능을 가지고 있습니다! 따라서 파워 쿼리에서 정규식을 구현하려면 파워 쿼리에 대한 모든 작업을 수행할 작은 JavaScript 프로그램에 대한 인수로 Web.Page 함수를 제공해야 합니다.
순수 JavaScript에서 보이는 것
인터넷에서 JavaScript의 정규식 작업에 대한 자세한 자습서가 많이 있습니다(예: 하나, 둘).
간단히 말해서 JavaScript 코드는 다음과 같습니다.
이리:
- var str = '소시지 비용 123 및 789'; – 변수를 생성 하위 버전 분석할 소스 텍스트를 할당합니다.
- var 패턴 = /d+/gi; – 정규식을 만들고 변수에 넣습니다. 무늬.
표현식은 슬래시(/)로 시작합니다.
예를 들어 여기서 표현 자체는 d+ 모든 숫자 시퀀스를 나타냅니다.
표현식 뒤의 분수를 통해 추가 검색 매개변수(수정자)가 있습니다. 임의의 순서로 지정할 수 있습니다.
- g – 전체 검색을 의미합니다. 즉, 일치하는 항목을 찾은 후 중지하지 말고 텍스트 끝까지 검색을 계속해야 합니다. 이 수정자가 설정되지 않으면 스크립트는 첫 번째 일치 항목(123)만 반환합니다.
- i – 대소문자 구분 없이 검색
- m – 여러 줄 검색(원본 텍스트를 여러 줄로 나눌 때 사용)
- var 결과 = str.match(패턴).join(';'); – 소스 텍스트에서 검색 수행(하위 버전) 주어진 정규식(무늬) 결과를 변수에 넣습니다. 결과, 명령을 사용하여 세미콜론으로 연결 어울리다
- 문서.쓰기(결과); – 결과 변수의 내용을 표시합니다.
또한 JavaScript의 텍스트 문자열(정규식 제외)은 파워 쿼리 또는 VBA에서처럼 따옴표가 아니라 아포스트로피로 묶여 있습니다.
출력에서 이 스크립트는 결과적으로 소스 텍스트에서 찾은 모든 숫자를 제공합니다.
123, 789
JavaScript 단기 과정이 끝났습니다. 모두 감사합니다. 논리를 잡으시길 바랍니다^^
이 구성을 파워 쿼리로 전송하는 것이 남아 있습니다.
파워 쿼리에서 정규식으로 텍스트 함수 검색 및 추출
우리는 다음을 수행합니다.
1. Excel을 열고 탭에서 빈 파워 쿼리를 새로 만듭니다. 데이터 – 데이터 가져오기 / 요청 생성 – 다른 소스에서 – 빈 요청 (데이터 — 데이터 가져오기 / 새 쿼리 — 다른 소스에서 — 빈 쿼리). Excel 2010-2013 및 파워 쿼리의 이전 버전이 있고 기본 제공 기능이 없지만 별도의 추가 기능으로 설치된 경우 이 모든 것이 탭에 표시됩니다. 파워 쿼리그리고 Data.
2. 열리는 쿼리 편집기의 빈 창에서 오른쪽 패널에 즉시 미래 함수의 이름을 입력합니다(예: fxRegExp추출)
3. 탭으로 가자 보기 – 고급 편집기 (보기 — 고급 편집기), 우리는 빈 요청의 전체 M 코드를 지우고 슈퍼 기능의 코드를 거기에 붙여넣습니다.
손 조심:
첫 번째 줄에서 우리는 함수가 세 개의 텍스트 인수를 가질 것이라고 말합니다. TXT – 분석 중인 원본 텍스트, 정규식 – 정규 표현식 패턴, 탈림 — 결과를 표시하기 위한 구분 문자.
다음으로 우리는 함수를 호출합니다 웹 페이지, 인수에서 위에서 설명한 JavaScript 코드를 형성합니다. 변수 인수를 코드에 붙여넣고 대체합니다.
파편:
[데이터]{0}[어린이]{0}[어린이]{1}[텍스트]{0}
... 필요한 결과가 있는 테이블에 "빠져들어가기" 위해 필요합니다. 요점은 그 기능이 웹 페이지 결과적으로 웹 페이지의 구조를 반복하는 여러 중첩 테이블을 생성합니다. 이 M 코드가 없으면 함수는 다음을 출력합니다.
... 단어를 여러 번 클릭해야 합니다. 작업대, 열의 하위 중첩 테이블로 연속적으로 "떨어짐" 어린이:
이 모든 인용 대신에 우리는 함수 코드에서 테이블과 열(본문) 필요합니다.
사실 여기에 모든 비밀이 있습니다. 버튼을 누르는 것만 남았습니다 마감재 창에서 고급 편집기, 우리가 코드를 삽입한 곳에서 가장 맛있는 것으로 진행할 수 있습니다. 직장에서 우리의 기능을 사용해 보세요.
다음은 몇 가지 시드 예입니다.
예 1. 지불 설명에서 계좌 번호 및 날짜 검색
지불에 대한 설명(목적)이 포함된 은행 거래 명세서가 있습니다. 여기서 지불된 인보이스의 번호와 날짜를 별도의 열로 가져와야 합니다.
다음을 통해 표준 방식으로 테이블을 파워 쿼리에 로드합니다. 데이터 – 테이블/범위에서 (데이터 — T에서가능/R천사).
그런 다음 다음을 통해 함수와 함께 계산된 열을 추가합니다. 열 추가 – 사용자 정의 함수 호출 (열 추가 - 사용자 정의 함수 호출) 인수를 입력하십시오.
정규식(인수 정규식) 우리가 사용하는 템플릿:
(ㄹ{3,5}|d{2}.d{2}.d{4})
... 인간의 언어로 번역된 의미:
3~5자리 숫자(계좌번호)
or
“2비트 숫자 – 포인트 – 2비트 숫자 – 포인트 – 4비트 숫자” 형식의 조각, 즉 DD.MM.YYYY 형식의 날짜입니다.
구분 문자로(인수 탈림) 세미콜론을 입력합니다.
클릭 후 OK 매직 기능은 정규 표현식에 따라 모든 초기 데이터를 분석하고 송장에서 찾은 번호와 날짜로 열을 형성합니다.
명령을 사용하여 세미콜론으로 구분해야 합니다. 홈 — 열 분할 — 구분 기호별 (홈 — 열 분할 — 구분 기호로) 그리고 우리는 우리가 원하는 것을 얻습니다:
아름다움!
예 2: 텍스트에서 이메일 주소 추출
초기 데이터로 다음 테이블이 있다고 가정합니다.
... 거기에서 찾은 이메일 주소를 가져와야 합니다(명확성을 위해 텍스트에서 빨간색으로 강조 표시했습니다).
이전 예에서와 같이 다음을 통해 표준 방식으로 테이블을 파워 쿼리에 로드합니다. 데이터 – 테이블/범위에서 (데이터 — T에서가능/R천사).
그런 다음 다음을 통해 함수와 함께 계산된 열을 추가합니다. 열 추가 – 사용자 정의 함수 호출 (열 추가 - 사용자 정의 함수 호출) 인수를 입력하십시오.
이메일 주소를 파싱하는 것은 더 어려운 작업이며 이를 해결하기 위한 다양한 수준의 악몽의 정규 표현식이 많이 있습니다. 나는 이상적인 것은 아니지만 대부분의 경우에 아주 잘 작동하는 간단한 옵션 중 하나를 사용했습니다.
[w|.|-]*@w*.[w|.]*
구분자로(탈림) 세미콜론과 공백을 입력할 수 있습니다.
를 클릭하십시오 OK 원본 텍스트 "porridge"에서 추출한 전자 메일 주소가 포함된 열을 얻습니다.
마법!
PS
"이보다 더 좋을 수 없는 좋은 것은 없다"라는 말이 있듯이. 파워 쿼리는 그 자체로 훌륭하고 정규식과 결합될 때 모든 텍스트 데이터를 처리하는 데 있어 완전히 비현실적인 기능과 유연성을 제공합니다. Microsoft가 언젠가 Power Query 및 Power BI 업데이트에 RegExp 지원을 추가하고 탬버린과 함께 위의 모든 춤이 과거의 일이 되기를 바랍니다. 지금은 그렇습니다.
또한 온라인 편집기에서 바로 https://regexr.com/ 사이트에서 정규 표현식을 사용하는 것이 편리하다고 덧붙이고 싶습니다. 거기 섹션에 커뮤니티 패턴 모든 경우에 사용할 수 있는 수많은 정규 시즌이 준비되어 있습니다. 실험 – 이제 파워 쿼리에서 정규식의 모든 기능을 사용할 수 있습니다!
- 정규식(RegExp)이란 무엇이며 Excel에서 사용하는 방법
- 파워 쿼리의 퍼지 텍스트 검색
- 파워 쿼리를 사용하여 다른 파일에서 테이블 어셈블