Blog

Postado em em 27 de novembro de 2022

Exercícios de Power Query do Power BI para Praticar, Casos Reais!

Nesta aula vamos ensinar a resolver exercícios de Power Query do Power BI usando situações reais e corriqueiras do dia a dia de uma empresa!

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) utilizados na aula, preencha:

Fala impressionadores! Não se esqueçam de baixar o material que deixei disponível para download, treinar resolver os exercícios de Power Query é tão importante quanto ver as aulas.

Vamos resolver em um passo a passo simples alguns exercícios com situações comuns em um dia a dia de trabalho.

O que vamos ver nesta aula:

  • Importação de tabelas;
  • Criação de coluna para cálculo de salário + imposto;
  • Coluna com avaliação dos funcionários por pontuação;
  • Cálculo de tempo de empresa;
  • Extraindo informações do endereço:
  • Carregar o Power Query para o Power BI;
  • Cadastro de novos funcionários;
  • Atualizações
  • Correção de erros;
  • Juntar tabelas;
  • Erro por planilha renomeada.

Bom pessoal estes são os exercícios de Power Query, bora para a aula!

1) Primeiro vamos exportar e editar a planilha de Excel, nestes exercícios de Power Query alguns cuidados têm que ser tomados:

Lembre-se de sempre fazer as edições pelo Power Query e não pelo Excel. Isso porque se for uma planilha que a empresa baixa de um sistema, ela sempre vem no mesmo formato, então a ideia é sempre editar pelo Power Query para deixar automático para quando entrarem novas informações.

Repare que na planilha original, além da tabela, tem um texto escrito (no nosso cenário, a planilha veio assim do sistema). Então você vai precisar editar a tabela para só ter as informações que interessam, ou seja, a tabela sem esse texto que aparece na primeira linha da planilha.

Tabela - Exemplo.
Tabela – Exemplo.

Edição da tabela dentro do Power BI.

Temos na nossa tabela de funcionários ativos informações de número de cadastro, nome, gênero, data de nascimento, endereço da sede da empresa que o funcionário trabalha, data de contratação, salário, VR, VT, cargo e área, e a avaliação do funcionário.

Vamos abrir nosso Power BI e importar a planilha, vá em página inicial clique em Pasta de trabalho do Excel e escolha a planilha que vamos utilizar (BaseFuncionarios – Ativos).

Clicando na planilha vai abrir o Navegador com o nome da pasta, logo abaixo o nome das planilhas que temos dentro da pasta, selecione a planilha de funcionários ativos.

Exercícios de Power Query
Exportar planilha
Exportar planilha
Exportar planilha

Após isso, clique na opção “Transformar dados”, sempre vamos clicar nesta opção quando quisermos editar o arquivo.

Perfeito, este é o ambiente do Power Query!

Vamos começar nos livrando das linhas desnecessárias da nossa tabela, havia duas linhas, observe que o Power Query transformou uma delas em cabeçalho, o que não faz muito sentido para nós.

Exercícios de Power Query
Null.

Nosso objetivo é retirar a linha que agora aparece como cabeçalho e a linha abaixo dela que agora aparece como vazio (null).

Vamos começar retirando a linha vazia (null), clique em “página inicial” depois em “remover linhas”, vai aparecer diversas opções para remover a linha, remover as linhas superiores, inferiores, etc.

Vamos clicar na primeira, remover linhas superiores, vai aparecer uma caixinha pedindo que você informe a quantidade de linhas superiores que você deseja remover, neste caso coloque 1.

Exercícios de Power Query
Remover linhas superiores.

Agora vamos tratar do cabeçalho, vale dizer que este é um problema muito comum nos exercícios de Power Query, é comum ao exportar a planilha perceber que o cabeçalho dela está fora de lugar.

Nosso cabeçalho correto está na primeira linha da tabela, como vamos transformar a linha 1 em cabeçalho?

Como este é um problema comum, temos dentro da guia página inicial a opção “Usar a primeira linha como cabeçalho” clicando nesta opção não só a primeira linha se torna o cabeçalho como também o cabeçalho anterior é excluído.

Feito a importação e tratamento das linhas vamos agora criar uma coluna para calcular o salário com imposto que é de 60%:

Para conseguir fazer cálculos dentro do Power Query temos primeiro que selecionar a coluna com os valores que serão usados no cálculo (coluna – salário), a intenção não é modificar esta coluna e sim criar uma coluna com o valor do salário mais imposto.

Clique na coluna com os salários e depois na guia “Adicionar coluna”, vá em “Cálculos-padrão” selecione “Multiplicar”.

Agora vai aparecer uma caixinha para preencher por quanto queremos multiplicar a coluna de salário, vamos colocar 1,6 e dar ok.

Obs. Não conseguimos preencher esta caixinha com o número em percentual (exemplo: 60%) por este motivo temos que colocar 1,6 que seria o valor do salário + 60%.

Feito isso a coluna “Multiplicação” será criada com os valores da coluna salário acrescida de 60%. Para renomear a coluna é simples basta dar dois cliques nela e modificar o nome para “Salário com imposto” por exemplo.

Exercícios de Power Query
Cálculo padrão.

2) Crie uma coluna de ‘Gasto Salarial’, somando as colunas de Salário com Imposto, VR e VT:

Para somar estes 3 valores teremos que selecionar as três colunas, vamos clicar na tecla Control no nosso teclado e com a tecla pressionada selecione as três colunas que vamos somar.

Vá novamente na guia “Adicionar coluna”, vá em “Cálculos padrão” selecione “Adicionar”, a nova coluna está criada, precisamos apenas renomear para “Gasto Salarial”.

3) Crie uma Coluna com a avaliação do funcionário conforme a nota dele:

■ Maior ou igual a 7: Boa;

■ Maior ou igual a 5 e menor que 7: Razoável;

■ Abaixo de 5: Ruim.

Para fazer estas condições no Power Query vamos na guia “Adicionar coluna”, clique em “Coluna condicional”.

Dentro da caixinha que vai aparecer você vai nomear a coluna (Status funcionário) e depois completar a condição de forma muito intuitiva, como se estivesse falando.

“Se a avaliação do funcionário for maior ou igual a sete queremos escrever a palavra “Boa”.”

Após “Se” selecione a coluna que será o nosso parâmetro, neste caso “Avaliação do funcionário” em seguida o operador “é maior que ou igual a” -> valor “7” -> Saída “Bom”.

Agora vamos clicar em “Adicionar Cláusula” para os casos em que a pontuação do funcionário não for maior ou igual a 7.

Como ele só vai executar a segunda condição caso a primeira não tenha funcionado então podemos colocar:

Se “Avaliação do funcionário” -> operador “é maior que ou igual a” -> valor “5” -> Saída “Razoável”.

Terceira e última condição:

Já que esta é a última condição e todos os casos que não estiverem compatíveis com as duas primeiras condições farão parte desta regra, nós não precisamos adicionar mais uma cláusula.

Temos a opção “Senão” nela vamos apenas escrever “Ruim”, ou seja, se não for nenhuma alternativa anterior vai resultar em “Ruim”.

Exercícios de Power Query
Coluna condicional.

Agora que a coluna foi criada observe que cada coluna está definida com um “tipo de informação”, esta nova coluna não possui este tipo, esta como ABC123 ao lado do nome, clique sobre o ABC123 e selecione a opção Texto, desta forma o Power BI sabe como trabalhar com as informações dessa coluna.

Exercícios de Power Query
Tipo – texto.

4) Crie uma coluna para calcular a idade do funcionário e outra para calcular o tempo de empresa do funcionário (em anos).

Vá na guia “Adicionar coluna” -> clique na coluna “Data de nascimento”, ainda na mesma guia clique em “Adicionar coluna”, depois clique em Data -> Idade.

Note que foi criada uma coluna com a idade calculada em dias, para passar para anos vamos clicar no cabeçalho da coluna como botão direito do mouse, vá em transformar e escolha a opção “Total de anos”.

Observe que o resultado agora está por ano, mas queremos que mostre o número inteiro.

Para resolver isso precisamos ir à guia “Transformar” esta guia é semelhante a guia “Adicionar coluna”, mas ela apenas altera a coluna existente sem criar uma nova.

No canto direito da guia Transformar clique na opção “Arredondar” nesta opção você pode escolher entre arredondar para cima para baixo ou para o número mais próximo, selecione “Arredondar para baixo” para ficar compatível com a idade.

Cálculo do tempo de empresa do funcionário em anos:

Para calcular o tempo de empresa do funcionário em anos vamos fazer o mesmo que foi feito para calcular a idade, a única diferença é que vamos usar a coluna “Data de contratação”. No restante seguiremos exatamente o passo a passo acima.

Aprenda mais sobre como calcular indicadores importantes, exercícios de Power Query!

5) Crie uma coluna com a cidade do funcionário – extraindo a cidade do endereço completo.

Neste exercício estamos querendo a partir da coluna onde temos o endereço completo extrair a cidade, como fazemos isso?

Se houver um padrão na organização do endereço você pode ir à guia “Adicionar coluna” ir em “Extrair” e selecionar o modo como vai fazer a extração.

Neste caso não temos um padrão na forma como os endereços foram escritos.

Para resolver vá na guia “Adicionar coluna” -> “Coluna de Exemplos” o próprio nome é bem intuitivo, nesta opção você vai dar exemplos para o Power BI de como você quer extrair a informação.

Exercícios de Power Query
Coluna de exemplos.

A ideia aqui é inserir tantos exemplos quanto forem necessários para que o Power BI entenda o padrão e replique sem que precisemos fazer linha por linha.

Lembre-se, na hora de preencher a nova coluna preencha os nomes das cidades idênticos à forma como está escrito na coluna de “Endereço sede”. Escreva o nome da Cidade na primeira linha e dê enter, observe como as linhas abaixo estão sendo completadas, se estiver errado continue escrevendo quantas linhas forem necessárias até que o padrão seja identificado pelo Power BI.

Na segunda linha deste exemplo o Power BI já conseguiu entender o padrão, clique em ok e pronto, está feito.

Definindo padrão.
Definindo padrão.

Obs. Não se esqueça de renomear a coluna (Cidade).

Quer saber mais sobre como trabalhar com colunas no Power BI? Clique no link, temos uma aula sobre a ordem das colunas!

Agora vamos fechar e carregar a tabela do Power Query para o Power BI.

É muito simples, vá na guia página inicial e clique em “fechar e aplicar” você está dizendo para o Power BI que já acabou fazer as alterações, então ele pode pegar a planilha de Excel, efetuar as modificações e aplicá-las.

Fechar e aplicar.
Fechar e aplicar.

Alterações aplicadas!

Em seguida, acrescente um novo funcionário na planilha do Excel:

– Cadastro Funcionário: FC235;

– Nome: João Pinheiro – Gênero: Masculino;

– Nascimento: 19/08/1992;

– Endereço: R. Visc. de Pirajá, 136 – Ipanema, Rio de Janeiro – RJ, 22410-000;

– Data de Contratação: 10/10/2022;

– Salário: R$8500;

– VR (Vale Refeição): R$600;

– VT (Vale Transporte): R$160;

– Cargo e Área: Analista – Comercial;

– Nota: 7,5.

Vamos abrir a planilha de Excel e adicionar este novo funcionário na última linha da tabela.

6) Vamos salvar e corrigir o erro que vai aparecer.

Erro.
Erro.

Clique em exibir erros, observe que o erro está na planilha “Cadastro RH” clicando nela vamos ver o que aconteceu.

Veja que o funcionário novo tem no registo do RH letras e não somente números como os demais.

Essa mudança de padrão no registro da empresa trouxe para uma coluna formatada apenas para ter números algumas letras e por isso surgiu o erro.

Para corrigir é muito simples, eu recomendo que você clique em cada etapa aplicada e observe a barrinha verde que aparece logo abaixo do cabeçalho, se tiver verde está ok se for vermelho ou pontilhado tem algum erro para corrigir.

Observe na barra que o “Cadastro RH” esta como “Int64 Type” que significa número inteiro, selecione Tipo Alterado:

Após ter aparecido o erro, tentar modificar o tipo da coluna não vai mais funcionar.

Neste caso temos que corrigir o problema na nossa barra de fórmulas como na foto abaixo, se no seu Power BI ela não estiver aparecendo, selecione-a na guia exibição.

Correção do erro.
Correção do erro.

Agora só precisamos trocar na barra de fórmulas o “Int64 Type” por “Type text” na coluna Cadastro RH, fazendo isso podemos atualizar e depois excluir na parte de consultas os erros que haviam aparecido.

Se ainda tem dúvidas vou deixar uma aula sobre erros ao importar arquivos, clique no link.

7) Agora imagine que a empresa te enviou a “BaseFuncionarios – Desligados” com os funcionários que já saíram da empresa. O objetivo é você juntar essa tabela com a tabela de funcionários ativos, na mesma base de dados.

Primeiro vamos importar a planilha, em página inicial selecione a nova planilha e clique em importar dados para abri-la no Power Query.

Altere o nome da pasta para “BaseFuncionarios Desligados” para ficar diferente da primeira planilha importada.

O que vamos precisar editar?

Como as tabelas têm que estar com os mesmos tipos de informação e edição, vamos fazer nesta tabela tudo o que fizemos na de funcionários ativos.

Após as edições temos que escolher entre criar uma terceira tabela ou efetuar a junção em uma das tabelas prontas.

Para este exemplo vamos criar uma 3ª tabela, clique em qualquer uma das tabelas na parte de consultas e na guia página inicial clique em “Combinar”, em seguida selecione “Acrescentar Consultas como Novas”.

Combinar tabelas.
Combinar tabelas.

Agora vai aparecer uma caixinha pedindo a quantidade de tabelas que vamos usar e quais as tabelas que queremos adicionar, coloque as nossas duas tabelas completando os espaços e clique em ok.

Se você observar agora, vai ver que a tabela de funcionários desligados está logo abaixo da tabela de funcionários ativos, conseguimos juntar as duas tabelas em um só arquivo.

Não se esqueça de dar um nome a tabela, temos esta opção no canto superior direito em “Propriedades”, vamos chamá-la de “BaseFuncionarios Completa”.

Vamos para a última questão!

8) Imagine que alguém renomeou a aba do Excel de ‘BaseFuncionarios’ para ‘Funcionarios Ativos’ – Volte na planilha e renomeie para simular esse cenário. Em seguida, atualize o Power BI e corrija o erro que deu.

Quando a planilha de Excel que o Power BI está usando para obter dados é renomeada, o Power BI não consegue mais achar o caminho para o arquivo e isso vai resultar em um erro, vamos na nossa planilha de Excel modificar o nome e aprender a corrigir este erro.

Após modificar o nome vá no Power Query e clique em “aplicar e modificar”.

Falhas.
Falhas.

Dentro do Power Query vamos clicar em transformar dados -> Atualizar Visualização.

Vai aparecer uma mensagem dizendo onde está o erro ou nós podemos clicar nas etapas até encontrá-lo.

Causa da falha.
Causa da falha.

Neste caso o Power BI está procurando uma planilha (Sheet) chamada BaseFuncionarios que ele não consegue encontrar.

Na barra de fórmulas após Item= você tem o nome antigo da planilha, você só tem que apagar e substituir pelo nome atual.

Na barra de fórmula vai ficar:

= Fonte{[Item=”Funcionarios Ativos”, Kind=”Sheet”]}[Data]

Coloque o nome idêntico com as letras maiúsculas e minúsculas e se houver caractere também deve ser colocado idêntico como no nome atual.

Pronto agora é só voltar na guia página inicial e clicar em fechar e aplicar.

Quer uma aula focada em como corrigir erros no Power BI, clique no link!

Terminamos nossos exercícios de Power Query! Vou deixar mais alguns links de aulas que vão te ajudar no seu aprendizado!

Mitos e verdades do Power BI;

Atalhos do Power BI que você precisa conhecer;

Como se destacar em Power BI mesmo ainda não sabendo nada.

Conclusão – Exercícios de Power Query do Power BI

Esta aula foi pensada como uma forma de estudo prático do Power BI.

Nós vimos como criar colunas, excluir linhas, calcular porcentagem e soma, cálculo baseado em condições, correção de erros etc.

Tenho certeza de que é a melhor forma de aprender a usar a ferramenta, treinando de forma prática.

Todas as situações resolvidas nestes exercícios são comuns em ambientes de trabalho e vão te deixar afiado para resolver problemas.

Eu fico por aqui! Forte abraço,

Hashtag Treinamentos

Para acessar outras publicações de Power BI, clique aqui!


Quer aprender mais sobre Power BI com um minicurso básico gratuito?

Quer ter acesso a um Minicurso de Finanças no Excel de forma 100% gratuita? Preencha seu e-mail abaixo!