Neste artigo você aprenderá os mais comuns tratamentos de dados no Power Query no Excel.
1. Tratamentos de Dados no Power Query
No Power Query o teor da transformação dos dados dependerá sempre da forma como estão apresentados os dados brutos originalmente. Quanto mais desestruturados os dados, mais esforço e consequentemente etapas (e tempo) podem ser demandados para a adequada disponibilização dos dados.
Podemos precisar de executar tarefas simples ou até mais complexas para obter dados estruturados que nos permitam executar o trabalho desejado, seja ele um relatório de tabela dinâmica ou até mesmo um dashboard, por exemplo, sem maiores problemas na visualização e nos cálculos apresentados.
A seguir, são apresentadas algumas opções de transformações/tratamentos de dados, realizados com frequência, e com baixíssimo nível de complexidade (realizadas quase na totalidade com o uso do mouse).
2. Transformações de dados mais frequentes no Power Query
2.1 – Remover colunas
Selecione a coluna (note a seleção na cor verde) e pressione a tecla “Delete” ou use o botão secundário do mouse no cabeçalho da coluna e escolha a opção “Remover”.
Uma opção útil é “Remover Outras Colunas”, que remove as colunas não selecionadas. Isto é interessante para ignorar eventuais novas colunas que passem a existir nos dados, algo frequente em dados exportados de ERPs, por exemplo.
Dica: Para selecionar colunas dispersas, após a seleção de uma coluna inicial, use Ctrl e selecione outras colunas desejadas. Use Shift para selecionar colunas adjacentes.
2.2 – Inserir coluna personalizada
É extremamente comum necessitarmos de obter novas colunas em nossos dados para as mais diversas utilidades que se possa imaginar. Para este fim, acessamos a guia “Adicionar Coluna” (1) e em seguida a opção “Coluna Personalizada” (2).
Será exibida a janela para a criação da coluna desejada, em que devemos definir o seu nome (1) e a fórmula/expressão para a obtenção dos valores da coluna (2). Confirmarmos em “Ok” ao terminarmos de escrever a fórmula.
No Power Query as fórmulas empregam a linguagem M (em Inglês) e os números devem usar o ponto (.) como separador de decimal.
Esta coluna personalizada seria implementada normalmente com a função SE, através de fórmula equivalente (admitindo x = autonomia de estoque): =SE(x<=0;”Ruptura”;SE(x<=3;”Crítico”;”Normal”)).
A imagem anterior mostra a coluna personalizada sendo inserida com sua fórmula, cujo resultado está elencado na imagem abaixo.
2.3 – Dividir colunas
Podemos dividir colunas de acordo com vários critérios, inclusive por delimitadores. Use o botão secundário do mouse no cabeçalho da coluna e escolha a opção “Dividir coluna”, escolhendo a opção “Por Delimitador…” em seguida.
Selecione seu delimitador se já existente na lista disponibilizada (1), ou insira-o caso contrário (2). Confirme a divisão de colunas em “Ok”.
P.S: use um conjunto de caracteres consecutivos como delimitador, se necessário.
Eis o resultado obtido:
Esta tarefa costuma ser realizada com o recurso bem conhecido de “Texto para colunas” ou com fórmulas através das funções FILTROXML ou ainda TEXTODIVISÃO. A vantagem do Power Query é a automação, evitando trabalho repetitivo futuramente.
2.4 – Mesclar colunas (concatenar)
É o inverso da divisão de colunas mencionada no item anterior. Podemos juntar colunas (2 ou mais) para obter os dados em uma única coluna. Inicialmente, selecione as colunas que deseja unir e com o botão secundário do mouse clique no cabeçalho de uma delas e escolha a opção “Mesclar colunas”.
P.S: a ordem da seleção das colunas será mantida no resultado final, mesmo que originalmente a ordem das colunas nos dados seja diferente.
Selecione seu separador (caso deseje usar) se existente (1), ou insira-o caso seja personalizado e deseje realmente usar (2). Defina um nome para a coluna resultante desta mescla (3) e confirme em “Ok”.
P.S: Insira um separador personalizado contendo mais de 1 caractere, se necessário, como “ – “, que possui espaço antes e após o hífen, totalizando 3 caracteres.
Esta tarefa costuma ser realizada com fórmulas, usando as funções CONCATENAR, CONCAT e UNIRTEXTO, ou ainda o operador &. A vantagem do Power Query é a automação, evitando trabalho repetitivo futuramente.
2.5 – Usar linha como cabeçalho
A depender das fontes de dados, o Power Query costuma atribuir nomes padrões para as colunas. Com isso, muitas vezes o cabeçalho das colunas está na 1ª linha dos dados. No entanto, podemos aproveitar a 1ª linha dos dados e “subi-la”, tornando-a o cabeçalho. A imagem a seguir mostra os passos necessários.
Neste caso (imagem mais à esquerda – antes), a linha 1 possui os termos “UF, “Capital” e Valor”, que deveriam nomear as colunas em questão, mas estão representando dados. Através do Power Query promovemos esta linha, que “subiu”, tornando-se o cabeçalho das colunas (imagem mais à direita – depois).
P.S: toda a tabela será afetada e a linha 1 completa (ou seja, envolvendo todas as colunas) será promovida, tornando-se cabeçalho.
2.6 – Filtrar linhas
Podemos fazer de modo semelhante ao autofiltro dos dados em planilhas, clicando na “setinha” situada ao lado direito do nome da coluna e executando o filtro de acordo com os critérios desejados. Na imagem a seguir, por exemplo, podemos selecionar apenas “MG” e confirmar em “Ok”.
P.S: Explore opções avançadas de filtros de textos, evidenciadas em vermelho.
Para data e data/hora podemos fazer filtros manuais marcando individualmente todos os valores desejados, tal qual fizemos anteriormente com textos. Porém, temos também alguns filtros cronológicos inteligentes e notáveis, como “Mês” em que por exemplo podemos estrategicamente sempre escolher apenas dados do mês passado.
Para números também há possibilidades mais eficientes de filtragem, inclusive envolvendo faixas de valores.
2.7 – Remover linhas com valores vazios
É extremamente comum em fontes de dados desestruturadas precisarmos de remover linhas que contêm dados vazios em colunas. Estas linhas podem ser verdadeiro “lixo” nos dados. Visualmente podemos identificar um valor vazio com os termos “null” (evidenciados em vermelho na imagem a seguir).
A remoção das linhas contendo estes valores é simples: acionamos o filtro e escolhemos a opção “Remover Vazio”.
Explorando o filtro, os valores vazios se apresentam como “(nulo)”. Note na imagem anterior. A imagem seguinte mostra o resultado ao removermos vazios na terceira coluna.
Podemos e devemos remover vazios em várias colunas caso necessário, visto que em muitas ocasiões apenas uma coluna com vazios removidos não completa este tipo de limpeza.
P.S: O Power Query é case sensitive, ou seja: diferencia maiúsculas e minúsculas. Desta forma, as palavras ANA, AnA, Ana, ANa, aNA, anA, aNa e ana são tratadas como palavras completamente diferentes, impactando em filtros, por exemplo.
2.8 – Ajustar textos (maiúsculas e minúsculas)
Explorando as opções da coluna com o botão secundário do mouse encontramos opções para editarmos maiúsculas e minúsculas.
Normalmente, nos dados em planilhas usamos fórmulas com as funções PRI.MAIÚSCULA, MAIÚSCULA e MINÚSCULA. Recomenda-se especialmente o uso da alteração “Colocar Cada Palavra em Maiúscula” para a padronização dos dados, com reflexos positivos em gráficos e segmentações de dados, por exemplo.
Neste caso, os dados estão agora apenas com as iniciais em maiúsculas. É como se usássemos a função PRI.MAIÚSCULA.
2.9 – Limpar textos (caracteres “estranhos” e espaços no início e fim)
Explorando as opções da coluna com o botão secundário do mouse encontramos opções para fazermos “faxina” nos textos.
A opção “Limpar” remove “sujeiras” dos textos (quebras de linhas, caracteres não imprimíveis e outras inconformidades), semelhantemente à função TIRAR. A opção “Cortar” remove espaços antes e depois do texto, sendo semelhante à função ARRUMAR. No entanto, não elimina espaços excedentes intermediários no texto.
Neste caso, eliminamos os espaços excedentes no início e fim dos textos, além de remover a quebra de linha da linha 3 (Luiza Maria Rocha), em uma etapa para cada ação. Habitualmente usamos a função ARRUMAR para tratar os espaços inconvenientes e usamos o recurso de “localizar e substituir” para remover caracteres indesejados ou até mesmo a função SUBSTITUIR para este fim.
2.10 – Preencher textos (repetir)
Os dados podem se apresentar como os ilustrados na imagem abaixo, em que um certo valor ocupa somente o topo, não tendo sido repetido para baixo em linhas seguintes vazias. Isto é típico de dados mesclados ou de dados desestruturados em algumas fontes. Com o Power Query podemos facilmente repetir esses valores para as linhas seguintes (abaixo).
Note na imagem anterior que a coluna “Seção” possui células vazias (null) que precisam repetir o primeiro valor preenchido imediatamente acima, ou seja: as linhas 2 e 3 precisam receber a seção “Açougue”, e as linhas 5 e 6 precisam receber a seção “Bebidas”.
Para ajustarmos, devemos selecionar as colunas desejadas e acessar a guia “Transformar”, escolhendo em seguida as opções “Preenchimento” e “Para Baixo”.
Há também a mesma opção disponível com o botão secundário do mouse, ao clicarmos no(s) cabeçalho(s) da(s) coluna(s) desejada(s).
A imagem seguinte exibe o resultado do tratamento realizado, baseado na primeira imagem deste tópico.
2.11 – Substituir valores
Muitas vezes precisamos de substituir valores completos ou trechos por outros valores ou até mesmo por nada (o que implica apenas em uma simples remoção). O Power Query auxilia nesta tarefa e para executá-la devemos selecionar a(s) coluna(s) de interesse e clicar com o botão secundário em algum cabeçalho selecionado, escolhendo a opção “Substituir Valores…”.
Na janela que é apresentada logo em seguida devemos indicar o valor existente (1), da forma que é apresentado (obedecendo a maiúsculas e minúsculas). Pode ser valor completo ou parcial (neste último caso apenas para campos de texto). Este valor será substituído pelo valor indicado logo abaixo (2). Deixe este campo em branco para remover o texto ou trecho indesejado, indicado em (1).
A substituição ilustrada na imagem anterior faz a substituição do texto “Loja “ (note o espaço após), inserindo em seu lugar o termo “B”.
A imagem a seguir mostra o comparativo, com o resultado após a substituição devidamente realizada.
P.S: Em dados de planilhas, este ajuste seria realizado normalmente com fórmula, usando com a função SUBSTITUIR ou até mesmo MUDAR. Uma alternativa seria o uso do recurso de “Localizar e substituir”.
Download da Planilha Tratamento de Dados no Power Query
Clique no botão abaixo para realizar o download do arquivo de exemplo: