List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

참조 목록에 따라 텍스트를 수식으로 신속하고 대량으로 대체하는 방법 – 우리는 이미 분류했습니다. 이제 파워 쿼리에서 시도해 보겠습니다.

자주 발생하듯이 수행 이 작업은 설명하는 것보다 훨씬 쉽습니다. why 작동하지만 둘 다 해보자 🙂

따라서 키보드 단축키를 사용하여 일반 범위에서 생성된 두 개의 "스마트" 동적 테이블이 있습니다. Ctrl 키+T 또는 팀 홈 – 테이블 형식 (홈 — 표 형식):

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

나는 첫 번째 테이블에 전화를 걸었다. Data, 두 번째 테이블 - 명부 담당사용 분야 테이블 이름 (테이블 이름) 탭 건설자 (디자인).

작업: 테이블의 주소 바꾸기 Data 열의 모든 발생 찾다 안내서 열에서 해당하는 올바른 대응물에 대용품. 셀의 나머지 텍스트는 그대로 유지해야 합니다.

1단계. 디렉터리를 파워 쿼리에 로드하고 목록으로 변환

활성 셀을 참조 테이블의 임의 위치로 설정한 후 탭을 클릭합니다. Data (데이트)또는 탭에서 파워 쿼리 (이전 버전의 Excel이 있고 별도의 탭에 추가 기능으로 파워 쿼리를 설치한 경우) 단추 테이블/레인지에서 (표/범위에서).

참조 테이블이 파워 쿼리 쿼리 편집기에 로드됩니다.

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

방해하지 않도록 자동으로 추가된 단계 수정된 유형 (변경된 유형) 오른쪽 패널에서는 적용된 단계를 안전하게 삭제할 수 있으며 단계만 남게 됩니다. 출처 (출처):

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

이제 추가 변환 및 교체를 수행하려면 이 테이블을 목록(목록)으로 변환해야 합니다.

서정적 탈선

계속하기 전에 먼저 용어를 이해하겠습니다. 파워 쿼리는 다음과 같은 여러 유형의 개체에서 작동할 수 있습니다.
  • 작업대 여러 행과 열로 구성된 XNUMX차원 배열입니다.
  • 녹음(녹음) – 이름이 있는 여러 필드 요소로 구성된 XNUMX차원 배열 문자열, 예를 들어 [이름 = "마샤", 성별 = "에프", 나이 = 25]
  • 명부 – 예를 들어 여러 요소로 구성된 XNUMX차원 배열 열 {1, 2, 3, 10, 42} or { "믿음 희망 사랑" }

문제를 해결하기 위해 주로 유형에 관심이 있습니다. 명부.

여기서 트릭은 파워 쿼리의 목록 항목이 평범한 숫자나 텍스트일 뿐만 아니라 다른 목록이나 레코드일 수도 있다는 것입니다. 우리가 디렉토리를 돌려야 하는 레코드(레코드)로 구성된 까다로운 목록(목록)에 있습니다. 파워 쿼리 구문 표기법(대괄호 안의 항목, 중괄호 안의 목록)은 다음과 같습니다.

{

    [ 찾기 = “성. Petersburg", Replace = "St. 상트페테르부르크”] ,

    [ 찾기 = “성. Petersburg", Replace = "St. 상트페테르부르크”] ,

    [ 찾기 = "Peter", 바꾸기 = "St. 상트페테르부르크”] ,

등

}

이러한 변환은 파워 쿼리에 내장된 M 언어의 특수 기능을 사용하여 수행됩니다. Table.ToRecords. 수식 입력줄에 직접 적용하려면 이 함수를 거기에 있는 단계 코드에 추가하세요. 출처.

그것은 :

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

후 :

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

Table.ToRecords 함수를 추가하면 테이블의 모양이 변경되어 레코드 목록으로 바뀝니다. 개별 레코드의 내용은 단어 옆에 있는 셀 배경을 클릭하여 보기 창 하단에서 볼 수 있습니다. 기록 (하지만 한 마디로!)

위의 내용 외에도 생성된 목록을 캐시(버퍼)하기 위해 스트로크를 하나 더 추가하는 것이 좋습니다. 이렇게 하면 파워 쿼리가 조회 목록을 메모리에 한 번 로드하고 나중에 교체하기 위해 액세스할 때 다시 계산하지 않습니다. 이렇게 하려면 공식을 다른 함수로 래핑하십시오. 목록.버퍼:

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

이러한 캐싱은 지워야 할 많은 양의 초기 데이터와 함께 속도가 몇 배나 눈에 띄게 빨라집니다.

이것으로 핸드북 작성이 완료되었습니다.

클릭하는 것이 남아 있습니다. 홈 – 닫기 및 로드 – 닫기 및 로드… (홈 — 닫기&로드 — 닫기&로드..), 옵션을 선택하세요 연결만 만드세요 (연결만 생성) 그리고 엑셀로 돌아간다.

2단계. 데이터 테이블 로드

여기에서는 모든 것이 진부합니다. 이전 참고 도서와 마찬가지로 테이블의 아무 곳이나 가서 탭을 클릭합니다. Data 단추 테이블/레인지에서 그리고 우리 테이블 Data 파워 쿼리에 들어갑니다. 자동으로 추가된 단계 수정된 유형 (변경된 유형) 다음을 제거할 수도 있습니다.

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

특별한 준비 작업이 필요하지 않으며 가장 중요한 작업으로 넘어갑니다.

3단계. List.Accumulate 기능을 사용하여 교체 수행

명령을 사용하여 데이터 테이블에 계산된 열을 추가해 보겠습니다. 열 추가 – 사용자 정의 열 (열 추가 — 사용자 정의 열): 그리고 열리는 창에 추가된 열의 이름을 입력합니다(예: 수정된 주소) 및 우리의 마법 기능 목록.누적:

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

클릭하는 것이 남아 있습니다. OK – 그리고 교체가 이루어진 열을 얻습니다.

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

참고 사항 :

  • 파워 쿼리는 대/소문자를 구분하므로 디렉터리에 "SPb"가 아니라 "SPb"가 있으므로 끝에서 두 번째 줄에는 대체 항목이 없습니다.
  • 소스 데이터에 한 번에 여러 하위 문자열을 대체해야 하는 경우(예: 7번째 줄에서 "S-Pb"와 "Prospectus"를 모두 바꿔야 함), 이는 문제를 일으키지 않습니다( 이전 방법).
  • 원본 텍스트(9번째 줄)에 대체할 것이 없으면 오류가 발생하지 않습니다(다시, 수식으로 대체).

그러한 요청의 속도는 매우 적절합니다. 예를 들어 크기가 5000행인 초기 데이터 테이블의 경우 이 쿼리는 3초 미만(버퍼링 없이는 약 XNUMX초)에 업데이트되었습니다.

List.Accumulate 함수의 작동 방식

원칙적으로 이것이 이 기사의 끝이 될 수 있습니다. 당신이 할 수 있을 뿐만 아니라 그것이 "under the hood" 어떻게 작동하는지 이해하기를 원한다면, 당신은 토끼 구멍으로 조금 더 깊이 뛰어들어 List.Accumulate 함수를 다루어야 할 것입니다. 이것은 모든 대량 교체를 수행했습니다. 우리를 위해 일하십시오.

이 함수의 구문은 다음과 같습니다.

=목록.누적(명부, 씨, 축 압기)

어디에

  • 명부 요소가 반복되는 목록입니다. 
  • 씨 – 초기 상태
  • 축 압기 – 목록의 다음 요소에 대해 일부 연산(수학, 텍스트 등)을 수행하고 처리 결과를 특수 변수에 누적하는 함수입니다.

일반적으로 파워 쿼리에서 함수를 작성하는 구문은 다음과 같습니다.

(argument1, argument2, …argumentN) => 인수가 있는 일부 작업

예를 들어, 합산 함수는 다음과 같이 나타낼 수 있습니다.

(a, b) => a + b

List.Accumulate 의 경우 이 누산기 함수에는 두 개의 필수 인수가 있습니다(이름은 무엇이든 지정할 수 있지만 일반적인 이름은 다음과 같습니다. 상태 и current, 이 기능에 대한 공식 도움말에서와 같이, 여기서:

  • 상태 – 결과가 누적되는 변수(초기값은 위에서 언급한 값입니다. 씨)
  • current – 목록에서 다음 반복 값 명부

예를 들어 다음 구성의 논리 단계를 살펴보겠습니다.

=목록.누적({3, 2, 5}, 10, (상태, 현재) => 상태 + 현재)

  1. 가변 값 상태 초기 인수와 동일하게 설정됩니다. 씨Ie 상태 = 10
  2. 목록의 첫 번째 요소(현재 = 3) 변수에 추가 상태 (십). 우리는 얻는다 상태 = 13.
  3. 목록의 두 번째 요소(현재 = 2) 및 변수의 현재 누적 값에 더하기 상태 (십). 우리는 얻는다 상태 = 15.
  4. 우리는 목록의 세 번째 요소(현재 = 5) 및 변수의 현재 누적 값에 더하기 상태 (십). 우리는 얻는다 상태 = 20.

이것은 가장 최근에 축적된 상태 값은 List.Accumulate 함수이며 결과로 출력됩니다.

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

약간의 환상을 가지고 있으면 List.Accumulate 함수를 사용하여 예를 들어 Excel 함수 CONCATENATE를 시뮬레이션할 수 있습니다(파워 쿼리에서는 해당 아날로그가 텍스트.결합) 표현식 사용:

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

또는 최대값을 검색할 수도 있습니다(Power Query에서 호출되는 Excel의 MAX 함수 모방 목록.최대):

List.Accumulate 함수를 사용하여 파워 쿼리에서 대량 텍스트 대체

그러나 List.Accumulate의 주요 기능은 단순한 텍스트 또는 숫자 목록을 인수로 처리할 뿐만 아니라 더 복잡한 개체(예: 목록에서 목록 또는 레코드에서 목록(hello, Directory!))를 처리하는 기능입니다.

문제에서 교체를 수행한 구성을 다시 살펴보겠습니다.

목록.누적(명부 담당, [주소], (상태, 현재) => Text.Replace(상태, 현재[찾기], 현재[바꾸기]) )

여기에서 과연 무슨 일이 벌어지고 있는 걸까요?

  1. 초기 값으로 (씨) 열에서 첫 번째 서투른 텍스트를 가져옵니다. [주소] 우리 테이블: 199034, St. Petersburg, str. 베링가, d. 1
  2. 그런 다음 List.Accumulate는 목록의 요소를 하나씩 반복합니다. 안내서. 이 목록의 각 요소는 "찾을 대상 - 대체할 대상" 필드 쌍, 즉 디렉토리의 다음 행으로 구성된 레코드입니다.
  3. 누산기 함수는 변수에 넣습니다. 상태 초기 값(첫 번째 주소 199034, St. Petersburg, str. 베링가, d. 1) 및 그에 대한 누산기 기능을 수행합니다. 표준 M 기능을 사용하여 교체 작업을 수행합니다. 텍스트.바꾸기 (Excel의 SUBSTITUTE 함수와 유사). 구문은 다음과 같습니다.

    Text.Replace(원문, 찾고 있는 것, 대체할 것)

    그리고 여기에 우리가 있습니다:

    • 상태 우리의 더러운 주소입니다. 상태 (에서 거기에 도착 씨)
    • 현재[검색] – 필드 값 찾다 목록의 다음 반복 항목에서 명부 담당, 변수에 있는 current
    • 현재[교체] – 필드 값 대용품 목록의 다음 반복 항목에서 명부 담당에 누워 current

따라서 각 주소에 대해 디렉터리에 있는 모든 줄의 전체 열거 주기가 매번 실행되어 [찾기] 필드의 텍스트를 [바꾸기] 필드의 값으로 바꿉니다.

당신이 아이디어를 얻었기를 바랍니다 🙂

  • 수식을 사용하여 목록의 텍스트 대량 바꾸기
  • 파워 쿼리의 정규식(RegExp)

댓글을 남겨주세요.