BLOG DOS
INCOMPARÁVEIS

Você costuma extrair dados do sistema pra elaborar seus relatórios? Esses dados estão organizados, prontos para usá-los? Você precisa manipular os arquivos periodicamente ? Perde um tempão fazendo isso? O Power Query pode ser seu grande aliado nessa tarefa além de te ajudar a evitar retrabalhos, sabia? O conteúdo do post de hoje será sobre isso!

O cenário mais comum nas empresas é: quando você extrai um relatório do sistema ele vem baguçado! Ou seja, você vai precisar arrumar ele no Excel (puro)! Sem o Power Query essa tarefa acaba sendo bastante trabalhosa…

Ah, lembrando que o Power Query existe no Excel desde 2010 como add-in (em 2016 virou nativo) mas pouca gente usa esse recurso no Excel.

A grande vantagem de usar o Power Query é que ele vai proporcionar a automatização das transformações que você realizar. Bora aprender a fazer transformações no Power Query no Power Bi Desktop?

Nosso objetivo hoje será focar na etapa de transformação. Lembra daquela sigla ETL? Refrescando sua memória: Extract ⇒ Transform ⇒ Load (Extrair, Transformar, Carregar).

Clique Aqui para dominar o Power Query, e aprenda a fazer as mais complexas conexões e transformações de dados com a Linguagem M.

Cenário 1

Vamos simular que extraímos relatórios anuais de vendas de um sistema: Vendas 2017.xlsx, Vendas 2018. xlsx e Vendas 2019.xlsx.

Em geral, caso os dados tivessem sido extraídos diretamente de um Banco de Dados SQL, Oracle, etc, não seria necessário tratar esses dados já que eles provavelmente possuiriam colunas e conteúdos padronizados e estruturados. Diferentemente dos nosso exemplo aqui. Veja como é a estrutura do nosso exemplo:

Já podemos notar que temos os seguintes problemas nesse arquivo:

1. Tem um cabeçalho que não serve para nada (esse cabeçalho é repetido pra todos os arquivos da pasta);
2. Os dados de vendas estão sendo mostrados por Região, UF e Cidade (além de estarem em células mescladas)
3. Os meses estão distribuídos por coluna;
4. Há uma linha e coluna com o total (que não precisamos já que podemos utilizar medidas para calcular isso).

Lembrando que, como esses arquivos foram extraídos de um sistema, eles possuem a mesma estrutura, então o tratamento a ser aplicado será igual nos 3 arquivos, beleza?!

Importação de dados

Importando arquivo por arquivo

Agora, vamos iniciar importando os arquivos! Para importar o arquivo Vendas 2017.xlsx, siga essas etapas:

Etapas:
Página Inicial ⇒ Obter Dados ⇒ Excel ⇒ Selecione o arquivo Vendas 2017.xlsx ⇒ Transformar Dados

Dica:
Sempre clique em Transformar dados (não clique em Carregar direto)! Lembra que estamos fazendo o ETL? A transformação (T) vem antes do carregamento (L), ok?!

Lembra que temos 3 arquivos? E que esses arquivos tem a mesma estrutura? Se fizermos esse tipo de importação (via arquivo de Excel, um por um) teremos que realizar o tratamento de cada arquivo (e são 3 !). Vamos fazer melhor! Vamos importar uma pasta! É bem fácil!

Importando uma Pasta

Para importar uma pasta do seu computador, siga as seguintes etapas:

Etapas:
Página Inicial ⇒ Obter Dados ⇒ Excel ⇒ Pasta ⇒ Selecione a pasta onde estão os arquivos ⇒ Conectar (ou duplo clique).

Em seguida, aparecerá uma janela solicitando o caminho da pasta onde estão seus arquivos. Você tem duas opções: clicar em Procurar para localizar a pasta ou ir até a pasta no seu computador ⇒ copiar o caminho completo da pasta ⇒ colar nesse campo, conforme figura:

Você verá uma lista dos arquivos que estão nessa pasta na próxima janela. Confira se todos os arquivos de vendas estão na pasta e clique em Transformar Dados:

Note que o arquivo de extensão PBIX é esse arquivo do Power Bi que estamos trabalhando e precisamos removê-lo da lista de arquivos a serem tratados.

Dica:
Para remover arquivos que não temos interesse em tratar no Power Bi (Power Query), basta removê-lo da lista utilizando o filtro na coluna Extensão (desmarque a opção que deseja remover).

De forma intuitiva ao clicarmos no botão de combinar arquivos veremos que o Power Bi gerará uma série de etapas automáticas para combinar os arquivos.

Veja o resultado que obtivemos ao clicar em Combinar Arquivos na janela que aparecerá após clicar no botão da figura acima:

Ao utilizar essa combinação automática alguns “problemas” podem surgir sendo os principais:

• Organização e estética: seu Power Query ficará com algumas “sujeiras” (veja essa pastas “Transformar Arquivo de Vendas…”, “Consultas Auxiliares”, etc);
• Qualquer alteração posterior em algum dos arquivos importados, como por exemplo, alterar os nomes das abas das planilhas, irá gerar erros na atualização dos seus dados no Power Query. Isso ocorrerá porque ele não será capaz de encontrar os nomes de origem (quando foi realizado o processo de ETL na primeira vez).

Vamos simular uma alteração no nome de cada arquivo. Antes todos os arquivos tinham suas abas com nome Planilha1 e agora se elas vão se chamar 2017, 2018 e 2019. Ao clicar em Atualizar veja o que aconteceu em amarelo:

Veja que não foi possível encontrar o mesmo nome de aba em arquivos diferentes. Já deu pra entender que não foi uma boa escolha nesse caso, certo?! Então como vamos reverter isso? Simples! Vamos deletar algumas etapas clicando no “x” ao lado de cada etapa aplicada e deixar apenas as três primeiras. Ah, também vamos excluir tudo que não for nossa consulta Vendas Anuais também. Nesse último caso, basta clicar com botão direito sobre os demais itens em Consultas ( que estão no lado esquerdo) e clicar em Excluir. Ficou assim:

Vamos coletar os dados de cada arquivo de uma outra forma: através de uma Coluna Personalizada. Vá em Adicionar Coluna Personalizada ⇒ Digite Conteúdo no campo de nome da nova coluna ⇒ Insira a fórmula abaixo ⇒ Ok.

Fórmula:
Excel.Workbook([Content])

Mas afinal, o que tem essa coluna Conteúdo que adicionamos? Ela vai ter todo o conteúdo de cada arquivo Excel da nossa pasta. Ela vai pegar todas as linhas de cada um dos nossos arquivos e empilhar esse conteúdo, de forma que fiquem o conteúdo de cada arquivo um embaixo do outro.

Vamos deletar as colunas que não precisamos e deixar apenas as colunas Name e Conteúdo.

Etapas:
Página Inicial ⇒ Escolher Colunas ⇒ Selecione Name e Conteúdo ⇒ Ok.

Para expandir clique no botão conforme figura:

Veja, após expandir as colunas, que não precisamos de algumas colunas.

Para não precisar criar outra etapa, vamos editar a etapa anterior (Conteúdo expandido) conforme figura acima. Deixe apenas a coluna Data selecionada (não é Data de dia não, ok?! É Data de Dados). Por fim, clique em Ok.

Agora vamos expandir o conteúdo da coluna Data. Clique no botão de expandir dessa e deixe selecionado apenas Data (e depois, clique em Ok). Veja:

Agora vamos expandir todas as colunas de Data. Mesmo esquema: clique naquele botão pra expandir todas as colunas (deixe todas selecionadas) e no final clique em Ok. Teremos o seguinte resultado:

Procuro sempre seguir no mínimo esse passo a passo:

1. Mapear as colunas que estarão presentes na tabela estruturada
2. Remover linhas sobrando
3. Remover colunas sobrando

As etapas 2 e 3 não necessariamente precisarão estar nessa ordem, beleza?!

Dica:
Sempre tenha em mente onde você quer chegar respondendo à pergunta: Quais colunas deverão estar presentes na tabela estruturada? Anote-as!

Passo 1: Mapeando colunas finais

Nesse nosso cenário precisamos obter as colunas Ano, Região, UF, Cidade, Mês e Valor (do faturamento).

Passo 2: Remover linhas sobrando

Escolha sempre uma coluna que você tenha certeza que, ao excluir os valores Null (nulos) dela, não estará removendo valores e outras informações importantes.

Nossa escolha será através da coluna Total (a última da nossa tabela), que será filtrada. Como nossos arquivos são anuais, sabemos que a coluna de Total sempre será a Column16 (posição 16), então se adicionarmos o arquivo de 2020 na pasta, ao atualizar, nossa transformação não trará problemas, ok?!

Importante:
Para remoção de valores Null (com filtro), ao escolhermos uma coluna para realizar o filtro, temos que ter certeza que essa coluna (e sua posição) não vai mudar quando adicionarmos um novo arquivo na pasta (exemplo Vendas 2020.xlsx), caso contrário provavelmente você terá problemas no ao atualizar a consulta.

No filtro dessa consulta clique no botão Remover Vazio.

Passo 3: Preencher linhas para baixo

A próxima etapa será realizar o preenchimento para baixo. Lembra que já tínhamos identificado células mescladas nas colunas Região e UF? Esse preenchimento de linhas para baixo nos ajudará a resolver isso!

Etapas: Selecione as duas colunas (Região e UF) ⇒ Clique com o botão direito em qualquer uma dessas duas colunas ⇒ Preenchimento ⇒ Para baixo

Veja que fazer isso no Power Query é mil vezes melhor que fazer no Excel. Lá, quando temos uma estrutura como essa, nós conseguimos preencher as células vazias com o conteúdo da célula preenchida dando duplo clique naquela alça no canto direito da célula e arrastando para baixo. Mas teríamos que fazer isso várias vezes, certo?! Aqui no Power Query, só fazemos isso uma vez! Legal, né?!

Estes passos poderiam ser feitos por algum script?

Maikel Cordeiro

Sim, Maikel. O Power Query tem uma linguagem de programação por trás de dele: a Linguagem M. Todos os passos aplicados nas transformações são traduzidos para a Linguagem M. É sempre legal acompanhar na barra de fórmulas cada etapa aplicada. Se essa barra não estiver aparecendo pra você, vá em Exibição ⇒ Layout ⇒ Marque Barra de Fórmulas. Para ver o Script do Power Query clique no botão do Editor Avançado, e assim conseguirá visualizar todas as etapas aplicadas em determinada consulta.

Para abrir o editor avançado dessa consulta, clique em Exibição e Editor Avançado.

Passo 3: Promover Cabeçalhos

Agora que removemos linhas Null e preenchemos as linhas de células mescladas, vamos promover cabeçalhos. Isso significa que vamos usar a primeira linha dos nossos dados como cabeçalho. Clique no canto esquerdo superior da tabela e selecione Usar a Primeira Linha como Cabeçalho. Veja:

Passo 4: Removendo linhas

Veja que ainda temos um problema: há linhas totalizadoras e cabeçalhos de outros arquivos:

Importante:
Temos que nos lembrar que não devemos levar linhas nem colunas totalizadoras para o relatório. Devemos sempre calcular esses valores através da criação de medidas usando a Linguagem DAX.

Precisamos remover essas linhas, certo?! Qual a melhor maneira de fazer isso? Removendo essas linhas? Nada disso! Vamos Filtrar a coluna Região (desmarque Total e Região).

Dica:
Utilize sempre filtros ao invés de remover linhas específicas. Lembre-se de que a base pode ser atualizada e você precisa ‘padronizar’ as etapas aplicadas, ou seja, se a linha com o Total não estiver sempre na posição 17, vamos ter problemas futuros. O filtro é a melhor maneira de remover essas linhas de forma padronizada / estruturada.

Passo 5: Remover e editar colunas

Por fim, vamos remover aquela coluna de Total (última coluna). Clique com o botão direito na coluna Total e depois em Remover. Sempre fique de olho em como o Power Query traduziu essa etapa para a Linguagem M. Nesse caso, a fórmula gerada pra essa etapa foi essa:

Fórmula: Table.RemoveColumns(#Linhas Filtradas2", ("Total"))

Veja que o nome “Total” foi utilizado nessa fórmula, então tenha certeza de tudo que foi utilizado na fórmula não vai mudar quando você adicionar novos arquivos na pasta, beleza?!

Agora, vamos mudar o nome da coluna que acabou se chamando Vendas 2018.xlsx porque promovemos cabeçalho anteriormente, lembra?! Basta dar duplo clique no cabeçalho e digitar Ano.

Depois disso, vamos editar essa coluna de Ano de forma com que apareça somente o valor do Ano (sem texto). E aí, alguma ideia de como fazer isso? Se você respondeu algo parecido com a função Extrair do Power Query, acertou!

Temos a opção Extrair em dois lugares: na guia Adicionar Coluna e Transformar. Tanto faz usar uma ou outra? Qual a diferença, Leo?

A opção localizada na guia Transformar vai realizar a mudança na própria coluna e a opção localizada na guia Adicionar coluna vai adicionar uma coluna com os caracteres extraídos da coluna que selecionarmos. Vamos então na guia Transformar (já que não precisamos dos caracteres extraídos):

Etapas: Transformar ⇒ Extrair ⇒ Texto entre delimitadores

Perceba que o trecho que queremos manter da coluna Ano são os valores 2017, 2018 e 2019. Eles estão sempre entre um espaço (após Vendas) e um ponto (antes de xlsx), certo?! Então basta digitar um espaço e um ponto na próxima janela (após clicar em Texto entre Delimitadores), veja:

Dica:
Sempre que ver esse botão Extrair, lembre-se que é equivalente a Manter. Ou seja, vamos utilizá-lo para manter os caracteres que queremos.

Após essa transformação nossa coluna deverá ter apenas o ano. Dessa forma, transforme-a para número inteiro. Clique no botão ABC 123 na coluna Ano e selecione a opção Número Inteiro.

Passo 6: Transformar Colunas em Linhas

Lembra do mapeamento de colunas que fizemos lá no início ? Utilizaremos essa dica valiosa agora para identificar se já possuímos todas as colunas que queremos. Anotamos essas: Ano, Região, UF, Cidade, Mês, Valor.
Veja que ainda faltam as colunas Mês e Valor, porque os meses ainda estão em formato de coluna. E para resolver isso, precisamos transformar essas colunas em linhas. Vamos seguir o passo a passo:

1º passo: Selecione somente as 12 colunas dos meses segurando o botão Shift do teclado;
2º passo: Clique com o botão direito do mouse em qualquer uma das colunas;
3º passo: Selecione Transformar Colunas em Linhas (Unpivot).

Note que foram criadas duas novas colunas: Atributo e Valor. Onde a coluna Atributo é o Mês. Vamos então renomear a coluna Atributo para Mês? Mas, isso adicionaria mais uma etapa, certo?! Para otimizar nosso processo de transformação vamos editar a etapa Colunas Não dinâmicas, beleza?!. Então, clique na etapa que acabamos de realizar e edite o nome Atributo na barra de fórmulas, veja:

Dica:
Economize etapas! Podemos realizar uma etapa dentro da outra otimizando ainda mais nossos processos de transformações. Sempre que possível, diminua o número de etapas aplicadas.

Por fim, basta clicar em Fechar e Aplicar.

Cenário 2

No cenário 2, precisamos estruturar uma tabela de forma que ela nos traga as informações de Data, Credor, Documento e Valor do Documento mas também nos mostre o Nível e o Subnível de cada uma das contas (que estão nas linhas 5 e 6 por exemplo, conforme figura abaixo).

Vamos utilizar o mesmo padrão do primeiro exercício. Vamos importar arquivos de uma pasta. Esses arquivos têm a mesma estrutura, o que muda é apenas o nome da cidade (no Cenário 1 era o Ano, certo?!).

Após importarmos os arquivos da pasta, mesmo esquema: escolha a opção Transformar Dados! Sempre! Após clicar em Transformar dados, veremos o seguinte:

Deixei um arquivo aberto na minha máquina de propósito para que fosse identificado o arquivo temporário (veja a linha 4 da figura acima). Para resolver isso, vamos filtrar a coluna Name para manter apenas linhas que não começam com “~”.

Etapas: Clique no filtro da coluna Name ⇒ Filtros de Texto ⇒ Não começa com ... ⇒ ~

Passo a Passo resumido

Agora, aplicaremos as seguintes etapas (vou descrever brevemente porque já fizemos isso no Cenário 1, ok?!) :

• Na guia Adicionar Coluna clique em Coluna Personalizada para darmos início a nossa nova etapa;
• Renomeie Nova Coluna para Conteúdo;
• Digite a Fórmula: Excel.Worbook([Content]);
• Na guia Página Inicial clique em Escolher colunas e deixe selecionado apenas Name e Conteúdo;
• Clique em no botão de expandir da coluna Conteúdo (lembre-se de deixar selecionado apenas Data);
• Clique em no botão de expandir da coluna Data para obter todas as colunas dos nossos dados;
• Vamos manter as colunas Nome, Coluna1, Coluna2, Coluna4, Coluna11, Coluna13. Para isso, selecione com a tecla CTRL todas elas e depois clique em uma delas com o botão direito. Por fim, selecione Remover Outras Colunas;
• Vamos criar uma coluna com o nome do Nível via teste lógico. Na guia Adicionar Coluna selecione a opção Coluna Condicional;
• Mude o nome da coluna criada para Nível e use a lógica seguinte (no fim, clique em Ok):

Note que onde aparecer erro é devido a condição não ter transformado o Null da coluna em nenhum argumento. Para remover esses erros clique com o botão direito do mouse em cima da coluna Nível criada e depois em Substituir Erros: insira Null e clique em Ok.

• Vamos criar uma coluna para o Subnível. Vamos fazer semelhante ao que fizemos com Nível. Na guia Adicionar Coluna selecione a opção Coluna Condicional. Mude o nome da coluna criada para Subnível e use a lógica seguinte (no fim, clique em Ok):

• Lembre-se de remover erros da mesma forma que foi feita na coluna Nível, beleza?
• Selecione as duas colunas criadas (Nível e Subnível) e clique com o botão direito do mouse em cima de uma delas e depois em Preencher ⇒ Para baixo
• Selecione a coluna 4 e através do filtro clique no botão Remover Vazio;
• Subir o cabeçalho: clique no ícone de tabela localizado no canto esquerdo superior da tabela e selecione Usar a primeira Linha como Cabeçalho
• Na coluna Credor através do filtro remova os cabeçalhos dos outros arquivos importados. Para isso, basta desmarcar a linha com Credor. Veja:

• Remover a coluna CTP (clique na coluna e selecione a opção de remover);
• Mude os tipos de coluna para o formato correto: texto, número decimal, etc e renomeie as colunas conforme necessário.

Agora, basta clicar em Fechar e Aplicar para finalizar! Finalizamos o ETL!

Vamos listar algumas perguntas interessantes que foram realizadas durante a Live #10 (que foi base para esse conteúdo).

Perguntas e Respostas

Se depois deposito um novo arquivo na pasta, será necessário fazer o mesmo processo?

Macarena Moraga

Pergunta importante! Se o novo arquivo adicionado mantiver a mesma estrutura (mesmo nome de coluna, mesmo padrão de nome de arquivo, etc) dos demais que já estavam adicionados quando as transformações foram aplicadas, as mesmas transformações também serão aplicadas automaticamente para o novo arquivo adicionado.

Dica:
Não manipule os dados no arquivos Excel quando esses forem extraídos do sistema (ou seja, edite nada na estrutura dos seus dados). Apenas coloque-o na pasta para aproveitar as transformações realizadas no Power Query. Assim, você garante que irá manter a mesma estrutura, automatizando seu trabalho.

Um arquivo com diversas abas seria a mesma lógica?

Luciel Bonella

Sim, exatamente a mesma coisa!

Coluna Exemplo também funciona?

Renato Marinho

Vamos fazer um teste! Nosso exercício será realizar uma nova coluna apenas com o a primeira letra do Primeiro Nome e Sobrenome. Nossos dados são esses:

Agora, utilizaremos a Coluna de Exemplos encontrada na guia Adicionar Coluna.

Note que aparecerá essa Coluna1 para você digitar o resultado que você quer obter com essa funcionalidade:

Deixe selecionada apenas a primeira coluna para esta ser a única a ser utilizada como Exemplo. Agora, vamos digitar na primeira linha da Coluna 1: L. Karpinski .

Veja que ao fazer isso não foi identificado um padrão (as demais linhas não foram preenchidas automaticamente). Então vamos digitar também na segunda linha F. Souza. Agora o Power Bi conseguiu identificar um padrão e sugeriu o resultado para as demais linhas. Se tiver tudo certinho, clique em Ok.

Esse foi nosso conteúdo de hoje, pessoal! Espero que tenham gostado e até a próxima!

Grande abraço,
Leonardo.