Blog

Postado em em 18 de agosto de 2020

Curso de Power Query Básico

Nesse post vamos fazer um curso de Power Query Básico em apenas uma aula! Vou te mostrar como sair do zero nessa ferramenta!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é e quando utilizar o Power Query?

O Power Query é uma tecnologia de conexão de dados que permite que você descubra, conecte, combine e refine fontes de dados para atender às suas necessidades de análise. Os recursos do Power Query estão disponíveis no Excel e no Power BI desktop.

Dentro do ambiente Excel a maior utilização do Power Query é para pesquisar fonte de dados e fazer conexões com outros arquivos para que o usuário consiga fazer criar seus relatórios e análises.

Outro uso para o Power Query é para edição de dados, ou seja, o usuário consegue fazer algumas alterações nas tabelas antes de juntá-las e criar suas análises.

Fonte: https://support.microsoft.com/pt-br/office/power-query-vis%C3%A3o-geral-e-aprendizagem-ed614c81-4b00-4291-bd3a-55d80767f81d

Curso de Power Query Básico

Neste post vamos ensinar os primeiros passos no Power Query para as pessoas que ainda não saibam para que serve e como utilizar essa ferramenta. Então teremos uma introdução ao Power Query para que os alunos possam sair do zero com a ferramenta e ainda começar a utilizá-la na rotina de trabalho.

Tabela com dados de 2018 - Curso de Power Query Básico

Tabela com dados de 2018

Dentro desse primeiro arquivo nós temos 3 abas, a primeira deles contém as informações de venda do ano de 2018, a segunda contém as informações de venda de 2019 e a última contém as informações dos produtos.

Tabela de produtos

Tabela de produtos

Acontece que algumas vezes precisamos juntar informações para fazer uma análise mais detalhada, neste caso temos em duas abas diferente as informações de 2018 e 2019.

Uma opção que o usuário teria para fazer essa junção de dados seria simplesmente copiar e colar as informações para que todas fiquem juntas, no entanto em certos casos isso acaba não sendo viável principalmente quando temos vários arquivos com informações que precisamos juntar.

Então ficaria mais trabalhoso ter que abrir arquivo por arquivo, copiar e colar essas informações para que só depois o usuário consiga de fato tratar as informações para poder criar seus dashboards e análises.

Sabendo as informações que temos dentro desse arquivo vamos abrir um novo arquivo em branco para que possamos juntar essas informações. Após abrir o arquivo em branco vamos até a guia Dados, selecionar a opção Obter Dados, em seguida em De Arquivo e por fim selecionar a opção Da Pasta de Trabalho.

OBS: Para as versões do Excel 2010 e 2013 o usuário terá que fazer o download do Power Query. Nas versões mais recentes não será necessário o download. O download do Power Query para essas versões pode ser feito no link abaixo:

https://www.microsoft.com/pt-BR/download/details.aspx?id=39379

Opção para obter dados - Curso de Power Query Básico

Opção para obter dados

OBS: Veja que o Power Query não permite com que o usuário obtenha apenas arquivos de Excel, existem outras possibilidades para obtenção desses dados, basta verificar se a opção desejada tem suporte.

Feito isso o Excel irá abrir uma janela para que o usuário possa escolher onde está o seu arquivo, basta escolher o arquivo Vendas 2018-2019.xlsx e clicar na opção Importar.

Pré-visualização de dados

Pré-visualização de dados

Depois de importar o programa irá abrir uma guia Navegador para que o usuário possa selecionar dentro do arquivo as abas que deseja fazer alguma alteração. Veja que ao selecionar uma das abas o programa já mostra uma pré-visualização dos dados dessa aba, no entanto é possível verificar que por conta das formatações os dados acabam não ficando com o formato correto, mas as informações permanecem as mesmas.

IMPORTANTE: Esses problemas de formação, como linhas adicionais em cima dos cabeçalhos e colunas com informações vazias poderão ser tratados dentro do ambiente Power Query, pois como foi informado no início essa ferramenta também serve para edição de dados, então será possível tratar esses problemas.

Como vamos selecionar mais de 1 aba para fazer essas edições, será necessário marcar a opção Selecionar vários itens e marcar cada uma das abas que serão importadas.

Selecionando os dados para importar - Curso de Power Query Básico

Selecionando os dados para importar

Feito isso basta clicar na opção Transformar Dados, pois dessa forma vamos levar essas informações para o ambiente do Power Query.

Ambiente Power Query

Ambiente Power Query

Esse é o ambiente do Power Query onde o usuário poderá editar e fazer algumas alterações nos dados que tem, assim como juntar essas informações caso seja necessário. Vamos passar todo o nosso curso de Power Query Básico nesse ambiente!

É possível observar que no canto esquerdo temos as Consultas, que é o nome dado as tabelas que foram importadas.

Ao centro temos as informações de cada um dessas consultas. Para alterar basta selecionar a consulta desejada.

A direita temos as configurações das consultas, onde é possível alterar o nome de cada consulta e é possível verificar a parte de Etapas Aplicadas, ou seja, o Power Query vai mostrar todas as ações que foram executadas dentro de cada consulta, desta forma o usuário vai saber o que foi feito para que o dados ficassem dessa forma e pode volta em cada etapa clicando nelas ou até mesmo excluir clicando no x.

Por fim na parte superior temos as ferramentas que serão utilizadas para tratar esses dados para que fiquem organizados para que possamos importar ao Excel as informações corretas tratando qualquer problema que tenha sido encontrado até o momento.

Selecionando a coluna vazia - Curso de Power Query Básico

Selecionando a coluna vazia

Começando pelas informações do ano de 2019 é possível notar que para organizarmos as informações precisamos remover a primeira coluna, pois ela contém um título que não será útil no momento e o restante das informações são vazias.

Para isso podemos selecionar essa coluna (clicando no cabeçalho), em seguida ir até a guia Página Inicial e por fim selecionar a opção Remover Colunas.

Opção para remover colunas

Opção para remover colunas

Feito isso a coluna será removida e podemos partir para a remoção de algumas linhas que estão atrapalhando. É possível notar que as 3 primeiras linhas possuem informações vazias, portanto vamos repetir o procedimento, mas desta vez utilizando a opção de Remover Linhas Principais dentro da opção de Remover Linhas.

Opção para remover linhas

Opção para remover linhas

Ao selecionar essa opção o programa pergunta quantas linhas a partir da primeira vamos querer remover. Como as 3 primeiras linhas estão com informações vazias vamos inserir o número 3 para removê-las.

Inserindo a quantidade de linhas a serem removidas - Curso de Power Query Básico

Inserindo a quantidade de linhas a serem removidas

Feito isso essa consulta já está quase pronta, o único detalhe agora é que os nomes das colunas estão na primeira linha da nossa tabela e não de fato onde ficam os cabeçalhos.

Cabeçalhos na primeira linha

Cabeçalhos na primeira linha

É possível observar que as colunas têm o nome de “coluna” e não é isso que queremos, portanto vamos até a guia Página Inicial e selecionar a opção Usar a Primeira Linha como Cabeçalho.

O nome da função é bem intuitivo então de fato ela vai pegar a primeira linha da tabela e vai transformá-la em cabeçalho para que possamos identificar corretamente as informações de cada uma das colunas.

Utilizando a primeira linha como cabeçalhos - Curso de Power Query Básico

Utilizando a primeira linha como cabeçalhos

Feito isso essa consulta já está pronta, ou seja, temos somente as informações necessárias, sem as linhas e colunas vazias.

Inserindo os cabeçalhos

Inserindo os cabeçalhos

Como o Power Query também nos permite editar essas consultas podemos fazer algumas alterações para facilitar a análise dos dados futuramente. Veja que na primeira coluna nós temos o código de cada produto em conjunto com o nome de cada loja.

As informações dessa maneira podem dificultar uma análise caso o usuário queira verificar um produto ou uma loja em específico, portanto, podemos utilizar a opção de Dividir Coluna para separar elas de acordo com um delimitador.

Dividindo colunas por delimitador

Dividindo colunas por delimitador

Como temos um padrão do traço separando essas duas informações podemos utilizar essa opção Por Delimitador para que o programa consiga facilmente dividir essa coluna.

Inserindo o delimitador - Curso de Power Query Básico

Inserindo o delimitador

Veja que o usuário tem diversas opções para essa separação, pode escolher um delimitador já na lista, caso não tenha pode escrever qual é esse delimitador. Pode escolher por onde essa divisão irá ocorrer caso tenha mais de um delimitador dentro de cada informação.

Nas opções avançadas o usuário ainda consegue informar que ser dividir as informações em linhas ou colunas e até mesmo informar a quantidade de colunas que as informações serão divididas.

Informações divididas

Informações divididas

É possível observar que as informações foram divididas corretamente conforme o delimitador, no entanto temos que alterar os nomes dos cabeçalhos para que seja mais fácil identificar cada informação. Para renomear os cabeçalhos basta dar um duplo clique em cada informação e inserir o novo nome.

Alterando o nome da coluna - Curso de Power Query Básico

Alterando o nome da coluna

Para finalizar conseguimos notar que as informações de dia, mês e ano estão em colunas separadas, no entanto, seria mais viável ter essas informações em apenas uma coluna, ou seja, podemos transformar as 3 colunas em uma única coluna.

Para isso vamos selecionar as três colunas utilizando a tecla CTRL, para que todas fiquem selecionadas. Em seguida vamos até a guia Transformar e por fim na opção Mesclar Colunas.

Opção para mesclar colunas

Opção para mesclar colunas

Ao selecionar essa opção o programa irá abrir uma janela para que o usuário possa selecionar qual separador será utilizado entre essas informações e o nome da nova coluna. Lembrando que caso o usuário não encontre o separador é possível selecionar a opção Personalizado e escrever manualmente.

Utilizando a barra para juntar a data

Utilizando a barra para juntar a data

Feito isso temos uma coluna com essas informações mescladas, no entanto é possível observar que quando fazemos uma operação desse tipo para juntar informações o Power Query acaba considerando essas informações como texto, símbolo de ABC ao lado esquerdo do cabeçalho.

Como uma coluna pode ter apenas um único tipo é importante que o usuário formate essas informações de forma correta para que o programa consiga saber corretamente o tipo daquela informação e trate ela corretamente.

Então para mudar o tipo dos dados da coluna basta clicar no símbolo ao lado do nome da coluna e escolher a opção desejada. Neste caso vamos selecionar o tipo de data.

Alterando o formato para data - Curso de Power Query Básico

Alterando o formato para data

É possível verificar que o símbolo passa a ser um calendário e até o alinhamento das informações muda, pois os textos são alinhados a esquerda, enquanto números e datas são alinhados a direita da coluna.

Verificando o formato da coluna

Verificando o formato da coluna

Com isso finalizamos a edição desses dados nessa parte do nosso curso de Power Query Básico. Algo muito interessante do Power Query é que como as ações que o usuário faz ficam gravadas ele acaba que não precisa refazer essas ações quando atualiza sua base de dados.

Isso quer dizer que se o usuário inserir mais informações na sua tabela o Power Query vai seguir a lista de ações para os novos dados que foram inseridos.

Etapas aplicadas

Etapas aplicadas

Então todas essas etapas serão aplicadas automaticamente aos novos dados, portanto para atualizar com as novas informações depois de acrescentar no arquivo de Excel basta ir até a guia Página Inicial e selecionar a opção Atualizar Visualização.

Opção para atualizar as informações

Opção para atualizar as informações

Feito isso os dados serão atualizados e as mesmas ações que foram feitas serão replicadas para os novos dados, desta forma o usuário não irá precisar repetir os procedimentos sempre que atualizar sua base de dados.

Agora vamos repetir o mesmo procedimento para a base de dados de 2018 para que possamos em seguida juntar essas duas informações em uma única tabela. Feito isso podemos ajustar também a base de Produtos para que fique correta sem informações vazias dentro da tabela.

Com essas consultas ajustadas podemos voltar para a consulta de 2019 por exemplo, ir até a guia Página Inicial e selecionar a opção Acrescentar Consultas.

Opção para acrescentar consultas

Opção para acrescentar consultas

Veja que neste caso temos duas opções, a primeira opção vai acrescentar uma outra consulta já dentro dessa, ou seja, vamos adicionar outra consulta dentro da que estamos visualizando, enquanto a segunda opção nos permite criar uma nova consulta com a junção dessas duas, assim o usuário não “perde” informações.

Ao selecionar a segunda opção, para adicionar uma nova consulta com a junção o programa irá abrir uma janela para solicitar ao usuário quais consultas serão adicionadas.

Acrescentando consultas

Acrescentando consultas

Como vamos adicionar somente 2 tabelas podemos deixar a primeira opção marcada, caso o usuário tenha 3 ou mais tabelas pode marcar a segunda opção e selecionar todas as tabelas desejadas para juntá-las.

Ao pressionar OK teremos a nova consulta criada com a junção das duas consultas com as informações de 2018 e 2019. É importante ressaltar que as colunas devem ter as mesmas informações e que elas possuam a mesma ordem para que o programa consiga fazer a junção correta dessas informações. Devem possuir também o mesmo nome para que o programa não encontre problemas ao juntar as informações.

Nova consulta criada

Nova consulta criada

Para alterar o nome da consulta basta dar um clique duplo e alterar para o nome desejado.

Desta forma já temos as informações dos anos de 2018 e 2019 em uma única tabela, no entanto não temos as informações de produto dentro dessa tabela, temos uma consulta somente com essas informações.

Então para trazer essas informações do produto para dentro dessa consulta compilada podemos fazer uma espécie de PROCV dentro do Power Query para trazer essas informações.

Ainda com essa consulta de 2018 e 2019 selecionada vamos até a guia Página Inicial e vamos selecionar a opção Mesclar Consultas.

Mesclando consultas (tipo PROCV)

Mesclando consultas (tipo PROCV)

Dentro dessa nova janela é possível notar que a parte de cima já estará a mostra, pois é a consulta em que iniciamos o processo. Em seguida teremos que selecionar a consulta em que vamos obter as informações e por fim temos que selecionar as colunas que possuem as informações em comum entre essas duas tabelas.

Selecionando as informações desejadas

Selecionando as informações desejadas

Feito isso teremos uma coluna sendo adicionada a essa tabela, mas que possui um símbolo de duas setas ao lado direito do cabeçalho. Clicando nessa opção teremos a possibilidade de escolher quais informações vamos trazer dessa tabela de produtos.

Então o usuário poderá escolher entre as colunas disponíveis quais informações vai querer trazer, neste caso vamos trazer todas as informações tirando o SKU, pois essa informação já temos na tabela.

Informações inseridas na tabela

Informações inseridas na tabela

O usuário também pode desmarcar a opção de Use o nome da coluna original como prefixo, assim só terá os nomes das colunas e não o nome da consulta + o nome da coluna.

Para finalizar e importar as informações diretamente para dentro do Excel basta ir até a opção Fechar e Carregar.

Opção para importar as consultas ao Excel

Opção para importar as consultas ao Excel

Feito isso as informações serão passadas para o ambiente Excel e o Power Query será fechado.

Tabelas dentro do Excel

Tabelas dentro do Excel

Agora com as informações dentro do ambiente Excel o usuário poderá dar continuidade a construção dos seus relatórios e dashboards.

Bônus – Obtendo informações de uma pasta inteira

Nesta parte do nosso curso de Power Query básico, vamos aprender a como importar as informações de uma pasta, ou seja, muita das vezes os usuários têm informação de anos em planilhas separadas e precisam juntar essas informações que estão em uma determinada pasta.

Vamos seguir o mesmo procedimento para obtenção de dados, mas ao invés de escolher a opção Da Pasta de Trabalho vamos selecionar a opção Da Pasta.

Obtendo dados de uma pasta

Obtendo dados de uma pasta

Ao clicar nessa opção o programa irá solicitar o caminho da pasta para que os arquivos possam ser importados todos de uma só vez.

Combinando e transformando os dados

Combinando e transformando os dados

Com a pasta selecionada os arquivos serão todos mostrados, então o usuário poderá escolher qual ação será tomada. Neste caso vamos escolher a opção de Combinar e Transformar Dados, ou seja, vamos juntar essas informações, mas vamos para dentro do ambiente Power Query para poder editar esses dados.

Pré-visualização dos dados

Pré-visualização dos dados

Teremos novamente a pré-visualização dos dados, neste caso é possível observar que as informações já não possuem aquelas linhas e colunas vazias. Por fim o usuário poderá pressionar OK para ir até o ambiente de edição.

Verificando as informações de todos os anos - Curso de Power Query Básico

Verificando as informações de todos os anos

Dentro do Power Query teremos todos os anos, ou seja, todos os arquivos em conjunto. Aqui poderão ser feitas as edições necessárias e por fim será possível Fechar e Carregar para enviar essas informações para dentro do Excel.

Nesta aula, um pouco longa, foi possível aprender como usar o Power Query no Excel, então quem não sabia nada agora, com esse curso de Power Query básico, já tem uma boa noção de como funciona a ferramenta e de como pode utilizá-la para facilitar algumas ações com dados do Excel.

É uma ferramenta muito interessante até porque permite a edição das informações a serem importadas e permite uma atualização mais dinâmica do que o usuário teria se estivesse utilizando somente o Excel ou até mesmo o VBA.

Espero que esse curso de Power Query básico tenha te ajudado a sair do zero nessa ferramenta!

Hashtag Treinamentos

Para acessar outras publicações sobre Excel, clique aqui!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?