Blog

Postado em em 3 de março de 2021

Corrigindo Planilha Bagunçada no Power Query

Nessa aula vamos estar corrigindo planilha bagunçada do Excel dentro do Power Query, dessa forma você poderá trabalhar corretamente com esses dados!

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 a planilha que usamos na aula no seu e-mail, preencha:

Como Corrigir Planilha do Excel para Trabalhar no Power BI

Você já deve ter se deparado com essa situação ou que a própria planilha do Excel já está bagunçada ou até mesmo na hora de importar os dados não são importados corretamente.

Se já passou por isso não se desespere, porque hoje, nós vamos te ensinar como corrigir dados no Power Query.

Isso mesmo, vamos te mostrar os procedimentos que precisa fazer para que possa ajustar seus dados e utilizá-los corretamente.

Planilha do Excel Bagunçada

Vamos inicialmente olhar como está a nossa planilha em Excel para que possamos importá-la ao Power Query e fazer os devidos ajustes.

Base de dados no Excel
Base de dados no Excel

Ao olhar pela primeira vez, você pode pensar “mas essa planilha está bonita e bem dividida, qual o problema dela?”.

Para começar temos que lembrar que o Power BI ele trabalha com as informações em colunas, então é necessário que todas as informações de um mesmo tipo estejam na mesma coluna.

Aqui já temos ano em células mescladas que é um grande problema (até mesmo para o Excel), pois o programa só considera a primeira célula de fato com o valor do ano, as demais são consideradas como vazias.

Temos outra mescla de células nas cidades, temos os meses divididos em colunas e ainda temos uma coluna de total que pode nos atrapalhar dentro do Power Query.

O ideal seria que tivéssemos uma coluna para cada tipo de informação, desta forma conseguiremos trabalhar normalmente dentro do Power BI.

Base corrigida no Excel
Base corrigida no Excel

Esse seria o ideal para trabalhar dentro do Power BI, cada coluna com uma informação específica, pois facilita na análise e construção de relatórios dentro do programa.

Então o nosso objetivo é importar a tabela que não está nos padrões para dentro do Power Query e fazer o tratamento dos dados para que fique como a tabela acima.

Base dentro do Power Query
Base dentro do Power Query

Ao importar os dados ao Power Query é isso que temos. É possível notar que temos alguns problemas, então vamos aos problemas que temos e depois vamos ver como corrigir cada um deles.

  1. Linhas vazias referentes a separação de dados dentro do Excel de um ano para o outro;
  2. Informações vazias dentro da coluna de Cidade, por conta da mescla de células no Excel, que só considera a informação na primeira célula e não nas demais;
  3. Primeira coluna sem nome;
  4. Informações vazias dentro da coluna que seria de ano, pelo mesmo problema de células mescladas no Excel;
  5. Informações dos meses divididas em colunas ao invés de linhas;
  6. Coluna de total, nesse caso é um problema, pois na correção dos dados ele vai acabar entrando como um valor de quantidade e faturamento e não queremos isso;
  7. Cabeçalhos repetidos ao longo da tabela por conta da separação de ano dentro do Excel;
  8. As informações de quantidade e faturamento estão em uma única coluna, é necessário separar para dividir essas duas informações.

Essa não é necessariamente a ordem em que vamos tratar os problemas, mas é para que você possa ver quais os problemas temos.

Então temos que tratar todos eles para chegar ao resultado desejado, inicialmente vamos começar removendo as informações que não precisamos.

Corrigindo Planilha Bagunçada
Removendo as linhas em branco

1 – O primeiro que já vamos fazer é Remover Linhas em Branco. Com isso já não teremos mais a separação entre os dados.

3 – Em seguida vamos dar um duplo clique na primeira coluna para poder colocar o nome da coluna de Ano.

Corrigindo Planilha Bagunçada
Removendo dos cabeçalhos duplicados

7 – Dentro do filtro de Cidade nós vamos desmarcar a opção Cidade que é referente aos demais cabeçalhos, dessa forma vamos remover todas as linhas que contém os cabeçalhos repetidos ao longo da tabela.

Corrigindo Planilha Bagunçada
Removendo a coluna de Total

6 – Vamos clicar com o botão direito na coluna de total e selecionar a opção Remover.

2 e 4 – Para resolver esses dois problemas temos uma ferramenta que vai nos auxiliar, que é a ferramenta Preencher para baixo no Power Query.

Preencher para Baixo
Preencher para Baixo

Essa ferramenta fica dentro da guia Transformar em Preenchimento. Basta selecionar essas duas colunas de Ano e Cidade e utilizar essa ferramenta para que o programa preencha as informações vazias com as informações logo acima.

5 – Vamos agora selecionar todas as colunas de meses (pode clicar na primeira, segurar a tecla SHIFT e clicar na última). Em seguida vamos até a guia Transformar e selecionar a opção Transformar Colunas em Linhas.

Transformando Colunas em Linhas
Transformando Colunas em Linhas

Feito isso teremos todos os meses em apenas uma coluna, e é assim que nossa base está até o momento.

Corrigindo Planilha Bagunçada
Base de dados até o momento

Então já temos praticamente tudo pronto, com as informações corretas, só nos falta corrigir a coluna de indicador para que possamos separar o que é valor de quantidade e o que é valor de faturamento para não misturarmos essas informações.

OBS: Lembrando que é necessário alterar o nome da coluna de mês, pois o procedimento deixou essa coluna com o nome de atributo.

8 – Para separar essa última coluna que ainda está com problema vamos selecioná-la e ir até a opção Coluna Dinâmica, ainda dentro da guia Transformar.

Ferramenta Coluna Dinâmica
Ferramenta Coluna Dinâmica

Ao clicar nessa opção o programa vai abrir uma nova janela para que possamos escolher a coluna que vamos separar de acordo com essas informações.

Que é exatamente a nossa coluna de valores que contém valor de quantidade e faturamento. Que não podem estar juntos!

Configurando a ferramenta coluna dinâmica
Configurando a ferramenta coluna dinâmica

OBS: É necessário clicar em Opções Avançadas e selecionar a opção Não Agregar.

Feito isso nossa tabela está completamente tratada para que possamos utilizá-la corretamente dentro do Power BI.

Corrigindo Planilha Bagunçada
Base de dados corrigida

Temos todas as informações que mostramos anteriormente na base corrigida para que possamos utilizar os dados dentro do Power BI.

Conclusão Corrigindo Planilha Bagunçada

Foram necessário vários passos dentro desse tratamento para estar corrigindo planilha bagunçada do Excel para trabalhar corretamente dentro do Power BI.

É muito importante esse tratamento para garantir que os relatórios e gráficos terão as informações corretas, pois no meio daquela bagunça poderíamos estar mostrando informações erradas.

E ninguém quer um relatório cheio de informações erradas, por isso é muito importante esse tratamento de dados antes de qualquer análise.

Então é necessário certificar de que as informações estão separadas corretamente para evitar erros futuros.

Agora que você aprendeu como tratar uma base de dados que parecia organizada, e dentro do Power BI ela vira uma bagunça.

Agora você pode estar corrigindo planilha bagunçada sempre que precisar para fazer os tratamentos necessários e sua análise de dados.

Hashtag Treinamentos

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


Quer aprender mais sobre Power BI com um minicurso gratuito?