Substituição de texto em massa no Power Query com função List.Accumulate

Como substituir rapidamente e em massa o texto de acordo com a lista de referência por fórmulas - já resolvemos isso. Agora vamos tentar fazer isso no Power Query.

Como muitas vezes acontece realizar esta tarefa é muito mais fácil do que explicar porque funciona, mas vamos tentar fazer os dois 🙂

Então, temos duas tabelas dinâmicas “inteligentes” criadas a partir de intervalos comuns com um atalho de teclado Ctrl+T ou equipe Home – Formatar como uma tabela (Início — Formatar como Tabela):

Liguei para a primeira mesa Data, a segunda tabela – Diretóriousando campo Nome da mesa (Nome da tabela) aba Construtor (Desenhar).

Tarefa: substituir em endereços na tabela Data todas as ocorrências de uma coluna Para encontrar Manual para suas contrapartes corretas correspondentes da coluna Substituto. O restante do texto nas células deve permanecer intocado.

Etapa 1. Carregar o diretório no Power Query e transformá-lo em uma lista

Tendo definido a célula ativa para qualquer lugar na tabela de referência, clique na guia Data (Encontro: Data)ou na aba Consulta de energia (se você tiver uma versão antiga do Excel e instalou o Power Query como um suplemento em uma guia separada) no botão Da tabela/intervalo (Da tabela/intervalo).

A tabela de referência será carregada no editor de consultas do Power Query:

Para não interferir, uma etapa adicionada automaticamente tipo modificado (Tipo Alterado) no painel direito, as etapas aplicadas podem ser excluídas com segurança, deixando apenas a etapa fonte (Fonte):

Agora, para realizar mais transformações e substituições, precisamos transformar essa tabela em uma lista (lista).

Digressão lírica

• mesa é uma matriz bidimensional que consiste em várias linhas e colunas.
• Gravar (Gravar) – array-string unidimensional, consistindo em vários elementos de campos com nomes, por exemplo [Nome = “Masha”, Gênero = “f”, Idade = 25]
  
• Lista – uma coluna de matriz unidimensional, consistindo em vários elementos, por exemplo {1, 2, 3, 10, 42} or { "Fé esperança amor" }
  

Para resolver nosso problema, estaremos principalmente interessados ​​no tipo Lista.

O truque aqui é que os itens de lista no Power Query podem ser não apenas números ou texto banais, mas também outras listas ou registros. É em uma lista tão complicada (list), composta por registros (registros) que precisamos transformar nosso diretório. Na notação sintática do Power Query (entradas entre colchetes, listas entre colchetes), ficaria assim:

{

    [ Encontrar = “St. Petersburgo”, Substituir = “S. Petersburgo” ] ,

    [ Encontrar = “St. Petersburgo”, Substituir = “S. Petersburgo” ] ,

    [ Localizar = “Pedro”, Substituir = “St. Petersburgo” ] ,

etc.

}

Essa transformação é realizada usando uma função especial da linguagem M incorporada ao Power Query – Tabela.ParaRegistros. Para aplicá-lo diretamente na barra de fórmulas, adicione esta função ao código da etapa lá fonte.

Isso foi:

Depois:

Após adicionar a função Table.ToRecords, a aparência da nossa tabela mudará – ela se transformará em uma lista de registros. O conteúdo de registros individuais pode ser visto na parte inferior do painel de visualização clicando no fundo da célula ao lado de qualquer palavra Registro (mas não em uma única palavra!)

Além do acima, faz sentido adicionar mais um traço – para armazenar em cache (buffer) nossa lista criada. Isso forçará o Power Query a carregar nossa lista de pesquisa uma vez na memória e não a recalcular novamente quando a acessarmos posteriormente para substituí-la. Para fazer isso, envolva nossa fórmula em outra função – Lista.Buffer:

Esse cache dará um aumento muito perceptível na velocidade (várias vezes!) com uma grande quantidade de dados iniciais a serem limpos.

Isso completa a preparação do manual.

Resta clicar em Home – Fechar e carregar – Fechar e carregar para… (Início — Fechar e carregar — Fechar e carregar para..), selecione uma opção Basta criar uma conexão (Apenas criar conexão) e retorne ao Excel.

Etapa 2. Carregando a tabela de dados

Tudo é banal aqui. Como antes com o livro de referência, chegamos a qualquer lugar da mesa, clique na guia Data botão Da tabela/intervalo e nossa mesa Data entra no Power Query. Etapa adicionada automaticamente tipo modificado (Tipo Alterado) você também pode remover:

Não são necessárias ações preparatórias especiais com ele, e passamos para o mais importante.

Etapa 3. Faça substituições usando a função List.Accumulate

Vamos adicionar uma coluna calculada à nossa tabela de dados usando o comando Adicionando uma Coluna – Coluna Personalizada (Adicionar coluna — coluna personalizada): e digite o nome da coluna adicionada na janela que se abre (por exemplo, endereço corrigido) e nossa função mágica Listar.Acumular:

Resta clicar em OK – e obtemos uma coluna com as substituições feitas:

Observe que:

• Como o Power Query diferencia maiúsculas de minúsculas, não houve substituição na penúltima linha, pois no diretório temos “SPb”, não “SPb”.
• Se houver várias substrings para substituir de uma só vez nos dados de origem (por exemplo, na 7ª linha você precisa substituir "S-Pb" e "Prospecto"), isso não cria problemas (ao contrário de substituir por fórmulas de o método anterior).
• Se não houver nada para substituir no texto de origem (9ª linha), não ocorrerão erros (ao contrário, novamente, da substituição por fórmulas).

A velocidade de tal pedido é muito, muito decente. Por exemplo, para uma tabela de dados iniciais com tamanho de 5000 linhas, essa consulta foi atualizada em menos de um segundo (sem buffer, aliás, cerca de 3 segundos!)

Como funciona a função List.Accumulate

Em princípio, este poderia ser o fim (para eu escrever e para você ler) este artigo. Se você quiser não apenas ser capaz, mas também entender como funciona “sob o capô”, então você terá que mergulhar um pouco mais fundo na toca do coelho e lidar com a função List.Accumulate, que fez toda a substituição em massa trabalhar para nós.

A sintaxe para esta função é:

=Lista.Acumular(Lista, semente, acumulador)

onde

• Lista é a lista cujos elementos estamos iterando. 
• semente - Estado inicial
• acumulador – uma função que realiza alguma operação (matemática, texto, etc.) no próximo elemento da lista e acumula o resultado do processamento em uma variável especial.
  

Em geral, a sintaxe para escrever funções no Power Query é assim:

(argumento1, argumento2, … argumentoN) => algumas ações com argumentos

Por exemplo, a função de soma pode ser representada como:

(a, b) => a + b

Para List.Accumulate , esta função de acumulador tem dois argumentos obrigatórios (eles podem ter qualquer nome, mas os nomes usuais são estado и atual, como na ajuda oficial para esta função, onde:

• estado – uma variável onde o resultado é acumulado (seu valor inicial é o mencionado acima semente)
  
• atual – o próximo valor iterado da lista Lista
  

Por exemplo, vamos dar uma olhada nos passos da lógica da seguinte construção:

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

Este é o último acumulado estado o valor é nossa função List.Accumulate e gera como resultado:

Se você fantasiar um pouco, então usando a função List.Accumulate, você pode simular, por exemplo, a função do Excel CONCATENATE (no Power Query, seu análogo é chamado Texto. Combinar) usando a expressão:

Ou até mesmo pesquisar o valor máximo (imitação da função MAX do Excel, que no Power Query é chamada Lista.Max):

No entanto, o principal recurso do List.Accumulate é a capacidade de processar não apenas texto simples ou listas numéricas como argumentos, mas objetos mais complexos – por exemplo, listas de listas ou listas de registros (olá, Diretório!)

Vejamos novamente a construção que realizou a substituição em nosso problema:

Listar.Acumular(Diretório, [Endereço], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

O que realmente está acontecendo aqui?

1. Como valor inicial (semente) pegamos o primeiro texto desajeitado da coluna [Endereço] nossa mesa: 199034, São Petersburgo, str. Beringa, D. 1
2. Então List.Accumulate itera sobre os elementos da lista um por um – Manual. Cada elemento desta lista é um registro composto por um par de campos “O que encontrar – O que substituir” ou, em outras palavras, a próxima linha no diretório.
3. A função de acumulador coloca em uma variável estado valor inicial (primeiro endereço 199034, São Petersburgo, str. Beringa, D. 1) e executa uma função de acumulador nele - a operação de substituição usando a função M padrão Texto.Substituir (análogo à função SUBSTITUIR do Excel). Sua sintaxe é:

   Text.Replace( texto original, o que estamos procurando, o que estamos substituindo )

   e aqui temos:

• estado é o nosso endereço sujo, que fica em estado (chegar lá de semente)
  
• atual[Pesquisar] - valor do campo Para encontrar da próxima entrada iterada da lista Diretório, que está na variável atual
• atual[Substituir] - valor do campo Substituto da próxima entrada iterada da lista DiretórioDeitando atual

Assim, para cada endereço, um ciclo completo de enumeração de todas as linhas do diretório é executado a cada vez, substituindo o texto do campo [Localizar] pelo valor do campo [Substituir].

Espero que tenha entendido 🙂

• Substituir texto em massa em uma lista usando fórmulas
  
• Expressões Regulares (RegExp) no Power Query