Nessa publicação vamos aprender várias dicas e truques sobre o Power Query para você conseguir impressionar com suas análises e relatórios!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nesta publicação, clique aqui!
O Power Query é um editor de consultas/tabelas que permite ao usuário fazer diversas modificações nessas consultas/tabelas de forma que fiquem da maneira que o usuário deseja para que possa importar ao Excel ou Power BI apenas para criar os relatórios e Dashboards sem que haja a necessidade de efetuar mais mudanças.
Vamos utilizar o Power Query sempre que quisermos fazer modificações dentro de consultas/tabelas a fim de formatar dados, inserir colunas, mesclar tabelas, entre outras funcionalidades. É uma forma de tratar dados antes de utilizá-los propriamente dentro do ambiente Excel ou Power BI.
Primeiramente vamos abrir o Power BI e começar a importar um arquivo de Excel, que contém os dados a serem utilizados, para dentro deste ambiente. Esse é o passo a passo padrão sempre que quisermos importar dados para dentro do Power BI.
Para iniciar a importação vamos até a opção Obter Dados e selecionar Excel, pois importaremos uma planilha que está neste formato.
Opção para obter dados do Excel
Em seguida vamos selecionar a planilha que queremos obter. Feito isso teremos uma prévia dos dados dentro dessa janela.
Selecionando a planilha para ser importada
Ao selecionar a planilha desejada é possível notar que no canto inferior direito desta janela temos três opções: Carregar, Transformar Dados e Cancelar. A primeira opção faz a importação direta desses dados diretamente para o ambiente do Power BI, enquanto a segunda opção nos leva para dentro do ambiente Power Query, onde será possível fazer algumas edições e formatações antes de importar os dados para o ambiente do Power BI de fato.
Planilha dentro do Power Query
Feito isso estaremos dentro do ambiente Power Query com os dados da nossa tabela para análise. No primeiro momento vamos verificar se as colunas estão com os formatos corretos para os tipos de dados representados.
Para fazer essa verificação basta observar se o símbolo que fica ao lado esquerdo de cada coluna está relacionado aos dados da coluna. Caso não esteja basta clicar neste símbolo e escolher a informação adequada.
O próximo passo é alterar o nome dessa tabela para que fique mais fácil de identificar do que se trata essa tabela depois que importarmos ela ao programa.
Alterando o nome da planilha
Esse nome pode ser alterado no campo que se encontra ao lado direito do programa. Não é recomendado a utilização de caracteres especiais, pois podemos ter problemas com as fórmulas do Power BI por conta disso.
Como primeiro exemplo vamos obter somente a cidade em que cada um desses funcionários trabalha. Para fazer as alterações na tabela teremos sempre duas opções, que estão divididas em duas guias: a guia Transformar e a guia Adicionar Coluna.
Guias para adicionar ou modificar uma coluna
Essas duas guias possuem praticamente as mesmas ferramentas, no entanto a grande diferença entre elas é que a guia Transformar executa a ação na coluna selecionada, ou seja, a coluna que está sendo utilizada será alterada para chegar o resultado desejado. Enquanto a guia Adicionar Coluna irá criar uma coluna com o resultado da ação desejada, ou seja, utilizando essa guia teremos no final duas colunas, a coluna inicial e a coluna com o resultado.
Sabendo disso vamos até a guia Adicionar Coluna para obter somente a cidade em que os funcionários trabalham. Para isso vamos até a opção Extrair e em seguida em Texto Antes do Delimitador.
Opção para extrair parte do texto
Estamos utilizando essa opção porque na coluna de Localidade temos um padrão, ou seja, temos o nome da cidade, um traço e o estado. Esse traço irá servir como nosso delimitador para separar a cidade do estado de cada funcionário.
Inserindo o delimitador para a extração de texto
Neste caso vamos utilizar o traço como delimitador. Uma possibilidade dependendo do caso poderia também ser utilizado o espaço, no entanto se utilizarmos o espaço neste caso algumas cidades não seriam obtidas por completo como é o caso do Rio de Janeiro.
Se colocar como delimitador o espaço neste caso teríamos como resultado apenas Rio como o nome da cidade, por isso vamos utilizar o traço como delimitador.
Resultado da extração de texto
Feito isso teremos uma nova coluna somente com os nomes das cidades, que foi exatamente a informação que tínhamos antes do delimitador. Falta apenas renomear a coluna, para isso basta dar um duplo clique no cabeçalho e alterar o nome.
Uma “ferramenta” muito interessante dentro desse ambiente do Power Query é que todas as modificações feitas pelo usuário ficam registrada dentro da parte de Etapas Aplicadas.
Lista de ações que foram executadas
Então todas as ações ficam gravadas, mesmo as feitas automaticamente que foram as 4 primeiras atividades. É possível também visualizar como a planilha estava em um determinado ponto apenas clicando em uma dessas etapas.
É possível também desfazer qualquer uma dessas etapas clicando no x do lado esquerdo de qualquer uma delas. Desta forma caso alguma etapa não seja mais necessária ou o usuário queira voltar a etapa anterior basta excluí-la. Isso é fundamental, pois dentro do Power Query não temos a opção de utilizar o CTRL + Z que é a opção padrão para desfazer.
Agora vamos supor que o usuário queira retirar informações da coluna de endereço.
Coluna de endereços para obtermos dados do meio do texto – Power Query
Aqui é possível observar que já temos informações e símbolos diferentes do que tínhamos ao obter as cidades, no entanto temos uma ferramenta que pode nos ajudar que se chama Coluna de Exemplos. Essa ferramenta fica dentro da guia Adicionar Coluna.
Selecionando a Coluna de Exemplos
Ao selecionar essa opção o Power Query irá abrir uma nova tela com uma coluna em branco para que o usuário escreva os dados que deseja obter dessa coluna selecionada.
Inserindo um exemplo para que o programa econtre um padrão
Na primeira linha vamos escrever a informação que queremos retirar da coluna, neste caso será o bairro, então na primeira linha vamos escrever Ipanema e pressionar enter. Feito isso o programa irá dar sugestões para as próximas linhas para verificar se é essa a informação que queremos.
Como o programa vai tentando achar o padrão, o que vamos fazer é continuar preenchendo mais alguns exemplos para que possamos ter o resultado desejado.
Colocando mais exemplos até o programa obtenha somente a informação desejada
É possível observar que a partir do terceiro exemplo o programa já consegue obter todas as informações de bairro corretamente. Agora para aceitar a sugestão basta pressionar OK que teremos uma nova coluna com essas informações.
Resultado da extração de dados
Feitas essas alterações é válido lembrar que elas só ocorrem dentro do ambiente Power BI, ou seja, o arquivo em Excel não será alterado nem terá essas colunas extras. No entanto se acrescentarmos dados dentro do arquivo Excel e salvar é possível trazer essas alterações para o Power BI.
Adicionando dados no arquivo em Excel
No arquivo em Excel vamos adicionar o funcionário 14 e em seguida salvar. Agora basta ir até o ambiente Power BI na guia Página Inicial e selecionar a opção Atualizar Visualização.
Opção para atualizar a visualização da planilha – Power Query
Ao selecionar essa opção os dados já serão atualizados automaticamente inclusive as novas colunas que foram criadas de Cidade e Bairro.
Atualização do ambiente Power Query com as informações inseridas no Excel
Então sempre que uma atualização na tabela for feita o Power BI consegue obter esses novos dados e fazer todas as etapas que foram feitas para essas novas informações.
Vamos agora trabalhar com a coluna de Entrada na Empresa. Com essa coluna selecionada vamos até a guia Adicionar Coluna e selecionar a opção Data.
Inserindo uma coluna de Idade para calcular o período de uma data até o dia atual – Power Query
Aqui temos algumas opções que podemos selecionar para obter dados dessa coluna que foi selecionada. Existem diversas opções para facilitar a obtenção de dados pelo usuário. Para o nosso caso vamos utilizar a opção Idade que será responsável por calcular o período entre a data selecionada e a data atual.
Resultado da coluna de Idade
O pequeno problema que temos ao criar essa coluna é que o período não é dado em anos e sim em dias, ou seja, foi calculada a quantidade de dias entre a data analisada e a data atual.
Como é algo simples podemos resolver de duas maneiras, uma delas é clicando com o botão direito do mouse na coluna, indo até a opção Transformar e depois em Total de Anos.
Transformando os dados para anos – Power Query
A outra opção é indo até a guia Transformar, selecionar a opção de Duração e por fim a opção de Total de Anos.
Outra opção para a transformação de dados
Utilizando qualquer uma das duas opções teremos o mesmo resultado, que é o período em anos.
Resultado da transformação de dados
No entanto por conta da fórmula que o programa utiliza não temos um valor somente de anos, o programa leve em conta os dias restantes para completar o outro ano. Como queremos apenas mostrar a quantidade de anos sem nenhuma casa decimal vamos arredondar esses valores.
Para efetuar o arredondamento basta ir até a guia Transformar, selecionar a opção Arredondamento e em seguida Arredondar para Baixo.
Opção para arredondar os valores da coluna Idade
Vamos utilizar essa opção porque não vamos considerar 1 e 8 meses por exemplo como dois anos de empresa, será considerado apenas como 1 ano.
Resultado do arredondamento de valores
Com isso temos a coluna de tempo de empresa completa com os valores em anos sem a consideração dos meses.
Feito isso vamos agora fazer a classificação desses funcionários baseada na quantidade de tempo que eles possuem na empresa. Para isso vamos criar uma Coluna Condicional, essa ferramenta se encontra na guia Adicionar Coluna.
Opção para inserir uma coluna condicional
Ao selecionar essa opção será aberta uma janela para colocarmos as condições para a nova coluna que será criada.
Condições para a criação da coluna de status na empresa
Essa é uma ferramenta bem simples, mas ao mesmo tempo bem importante, pois permite ao usuário classificar dados de uma forma bem intuitiva. Neste caso vamos dividir o tempo dos funcionários na empresa em 3 grupos. Serão divididos da seguinte maneira, caso tenha menos do que 5 anos de empresa será Jovem Aprendiz, caso tenha menos que 10 anos de empresa será Experiente e nos outros casos será considerado Sênior.
Resultado da coluna condicional – Power Query
É possível observar que ao pressionar OK teremos essas classificações feitas para todos os dados de forma automática. Podemos também observar que ao lado esquerdo do cabeçalho dessa coluna temos ABC 123. Isso quer dizer que não temos nenhum formato definido de texto ou número.
Ao clicar nessa opção podemos fazer a alteração do formato para texto, ou para qualquer formato desejado referente aos dados que estão na coluna.
Opção para alterar o formato dos dados na coluna
Para este caso vamos escolher a opção de Texto, pois os dados que temos são textos.
Para a próxima ação vamos utilizar a Coluna Personalizada para poder saber a quantia total de gastos por cada funcionário.
Opção para criar uma coluna personalizada
Ao selecionar essa opção teremos uma nova janela onde poderemos escolher o nome da coluna a ser criada e a fórmula que será utilizada para essa coluna. Uma informação importante é que o Power BI diferente do Excel não faz referência as células, ou seja, não referência dado por dado, ele faz essa referência por coluna.
Fórmula para a criação da coluna de salário total
Desta forma basta selecionar as colunas desejadas, as inserir e colocar o símbolo de + entre esses valores a serem somados.
Resultado da coluna personalizada – Power Query
Desta forma teremos a soma de todos os gastos com cada um dos funcionários para que o usuário possa fazer uma análise mais detalhada. É possível observar que não temos uma formatação específica para essa coluna, basta clicar no símbolo ABC 123 e modificar para Número Decimal.
Feitas todas as modificações desejadas podemos enviar esses dados para o ambiente do Power BI na opção de Fechar e Aplicar na guia Página Inicial.
Opção para importar os dados ao Power BI
Feito isso teremos todos esses dados dentro do ambiente Power BI. É possível verificar essas informações na guia Dados.
Acessando a guia de dados para verificar a planilha dentro do Power BI
As informações estarão da forma que foram formatadas e terão as colunas novas que foram criadas.
Planilha completa e formatada dentro do Power BI – Power Query
Foi possível aprender algumas ferramentas importantes do Power Query para a formatação dos dados antes de utilizá-los dentro do ambiente Power BI. Isso é interessante para que possamos tratar os dados antes de fazer qualquer relatório ou Dashboard dentro do programa.
Você já viu nossos conteúdos gratuitos que disponibilizamos além de nosso curso de excel? Clique para saber mais!
Quer aprender mais sobre Power BI com um minicurso gratuito?
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador