Hoje eu quero te mostrar o que é o Power Query Excel e como ele vai te auxiliar no seu tratamento de dados dentro do Excel!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:
Você já ouvir falar em Power Query ou sabe o que é esse tal de Power Query Excel? Nessa aula eu vou te mostrar o que é essa ferramenta e como ela pode te ajudar muito dentro do Excel.
Essa ferramenta é um editor de consultas do Excel que pode facilitar o seu tratamento de dados deixando essa parte automática.
Você vai notar que conseguimos fazer os mesmos procedimentos que fazemos dentro do Excel no Power Query, só que sem fórmulas, ou seja, é ainda mais fácil!
Hoje eu vou te mostrar uma aplicação prática no Excel para que você entenda como essa ferramenta funciona e como ela pode te ajudar.
Então imagine que você trabalha em uma empresa e mensalmente precisa ajustar um relatório para que seus superiores consigam visualizar a analisar as informações de forma eficiente.
Essa é a sua base de dados inicial, aqui você já vai notar alguns problemas que vamos ter que tratar.
Primeiro a nossa tabela não começa na primeira linha, depois temos algumas linhas em branco, temos na coluna de produto a marca do produto, então são 2 informações em uma única coluna.
Em País de Origem nós temos apenas a sigla desses países, então podemos substituir pelo nome de cada uma para facilitar.
No endereço nós temos a cidade específica de cada um desses produtos e essa é uma informação interessante caso você precise fazer uma análise por cidades em cada um dos países.
Os valores na quantidade de estoque estão no formato americano, então temos um ponto no lugar da vírgula.
São esses os ajustes que nós vamos ter que fazer. Como você deve saber no próprio Excel é possível fazer todos esses ajustes.
Mas a ideia é te mostrar como fazer isso dentro do Power Query para que você possa automatizar esse processo já que precisa fazer todos os meses.
A ideia é deixar a sua tabela nesse formato com tudo já ajustado.
Então vamos passar da tabela inicial para essa só utilizando o Power Query.
IMPORTANTE: Nos arquivos que nós disponibilizamos tem uma apostila ensinando a instalar o Power Query, pois nas versões do Excel 2010 e 2013 você precisa baixar essa ferramenta. Nas versões 2016 e Microsoft 365 ele já vem instalado no próprio Excel. Agora para versões mais antigas infelizmente esse recurso não está disponível.
Vamos começar! O primeiro passo é abrir um arquivo em branco, pois como vamos ter que repetir o procedimento todo mês a única alteração que vamos fazer depois é qual arquivo estamos utilizando.
Com o arquivo aberto (considerando que você tem a versão mais recente) vamos até a guia Dados > Obter Dados > De Arquivo > Da Pasta de Trabalho.
Ao clicar nessa opção vai aparecer uma janela para que você possa escolher o arquivo que vai começar a tratar.
No nosso caso é o arquivo 01-21 que seria o arquivo referente ao mês de janeiro de 2021.
Após escolher o arquivo você vai notar que vai aparecer uma janela de pré-visualização dos dados para que confira se está tudo certo.
Aqui você vai selecionar a tabela que vai ser tratada no canto esquerdo e depois vai clicar em Transformar Dados.
Feito isso o ambiente do Power Query será aberto e com isso você nota que é outra janela independente do Excel, é como se fosse um programa diferente.
Aqui você vai ter a sua tabela como tínhamos no Excel, só que aqui vamos ter algumas ferramentas bem interessantes para fazer esse tratamento.
O primeiro tratamento que nós vamos fazer é remover as 2 primeiras linhas, pois uma delas é totalmente vazia e a outra tem apenas uma data, que não será relevante.
Para isso vamos até a guia Página Inicial > Remover Linhas > Remover Linhas Principais. Isso significa que você vai remover as linhas de cima da tabela, então quando aparecer a nova janela basta escrever 2 e pressionar ok. Assim as 2 primeiras linhas serão eliminadas.
Em seguida nossa próxima etapa é eliminar todas as linhas em branco, que provavelmente foram geradas por algum software.
Você deve saber que quando trazemos dados do SAP ou qualquer outro sistema eles não ficam todos certinhos, então precisamos fazer esses tratamentos.
Para isso vamos até a Página Inicial > Remover Linhas > Remover Linhas em Branco. Com isso todas as linhas que estão totalmente vazias serão removidas.
Para finalizar essa parte inicial e dar formato a nossa tabela vamos até Página Inicial > Usar a Primeira Linha como Cabeçalho.
Essa parte é muito importante para que a primeira linha que está com os nomes do cabeçalho suba e você tenha os nomes corretos de todas as colunas.
Assim fica mais fácil de saber quais informações temos em cada uma delas, e claro, ao lado esquerdo de cada uma você vai notar um símbolo que indica qual o formato das informações daquela coluna.
OBS: Caso queira alterar o formato se ele não estiver correto basta clicar nesse símbolo e fazer a alteração.
IMPORTANTE: O Power Query não permite que você tenha 2 formatos em uma mesma coluna, então se tem texto não poderá ter números e se tem números não poderá ter textos. Isso é importante falar, pois quando for fazer algumas operações pode ter algum erro por conta dos formatos.
Com isso a nossa tabela já está ajustada, agora nós vamos fazer alguns tratamentos para melhorar ainda mais a visualização e depois a análise desses dados.
O primeiro ajuste é nada coluna País Origem, onde vamos substituir as siglas pelos nomes dos países.
Para isso basta ir até Transformar > Substituir Valores, depois basta colocar o que vai substituir, por exemplo CH, por China.
Feito isso basta repetir o mesmo procedimento para os demais países, assim fica mais fácil identificar qual país estamos tratando.
Feito isso nós vamos separar a coluna Produto, pois nela nós temos tanto o produto quanto a marca, então podemos separar para poder fazer algumas análises mais detalhadas.
Para isso basta ir em Transformar > Dividir Coluna > Por Delimitador. Na janela que será aberta você vai colocar delimitador personalizado e vai colocar “ – “ (sem as aspas). Então o nosso separador vai ser espaço + traço + espaço.
Assim você vai garantir que não vai ficar um espaço a mais em nenhuma das informações e com isso vamos poder dividir a coluna em produto e marca.
OBS: Lembrando que para renomear uma coluna basta dar um duplo clique no cabeçalho e colocar o nome que quiser.
Por padrão o próprio Power Query já ajusta o formato das informações das colunas, caso isso não aconteça você clicar no símbolo a esquerda do nome da coluna para formatar corretamente suas informações.
Não sei se você notou, mas a coluna de quantidade de estoque não estava no formato brasileiro, então quando passamos para o Power Query tivemos 2 zeros a mais nessa quantidade.
Para isso vamos precisar dividir a nossa coluna por 100 para voltar ao valor original e não alterar as nossas informações.
Para isso basta selecionar a coluna, ir até Transformar > Padrão > Dividir. Depois basta colocar o valor que vamos dividir que é 100.
Feito isso nós vamos criar uma coluna de Valor Total, para isso temos que selecionar a coluna de quantidade de estoque e valor unitário e agora selecionar a opção Multiplicar.
O próximo passo é obter o nome da cidade da coluna de endereço, para isso vamos utilizar a coluna de exemplos.
Basta ir em Adicionar Coluna > Coluna de Exemplos > Da Seleção. Com isso você vai poder dar alguns exemplos ao programa para que ele entenda qual é a informação que você quer obter.
Você vai colocando alguns exemplos e ajustando até que o resultado seja o que você precisa. Nesse caso só precisamos de 3 exemplos e 1 ajuste.
Já estamos quase finalizando os nossos ajustes, o próximo temos alguns passos para criar a coluna de Dias.
Vamos selecionar a coluna de Validade ir até Adicionar Coluna > Data > Idade. Depois vamos selecionar essa nova coluna e ir até Duração > Multiplicar e fazer a multiplicação por -1 para deixar o resultado positivo.
Em seguida vamos em Duração > Dias, assim vamos ter o resultado positivo em dias da data de validade até a data atual, dessa forma vamos saber quantos dias teremos até o vencimento do produto.
Depois disso você pode deletar as duas colunas auxiliares que criamos. Pode ficar tranquilo que não vai atrapalhar a nossa última coluna.
Agora para finalizar nós vamos criar uma coluna para classificar esses dias e saber se estamos em Risco ou se estamos Seguro.
Isso é bem simples, basta ir até Adicionar Coluna > Coluna Condicional. Depois vamos configurar igual a imagem abaixo.
Pronto! Agora conseguimos classificar essa coluna e finalizar nosso tratamento de dados.
Agora para enviar os dados tratados para dentro do Excel basta ir em Página Inicial > Fechar e Carregar.
Um ponto importante agora! Se você quiser fazer o mesmo tratamento em um outro arquivo?
Basta excluir esse arquivo inicial da pasta e colocar outro com o mesmo nome. Com isso você só precisa atualizar a pasta de trabalho e pronto! Tudo atualizado.
Nessa aula eu te mostrei o que é o Power Query Excel e como você faz para sair do zero com ele com um projeto prático!
Essa ferramenta é muito útil, pois além de facilitar o seu trabalho você automatiza esse processo e não precisa utilizar fórmulas!
Além de poder fazer o tratamento da sua base de dados e manter esse processo automático para os novos dados!
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador