Substitución de texto masivo en Power Query coa función List.Acumulate

Como substituír de forma rápida e masiva o texto segundo a lista de referencias con fórmulas: xa o solucionamos. Agora imos tentar facelo en Power Query.

Como adoita pasar realizar esta tarefa é moito máis fácil que explicar por que funciona, pero imos tentar facer as dúas cousas 🙂

Polo tanto, temos dúas táboas dinámicas "intelixentes" creadas a partir de rangos ordinarios cun atallo de teclado Ctrl+T ou equipo Inicio - Formato como unha táboa (Inicio — Formato como táboa):

Chamei á primeira mesa Data, a segunda mesa - guíausando campo Nome da táboa (Nome da táboa) aba Construtor (Deseño).

Tarefa: substituír os enderezos da táboa Data todas as ocorrencias dunha columna Atopar Manual ás súas correspondentes contrapartes correctas da columna Suplente. O resto do texto das celas debe permanecer intacto.

Paso 1. Cargue o directorio en Power Query e convérteo nunha lista

Despois de establecer a cela activa en calquera lugar da táboa de referencia, fai clic na pestana Data (Data)ou na pestana Consulta de enerxía (se tes unha versión antiga de Excel e instalaches Power Query como complemento nunha pestana separada) no botón Da táboa/rango (De mesa/rango).

A táboa de referencia cargarase no editor de consultas de Power Query:

Para non interferir, un paso engadido automaticamente tipo modificado (Tipo modificado) no panel dereito, os pasos aplicados pódense eliminar con seguridade, deixando só o paso fonte (Fonte):

Agora, para realizar máis transformacións e substitucións, necesitamos converter esta táboa nunha lista (lista).

Digresión lírica

• Táboa é unha matriz bidimensional formada por varias filas e columnas.
• Gravar (Rexistrar) – Array-string unidimensional, que consta de varios campos-elementos con nomes, por exemplo [Nome = "Masha", Sexo = "f", Idade = 25]
  
• lista – unha matriz-columna unidimensional, formada por varios elementos, por exemplo {1, 2, 3, 10, 42} or { "Faith Hope Love" }
  

Para resolver o noso problema, interesarémonos principalmente no tipo lista.

O truco aquí é que os elementos da lista en Power Query poden ser non só números ou texto banais, senón tamén outras listas ou rexistros. É nunha lista (lista) tan complicada, formada por rexistros (rexistros) que necesitamos converter o noso directorio. Na notación sintáctica de Power Query (entradas entre corchetes, listas entre corchetes) isto sería así:

{

    [ Buscar = “St. Petersburg", Substituír = "St. Petersburgo”] ,

    [ Buscar = “St. Petersburg", Substituír = "St. Petersburgo”] ,

    [ Buscar = "Pedro", Substituír = "St. Petersburgo”] ,

etc.

}

Tal transformación realízase mediante unha función especial da linguaxe M integrada en Power Query: Táboa.ToRecords. Para aplicalo directamente na barra de fórmulas, engade esta función ao código de paso alí fonte.

Foi:

Despois:

Despois de engadir a función Table.ToRecords, a aparencia da nosa táboa cambiará: converterase nunha lista de rexistros. O contido dos rexistros individuais pódese ver na parte inferior do panel de visualización facendo clic no fondo da cela xunto a calquera palabra rexistro (pero non nunha soa palabra!)

Ademais do anterior, ten sentido engadir un trazo máis: para almacenar na memoria caché (búfer) a nosa lista creada. Isto obrigará a Power Query a cargar a nosa lista de busca unha vez na memoria e non a volver calcular cando máis tarde accedamos a ela para substituíla. Para iso, envolve a nosa fórmula noutra función: Lista.Buffer:

Este almacenamento en caché dará un aumento moi notable da velocidade (en varias veces!) cunha gran cantidade de datos iniciais que se borrarán.

Deste xeito complétase a elaboración do manual.

Queda por premer Inicio – Pechar e cargar – Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en..), seleccione unha opción Só ten que crear unha conexión (Só crear conexión) e volver a Excel.

Paso 2. Cargando a táboa de datos

Aquí todo é trillado. Como antes co libro de consulta, subimos a calquera lugar da táboa, prememos na pestana Data botón De Táboa/Range e a nosa mesa Data entra en Power Query. Paso engadido automaticamente tipo modificado (Tipo modificado) tamén pode eliminar:

Non son necesarias accións preparatorias especiais para facer con el, e pasamos ao máis importante.

Paso 3. Realiza substitucións mediante a función List.Acumular

Engademos unha columna calculada á nosa táboa de datos usando o comando Engadir unha columna - Columna personalizada (Engadir columna — Columna personalizada): e introduza o nome da columna engadida na xanela que se abre (por exemplo, enderezo corrixido) e a nosa función máxica Listar.Acumular:

Queda por premer OK – e obtemos unha columna coas substitucións realizadas:

Teña en conta que:

• Dado que Power Query distingue entre maiúsculas e minúsculas, non houbo substitución na penúltima liña, porque no directorio temos "SPb", non "SPb".
• Se hai varias subcadeas para substituír á vez nos datos de orixe (por exemplo, na sétima liña cómpre substituír tanto "S-Pb" como "Prospectus"), isto non xera ningún problema (a diferenza de substituír con fórmulas de método anterior).
• Se non hai nada que substituír no texto fonte (9ª liña), non se produce ningún erro (a diferenza, de novo, coa substitución por fórmulas).

A velocidade de tal solicitude é moi, moi decente. Por exemplo, para unha táboa de datos iniciais cun tamaño de 5000 filas, esta consulta actualizouse en menos dun segundo (sen almacenar no búfer, por certo, uns 3 segundos!)

Como funciona a función List.Acumular

En principio, este podería ser o final (para que eu escriba, e que ti leas) este artigo. Se queres non só poder, senón tamén comprender como funciona "baixo o capó", entón terás que mergullar un pouco máis na madriguera do coello e xestionar a función List.Acumulate, que fixo toda a substitución masiva. traballa para nós.

A sintaxe desta función é:

=Lista.Acumular(lista, semente, acumulador)

onde

• lista é a lista sobre cuxos elementos estamos iterando. 
• semente - estado inicial
• acumulador – unha función que realiza algunha operación (matemática, de texto, etc.) sobre o seguinte elemento da lista e acumula o resultado do procesamento nunha variable especial.
  

En xeral, a sintaxe para escribir funcións en Power Query ten o seguinte aspecto:

(argumento1, argumento2, … argumentoN) => algunhas accións con argumentos

Por exemplo, a función de suma pode representarse como:

(a, b) => a + b

Para List.Acumulate , esta función acumuladora ten dous argumentos obrigatorios (pódense chamar calquera cousa, pero os nomes habituais son foron и actual, como na axuda oficial para esta función, onde:

• foron – unha variable onde se acumula o resultado (o seu valor inicial é o mencionado anteriormente semente)
  
• actual – o seguinte valor iterado da lista lista
  

Por exemplo, vexamos os pasos da lóxica da seguinte construción:

=Lista.Acumular({3, 2, 5}, 10, (estado, actual) => estado + actual)

Este é o último acumulado foron o valor é a nosa Lista.Acumula función e resultados como resultado:

Se fantaseas un pouco, entón usando a función Lista.Acumular, podes simular, por exemplo, a función de Excel CONCATENAR (en Power Query, o seu análogo chámase Texto.Combinar) utilizando a expresión:

Ou mesmo buscar o valor máximo (imitación da función MAX de Excel, que en Power Query se chama Lista.Máx):

Non obstante, a principal característica de List.Accumulate é a capacidade de procesar non só texto simple ou listas numéricas como argumentos, senón tamén obxectos máis complexos, por exemplo, listas-de-listas ou listas-de-rexistros (¡ola, Directorio!)

Vexamos de novo a construción que realizou a substitución no noso problema:

Lista.Acumular(guía, [Enderezo], (estado,actual) => Texto.Substituír(estado, actual[Buscar], actual[Substituír]) )

Que está pasando realmente aquí?

1. Como valor inicial (semente) collemos o primeiro texto torpe da columna [Enderezo] a nosa mesa: 199034, San Petersburgo, str. Beringa, d. 1
2. Entón List.Acumulate itera sobre os elementos da lista un por un - Manual. Cada elemento desta lista é un rexistro composto por un par de campos "Que atopar - Que substituír" ou, noutras palabras, a seguinte liña do directorio.
3. A función acumulador pon nunha variable foron valor inicial (primeiro enderezo 199034, San Petersburgo, str. Beringa, d. 1) e realiza nela unha función de acumulador: a operación de substitución mediante a función M estándar Texto. Substituír (análogo á función SUBSTITUTE de Excel). A súa sintaxe é:

   Text.Replace (texto orixinal, o que estamos a buscar, o que estamos a substituír)

   e aquí temos:

• foron é o noso enderezo sucio, que se atopa en foron (chegando alí dende semente)
  
• actual[Buscar] - valor de campo Atopar a partir da seguinte entrada iterada da lista guía, que reside na variable actual
• actual[substituír] - valor de campo Suplente a partir da seguinte entrada iterada da lista guíadeitado actual

Así, para cada enderezo, execútase cada vez un ciclo completo de enumeración de todas as liñas do directorio, substituíndo o texto do campo [Buscar] polo valor do campo [Substituír].

Espero que teñades a idea 🙂

• Substitúe o texto en masa nunha lista mediante fórmulas
  
• Expresións regulares (RegExp) en Power Query