Conteúdo
Um dos vídeos mais vistos no meu canal do YouTube é um vídeo sobre o Flash Fill no Microsoft Excel. A essência dessa ferramenta é que, se você precisar transformar de alguma forma seus dados de origem, basta começar a digitar o resultado que deseja obter na coluna adjacente. Depois de várias células digitadas manualmente (geralmente 2-3 são suficientes), o Excel vai “entender” a lógica das transformações que você precisa e continuar automaticamente o que você digitou, completando todo o trabalho monótono para você:
A quintessência da eficiência. O botão mágico “faça certo” que todos nós amamos tanto, certo?
De fato, existe um análogo de tal ferramenta no Power Query - lá é chamado Coluna de exemplos (Coluna de Exemplos). Na verdade, essa é uma pequena inteligência artificial incorporada ao Power Query que pode aprender rapidamente com seus dados e transformá-los. Vamos dar uma olhada em suas capacidades em vários cenários práticos para entender onde ele pode ser útil para nós em tarefas reais.
Exemplo 1. Colagem/corte de texto
Digamos que temos uma tabela tão “inteligente” no Excel com dados sobre funcionários:
Carregue-o no Power Query da maneira padrão - com o botão Da tabela/intervalo aba Data (Dados — Da Tabela/Intervalo).
Suponha que precisamos adicionar uma coluna com sobrenomes e iniciais para cada funcionário (Ivanov SV para o primeiro funcionário, etc.). Para resolver esse problema, você pode usar um dos dois métodos:
- clique com o botão direito do mouse no cabeçalho da coluna com os dados de origem e selecione o comando Adicionar coluna de exemplos (Adicionar coluna de exemplos);
- selecione uma ou mais colunas com dados e na aba Adicionando uma coluna escolha um time Coluna de exemplos. Aqui, na lista suspensa, você pode especificar se todas ou apenas as colunas selecionadas precisam ser analisadas.
Então tudo é simples – na coluna que aparece à direita, começamos a inserir exemplos dos resultados desejados, e a inteligência artificial incorporada ao Power Query tenta entender nossa lógica de transformação e continuar por conta própria:
A propósito, você pode inserir as opções corretas em qualquer célula desta coluna, ou seja, não necessariamente de cima para baixo e em uma linha. Além disso, você pode facilmente adicionar ou remover colunas da análise posteriormente usando as caixas de seleção na barra de título.
Preste atenção à fórmula na parte superior da janela – é isso que o Smart Power Query cria para obter os resultados de que precisamos. Esta, aliás, é a diferença fundamental entre esta ferramenta e Preenchimento instantâneo em Excel. O preenchimento instantâneo funciona como uma “caixa preta” – eles não nos mostram a lógica das transformações, mas simplesmente dão resultados prontos e nós os tomamos como garantidos. Aqui tudo é transparente e você sempre pode entender com absoluta clareza o que exatamente está acontecendo com os dados.
Se você vir que o Power Query “pegou a ideia”, pode pressionar com segurança o botão OK ou atalho de teclado Ctrl+Entrar – será criada uma coluna personalizada com uma fórmula inventada pelo Power Query. A propósito, ela pode ser facilmente editada posteriormente como uma coluna normal criada manualmente (com o comando Adicionando uma Coluna – Coluna Personalizada) clicando no ícone de engrenagem à direita do nome da etapa:
Exemplo 2: Caso como em frases
Se você clicar com o botão direito do mouse no cabeçalho da coluna com texto e selecionar o comando Transformação (Transformar), então você pode ver três comandos responsáveis por alterar o registro:
Conveniente e legal, mas nesta lista, por exemplo, sempre me faltou mais uma opção – caso como em frases, quando a capitalização (maiúsculo) não se torna a primeira letra de cada palavra, mas apenas a primeira letra da célula, e o restante do texto quando Isto é exibido em letras minúsculas (pequenas).
Esse recurso ausente é fácil de implementar com inteligência artificial Colunas de exemplos – basta inserir algumas opções para que o Power Query continue com o mesmo espírito:
Como uma fórmula aqui, o Power Query usa várias funções Texto. Superior и Texto. Inferior, convertendo texto em maiúsculas e minúsculas, respectivamente, e funções Texto.Início и Texto.Meio – análogos das funções Excel LEFT e PSTR, capazes de extrair uma substring do texto da esquerda e do meio.
Exemplo 3. Permutação de palavras
Às vezes, ao processar os dados recebidos, torna-se necessário reorganizar as palavras nas células em uma determinada sequência. Claro, você pode dividir a coluna em colunas de palavras separadas pelo separador e depois colá-la de volta na ordem especificada (não se esqueça de adicionar espaços), mas com a ajuda da ferramenta Coluna de exemplos tudo será muito mais fácil:
Exemplo 4: Apenas números
Outra tarefa muito importante é extrair apenas números (números) do conteúdo da célula. Como antes, depois de carregar os dados no Power Query, vá para a guia Adicionando uma coluna – Coluna de exemplos e preencha algumas células manualmente para que o programa entenda exatamente o que queremos obter:
Bingo!
Novamente, vale a pena olhar a parte superior da janela para ter certeza de que Query gerou a fórmula corretamente – neste caso ela contém uma função Texto. Selecionar, que, como você pode imaginar, extrai os caracteres fornecidos do texto de origem de acordo com a lista. Posteriormente, esta lista, é claro, pode ser facilmente editada na barra de fórmulas, se necessário.
Exemplo 5: somente texto
Da mesma forma que no exemplo anterior, você pode retirar e vice-versa – apenas o texto, excluindo todos os números, sinais de pontuação, etc.
Neste caso, é utilizada uma função que já tem significado oposto – Text.Remove, que remove caracteres da string original de acordo com uma determinada lista.
Exemplo 6: Extraindo dados de um mingau alfanumérico
O Power Query também pode ajudar em casos mais difíceis, quando você precisa extrair informações úteis do mingau alfanumérico em uma célula, por exemplo, obter o número da conta na descrição da finalidade do pagamento em um extrato bancário:
Observe que a fórmula de conversão gerada pelo Power Query pode ser bastante complexa:
Para facilitar a leitura e compreensão, ele pode ser convertido em uma forma muito mais sã usando um serviço online gratuito. Formatador do Power Query:
Coisa muito útil – respeito aos criadores!
Exemplo 7: Conversão de datas
ferramenta Coluna de exemplos também pode ser aplicado a colunas de data ou data e hora. Quando você insere os primeiros dígitos de uma data, o Power Query exibe uma lista de todas as opções de conversão possíveis:
Assim, você pode facilmente converter a data original para qualquer formato exótico, como “ano-mês-dia”:
Exemplo 8: Categorização
Se usarmos a ferramenta Coluna de exemplos a uma coluna com dados numéricos, funciona de forma diferente. Suponha que tenhamos resultados de testes de funcionários carregados no Power Query (pontuações condicionais no intervalo de 0 a 100) e usemos a seguinte gradação condicional:
- Masters – aqueles que marcaram mais de 90
- Especialistas – pontuados de 70 a 90
- Usuários – de 30 a 70
- Iniciantes – aqueles que pontuaram menos de 30
Se adicionarmos uma coluna dos exemplos à lista e começarmos a organizar essas gradações manualmente, muito em breve o Power Query pegará nossa ideia e adicionará uma coluna com uma fórmula, onde os operadores aninhados entre si if lógica será implementada, muito semelhante ao que precisamos:
Novamente, você não pode pressionar a situação até o fim, mas clique em OK e depois corrija os valores limite já na fórmula – é mais rápido assim:
Conclusões
Certamente uma ferramenta Coluna de exemplos não é uma “pílula mágica” e, mais cedo ou mais tarde, haverá situações fora do padrão ou casos especialmente negligenciados de um “farm coletivo” nos dados, quando o Power Query falhará e não poderá resolver o que queremos corretamente para nós. No entanto, como ferramenta auxiliar, é muito bom. Além disso, estudando as fórmulas que ele gerou, você pode expandir seu conhecimento das funções da linguagem M, que sempre serão úteis no futuro.
- Analisando texto com expressões regulares (RegExp) no Power Query
- Pesquisa de texto difuso no Power Query
- Preenchimento Flash no Microsoft Excel