Função Recursiva: Substituição de Múltiplas Palavras no Power Query

A linguagem M é uma linguagem muito poderosa para transformação de dados, seja no Excel ou no Power BI. Porém, a inexperiência dos usuários e a familiaridade com a linguagem fazem com que muitas transformações pareçam impossíveis de se realizar.

Neste artigo, escolhi um tema que é muito solicitado, e o último pedido veio do meu amigo Eduardo Medeiros (a.k.a. Baralhão). Na tentativa, vejo muita gente fazendo um monte de gambiarra para substituir várias palavras de uma vez no Power Query. Criam várias etapas desnecessárias e por final uma consulta completamente desestruturada gerando perda de performance.

Neste artigo vamos criar uma função recursiva para fazer essa substituição múltipla utilizando uma tabela de correspondências (De → Para).

Se você quiser, pode fazer o download do arquivo com o código no formulário ao lado.

Criando a tabela De → Para

Para começarmos, no Power Query vamos criar ou importar uma tabela de duas colunas, onde na primeira coluna estarão as palavras que desejamos substituir e na segunda coluna as palavras pelas quais serão substituídas. Vamos dar o nome DePara para esta tabela.

Uso de uma Função Personalizada

A linguagem M possui diversas funções buit-in que nos auxiliam no tratamento de dados, porém muitas vezes elas não são suficientes para o tratamento que desejamos fazer. Nestes casos podemos criar funções personalizadas. Uma função personalizada é muito útil quando desejamos encapsular códigos de tratamento de dados na linguagem M, invocando todos os passos através de apenas um comando.

Neste passo a ideia é criar uma função que possamos invocá-la passando apenas o texto que desejamos verificar as substituições, e o resultado será o texto com todas as palavras contidas na nossa tabela de correspondências já substituídas.

Usando a tabela acima seria algo mais ou menos assim:

= fnSubstituirDatab(“O produto foi enviado de MG para o PR ontem”) 

e o resultado será: O produto foi enviado de Minas Gerais para o Paraná ontem

Criando Uma Função Personalizada

Para criamos a nossa função iremos criar uma nova consulta nula. O código da função está disponível abaixo e irei explicar em blocos.

let
    fnSubstituirDatab = (Texto as text, optional Pos as nullable number) =>
    let
     NovaPos = if Pos = null then 0 else Pos, 
     TextoSubstituido = Text.Replace(Texto, DePara[De]{NovaPos}, DePara[Para]{NovaPos})
    in
      if NovaPos = List.Count(DePara[De]) - 1 
      then 
          TextoSubstituido 
      else 
          @fnSubstituirDatab(TextoSubstituido, NovaPos + 1)
in
    fnSubstituirDatab

Explicação

A grande sacada desta nossa função será criar um loop passando por todos os elementos da nossa tabela DePara, fazendo uma substituição por vez.

Por exemplo, na tabela que importamos existem 5 substituições possíveis. Faremos então 5 iterações no texto que desejamos fazer a substituição, tentando fazer as substituições quando possível.

Exemplo:

Entrada: O produto foi enviado de MG para o PR ontem
Iteração 1 (MG → Minas Gerais) : O produto foi enviado de Minas Gerais para o PR ontem
Iteração 2 (SP → São Paulo): O produto foi enviado de Minas Gerais para o PR ontem
Iteração 3 (RJ → Rio de Janeiro): O produto foi enviado de Minas Gerais para o PR ontem
Iteração 4 (PR → Paraná): O produto foi enviado de Minas Gerais para o Paraná ontem
Iteração 5 (SC → Santa Catarina): O produto foi enviado de Minas Gerais para o Paraná ontem
Saída: O produto foi enviado de Minas Gerais para o Paraná ontem

Para conseguirmos o efeito de loop acima será necessário utilizar o conceito de recursividade que irei falar um pouco mais a frente neste artigo.

Agora vamos começar pelo início.

A estrutura básica de qualquer código na linguagem M são os blocos let … in. Após a expressão let informamos todas as nossas variáveis (etapas de transformação) e ao final, informamos no bloco in o resultado que deverá ser retornado após a avaliação das etapas descritas no bloco let.

A primeira variável que vamos criar dentro do nosso bloco let será a variável fnSubstituirDatab, que será a nossa função em si, contendo 2 parâmetros. O primeiro será o texto que desejamos fazer a substituição, e o segundo será um contador para identificar em qual linha da nossa tabela DePara estamos iterando.

let
    fnSubstituirDatab = (Texto as text, optional Pos as nullable number) =>

Parâmetros: Texto do tipo text e Pos sendo opcional do tipo nullable number

Um tipo de dado nullable number significa que quando o parâmetro não for informado receberá o valor null, e quando informado deverá receber um valor numérico.

Agora precisamos criar o bloco let  da função fnSubstituirDatab.

Como iremos fazer um loop (recursividade) na tabela DePara,  a variável Pos deverá ser dinâmica, e a cada iteração iremos incrementá-la. Porém precisamos iniciar o nosso contador com o valor zero.  Criaremos uma variável chamada NovaPos que receberá o valor do incremento. Caso o parâmetro Pos não tenha sido informado (null) iremos definir o valor inicial como zero, caso contrário receberá o valor informado no parâmetro Pos.

A variável TextoSubstituido receberá o texto após a substituição da iteração corrente. Iremos utilizar a função built-in Text.Replace. A sintaxe desta função é a seguinte:

Text.Replace(texto_a_ser_substituido, texto_antigo, texto_novo)

O texto_antigo e o texto_novo serão referenciados através da variável NovaPos, referenciando a tabela DePara, a coluna e a posição do item. Veja um exemplo de busca do valor na tabela DePara:

DePara[De]{NovaPos}  Tabela[Coluna]{Posição}  

    let
     NovaPos = if Pos = null then 0 else Pos, 
     TextoSubstituido = Text.Replace(Texto, DePara[De]{NovaPos}, DePara[Para]{NovaPos})

No bloco in devemos informar o resultado da substituição que está armazenado na variável TextoSubstituido. Porém, só devemos retornar este valor caso todas as iterações tenham sido feitas, caso contrário invocaremos a função fnSubstituirDatab novamente, informando uma nova posição de substituição.

Para verificar se todas as iterações foram feitas utilizamos a função List.Count para saber quantos elementos tem em uma das colunas da tabela DePara. Perceba que subtraí 1 do resultado, pois nosso contador inicia-se do zero (a linguagem M é base 0).

Um detalhe importante ao invocar uma função dentro dela mesmo (recursividade) é necessário informar o @ antes do nome da função. Perceba que o segundo parâmetro NovaPos é somado + 1 para que a próxima iteração seja na próxima linha da tabela. A iteração só será interrompida quando NovaPos for igual a quantidade de itens na lista (subtraído de 1).

    in
      if NovaPos = List.Count(DePara[De]) - 1 
      then 
          TextoSubstituido 
      else 
          @fnSubstituirDatab(TextoSubstituido, NovaPos + 1)

Agora é só retornar a função que criamos através do bloco in

 
  in 
    fnSubstituirDatab 

E invocar a função quando desejar fazer a substituição.

Atualização da Função

Após a publicação original deste artigo, houve o questionamento sobre a substituição de partes de uma palavra. Imagine por exemplo a palavra NASCEU, ao usar a função Text.Replace o conjunto SC seria substituído por Santa Catarina. Para evitar problemas como esse, em alguns casos, devemos fazer a substituição apenas de palavras completas.

Para corrigir essa situação fiz uma pequena adaptação dentro do bloco let da função.

     QuebraTexto = Text.Split(Texto, " "),
     Substitui = List.Transform(QuebraTexto, 
                                each if _ = DePara[De]{NovaPos} then DePara[Para]{NovaPos} else _), 

Foram adicionadas duas novas etapas. A etapa QuebraTexto é responsável por dividir o texto informado em uma lista, separando os elementos a cada espaço encontrado. O resultado será uma lista onde cada palavra será um elemento desta lista.

     QuebraTexto = Text.Split(Texto, " "),

Na etapa Substitui fazemos a transformação da lista da etapa QuebraTexto, comparando se o elemento da lista é igual a palavra a ser substituída. Caso verdadeiro, o elemento da lista é substituído pela nova palavra. Caso falso, o próprio elemento é retornado ( _ ).

     Substitui = List.Transform(QuebraTexto, 
                                each if _ = DePara[De]{NovaPos} then DePara[Para]{NovaPos} else _), 

O resultado final da função completa fica assim:

let
    fnSubstituirDatab = (Texto as text, optional Pos as nullable number) => 
    let
     NovaPos = if Pos = null then 0 else Pos, 

     QuebraTexto = Text.Split(Texto, " "),
     Substitui = List.Transform(QuebraTexto, 
                                each if _ = DePara[De]{NovaPos} then DePara[Para]{NovaPos} else _), 
     
     TextoSubstituido = Text.Combine(Substitui, " ")

    in
     
      if NovaPos = List.Count(DePara[De]) - 1 
      then 
          TextoSubstituido 
      else 
          @fnSubstituirDatab(TextoSubstituido, NovaPos + 1),

    Saida = fnSubstituirDatab 
in
    fnSubstituirDatab

Bônus

Um detalhe muito importante é que a Linguagem M é case-sensistive, isso é, letras maiúsculas são diferentes de letras minúsculas. Se você quiser que não haja essa diferenciação na hora de fazer a substituição altere a etapa Substitui pelo código abaixo:

     Substitui = List.Transform(QuebraTexto, 
            each if Text.Lower(_) = Text.Lower(DePara[De]{NovaPos}) then DePara[Para]{NovaPos} else _), 

AGORA É COM VOCÊ!

  • Se gostou, compartilhe na sua rede social (Facebook, Twitter, Linkedin, etc). Basta usar os botões logo abaixo, isso ajuda a espalhar o conhecimento.
2019-03-26T14:09:15-03:00

Comente!

Informações de Contato

  • Av. Paulista, 171, Andar 4, Bela Vista, São Paulo/SP CEP 01311-904
  • (11) 9 4052 2499
  • comercial@datab.com.br
  • De Segunda à Sexta-feira Das 9:00 às 18:00

Posts recentes