Conteúdo
Se você estiver pelo menos um pouco familiarizado com expressões regulares, não precisará anunciá-las. Se você não está bem no assunto, então expressões regulares (Expressões Regulares = RegExp = “regexps” = “regulares”) é uma linguagem onde, usando caracteres e regras especiais, as substrings necessárias são pesquisadas no texto, elas são extraídas ou substituído por outro texto. Esta é uma ferramenta muito poderosa e bonita, uma ordem de grandeza superior a todas as outras formas de trabalhar com texto.
Já descrevi em detalhes e com vários exemplos da vida como você pode adicionar suporte a expressões regulares ao Excel usando macros simples – se você não leu este artigo, recomendo que você o leia antes de continuar. Você vai descobrir muitas coisas novas, eu garanto 🙂
No entanto, a questão permanece em aberto – como adicionar a capacidade de usar expressões regulares no Power Query? O Power Query, claro, é bom por si só e pode fazer muito com o texto (recortar, colar, limpar, etc.), mas se você pudesse cruzá-lo com o poder das expressões regulares, seria apenas uma bomba.
Infelizmente, não há funções internas para trabalhar com RegExps no Power Query, e a ajuda oficial da Microsoft e o suporte técnico respondem negativamente a essa pergunta. No entanto, existe uma maneira de contornar essa limitação 🙂
A essência do método
A idéia principal é simples de desgraçar.
Na lista de recursos internos do Power Query, há uma função Página da Internet. A descrição desta função no site de ajuda oficial da Microsoft é extremamente concisa:
Traduzido, isso seria: “Retorna o conteúdo do documento HTML dividido em suas estruturas de componentes, bem como uma representação do documento completo e seu corpo após a remoção das tags”. Descrição mais ou menos, francamente.
Normalmente esta função é utilizada ao importar dados da web e é substituída automaticamente, por exemplo, quando selecionamos na aba Data Command Da internet (Dados — Da web). Damos à função uma página da web como argumento e ela retorna seu conteúdo para nós na forma de tabelas, tendo limpado previamente todas as tags.
O que a ajuda NÃO diz é que além da linguagem de marcação HTML função Página da Internet suporta scripts JavaScript, que agora é onipresente em sites na Internet. E o JavaScript, por sua vez, sempre foi capaz de trabalhar com expressões regulares e possui funções internas para RegExps! Portanto, para implementar expressões regulares no Power Query, precisaremos alimentar as funções Web.Page como um argumento para um pequeno programa JavaScript que fará todo o trabalho do Power Query.
Como é em JavaScript puro
Existem muitos tutoriais detalhados sobre como trabalhar com expressões regulares em JavaScript na Internet (por exemplo, um, dois).
Em resumo e simplificado, o código JavaScript ficará assim:
Aqui:
- var str = 'Pagar as contas 123 e 789 da salsicha'; - criar uma variável str e atribua-lhe o texto fonte que iremos analisar.
- var padrão = /d+/gi; – criar uma expressão regular e colocá-la em uma variável de cinto de segurança.
A expressão começa com uma barra (/).
A própria expressão aqui, por exemplo, é d+ significa qualquer sequência de dígitos.
Através da fração após a expressão, existem parâmetros de pesquisa adicionais (modificadores) – eles podem ser especificados em qualquer ordem:
- g – significa pesquisa global, ou seja, após encontrar uma correspondência, você não deve parar, mas continuar a pesquisa até o final do texto. Se este modificador não estiver definido, nosso script retornará apenas a primeira correspondência (123)
- i – pesquisa sem considerar maiúsculas e minúsculas
- m – pesquisa de várias linhas (usada quando o texto de origem é dividido em várias linhas)
- var resultado = str.match(pattern).join(';'); – realizar uma pesquisa no texto fonte (str) pela expressão regular fornecida (de cinto de segurança) e coloque os resultados em uma variável resultar, concatenando-os com um ponto e vírgula usando o comando juntar
- document.write(resultado); – exibe o conteúdo da variável de resultado
Observe também que as cadeias de texto (excluindo expressões regulares) em JavaScript são colocadas entre apóstrofos, não aspas, como no Power Query ou VBA.
Na saída, este script nos dará como resultado todos os números encontrados no texto fonte:
123, 789
O minicurso de JavaScript acabou, obrigado a todos. Espero que tenha entendido a lógica 🙂
Resta transferir essa construção para o Power Query.
Função de pesquisa e extração de texto por expressão regular no Power Query
Fazemos o seguinte:
1. Abra o Excel e crie um novo Power Query vazio na guia Dados – Obter dados / Criar solicitação – De outras fontes – Solicitação vazia (Dados — Obter dados / Nova consulta — De outras fontes — Consulta em branco). Se você tiver uma versão antiga do Excel 2010-2013 e do Power Query que não possui, mas foi instalado como um suplemento separado, tudo isso estará na guia Consulta de energiaE não Data.
2. Na janela vazia do editor de consultas que se abre, no painel direito, digite imediatamente o nome da nossa futura função (por exemplo, fxRegExpExtract)
3. Vamos para a aba Visualização – Editor Avançado (Visualizar — Editor Avançado), apagamos todo o código M da requisição vazia e colamos o código da nossa superfunção lá:
Cuidado com as mãos:
Na primeira linha, dizemos que nossa função terá três argumentos de texto: txt – o texto original em análise, regex – padrão de expressão regular, delim — caractere delimitador para exibição de resultados.
Em seguida, chamamos a função Página da Internet, formando o código JavaScript descrito acima em seu argumento. Colamos e substituímos nossos argumentos variáveis no código.
Fragmento:
[Dados]{0}[Filhos]{0}[Filhos]{1}[Texto]{0}
… é necessário “cair” na tabela com os resultados que precisamos. A questão é que a função Página da Internet como resultado, ele produz várias tabelas aninhadas que repetem a estrutura de uma página da web. Sem este pedaço de código M, nossa função produziria isso:
… e teríamos que clicar na palavra várias vezes mesa, sucessivamente "caindo" em tabelas aninhadas filhas em colunas Crianças:
Em vez de toda essa citação, indicamos imediatamente no código de nossa função qual tabela e coluna aninhadas (Texto) nós precisamos.
Aqui, de fato, estão todos os segredos. Resta apertar o botão Acabamento na janela editor avançado, onde inserimos nosso código, e você pode prosseguir para o mais delicioso – experimente nossa função no trabalho.
Aqui estão alguns exemplos de sementes.
Exemplo 1. Recuperando o número da conta e a data da descrição do pagamento
Temos um extrato bancário com uma descrição (finalidade) dos pagamentos, onde você precisa extrair os números e as datas das faturas pagas em colunas separadas:
Carregamos a tabela no Power Query da maneira padrão por meio de Dados - Da Tabela/Intervalo (Dados - De Tcapaz/Ranjo).
Em seguida, adicionamos uma coluna calculada com nossa função via Adicionar Coluna - Chamar Função Personalizada (Adicionar coluna — invocar função personalizada) e insira seus argumentos:
Como uma expressão regular (argumento regex) modelo que usamos:
(e{3,5}|d{2}.d{2}.d{4})
... traduzido em linguagem humana significando:
números de 3 a 5 dígitos (números de conta)
or
fragmentos da forma “número de 2 bits – ponto – número de 2 bits – ponto – número de 4 bits”, ou seja, datas no formato DD.MM.AAAA.
Como caractere delimitador (argumento delim) insira um ponto e vírgula.
Depois de clicar em OK nossa função mágica analisa todos os dados iniciais de acordo com nossa expressão regular e forma uma coluna para nós com os números encontrados e as datas das faturas:
Resta separá-lo por ponto e vírgula usando o comando Home — Coluna Dividida — Por Delimitador (Início — Coluna dividida — Por delimitador) e conseguimos o que queríamos:
Beleza!
Exemplo 2: extrair endereços de e-mail do texto
Suponha que temos a seguinte tabela como dados iniciais:
… de onde precisamos extrair os endereços de e-mail encontrados lá (para maior clareza, destaquei-os em vermelho no texto).
Como no exemplo anterior, carregamos a tabela no Power Query da maneira padrão via Dados - Da Tabela/Intervalo (Dados - De Tcapaz/Ranjo).
Em seguida, adicionamos uma coluna calculada com nossa função via Adicionar Coluna - Chamar Função Personalizada (Adicionar coluna — invocar função personalizada) e insira seus argumentos:
Analisar endereços de e-mail é uma tarefa mais difícil e há várias expressões regulares de vários graus de pesadelo para resolvê-lo. Eu usei uma das opções simples – não é o ideal, mas funciona na maioria dos casos:
[w|.|-]*@w*.[w|.]*
Como separador (delim) você pode inserir um ponto e vírgula e um espaço.
Clique em OK e obtemos uma coluna com endereços de e-mail extraídos do texto original “mingau”:
Magia!
PS
Como diz o ditado: “Não existe coisa tão boa que não possa ser ainda melhor”. O Power Query é legal por si só e, quando combinado com expressões regulares, nos dá poder e flexibilidade completamente irreais no processamento de quaisquer dados de texto. Espero que um dia a Microsoft adicione suporte ao RegExp nas atualizações do Power Query e do Power BI e todas as danças acima com um pandeiro se tornem uma coisa do passado. Bem, por enquanto, sim.
Também quero acrescentar que é conveniente brincar com expressões regulares no site https://regexr.com/ – diretamente no editor online. Lá na seção Padrões da comunidade Há um grande número de temporadas regulares prontas para todas as ocasiões. Experimente – todo o poder das expressões regulares está agora ao seu serviço no Power Query!
- O que são expressões regulares (RegExp) e como usá-las no Excel
- Pesquisa de texto difuso no Power Query
- Montando tabelas de arquivos diferentes usando o Power Query