Como usar o Power Query no Excel?
O Excel Power Query é usado para pesquisar fontes de dados, fazer conexões com fontes de dados e, em seguida, moldar os dados de acordo com nossos requisitos de análise. Depois de moldar os dados de acordo com nossas necessidades, também podemos compartilhar nossas descobertas e criar vários relatórios usando mais consultas.
Passos
Basicamente, existem 4 etapas, e a ordem dessas 4 etapas no Power Query é a seguinte:
- Conectar: primeiro nos conectamos aos dados, que podem estar em algum lugar, na nuvem, em serviço ou localmente.
- Transformar: a segunda etapa seria alterar a forma dos dados de acordo com a necessidade do usuário.
- Combinar: nesta etapa, realizamos algumas etapas de transformação e agregação e combinamos dados de ambas as fontes para produzir um relatório combinado.
- Gerenciar: mescla e acrescenta colunas em uma consulta com colunas correspondentes em outras consultas na pasta de trabalho.
Existem muitos recursos superpoderosos do Excel Power Query.
Suponha que temos dados de compra dos últimos 15 anos em 180 arquivos. Agora, a gestão de uma organização exigiria consolidar os números antes de analisá-los. A gestão pode seguir qualquer um dos seguintes métodos:
- Eles abririam todos os arquivos e os copiariam e colariam em um arquivo.
- Por outro lado, eles podem usar uma solução sábia, que é aplicar fórmulas, pois está sujeito a erros.
Seja qual for o método escolhido, ele contém muito trabalho manual e, após alguns meses, haveria novos dados de vendas para o período adicional. Eles terão que fazer o mesmo exercício novamente.
No entanto, o Power Query pode ajudá-los a não fazer esse trabalho tedioso e repetitivo. Vamos entender esta consulta de potência do Excel com um exemplo
Exemplo
Suponha que temos arquivos de texto em uma pasta com dados de vendas e queremos obter esses dados em nosso arquivo Excel.
Como podemos ver na imagem abaixo, temos dois tipos de arquivos na pasta, mas queremos obter os dados de apenas arquivos texto no arquivo excel.
Para fazer o mesmo, as etapas seriam:
Etapa 1: primeiro, precisamos obter os dados no Power Query para que possamos fazer as alterações necessárias nos dados para importá-los para um arquivo Excel.
Para fazer o mesmo, escolheremos a opção “From Folder” do menu “From File” após clicar no comando “Get Data” do grupo “Get & Transform” na aba “Data” .
Etapa 2: Selecione o local da pasta navegando.
Clique em 'OK'
Etapa 3: Uma caixa de diálogo será aberta contendo a lista de todos os arquivos na pasta selecionada com os cabeçalhos das colunas como 'Conteúdo,' 'Nome,' 'Extensão', 'Data de acesso,' 'Data de modificação,' 'Data de criação,' 'Atributos' e 'Caminho da pasta'.
Existem 3 opções, ou seja, Combine , Load e Transform Data .
- Combinar : esta opção é usada para ir para uma tela onde podemos escolher quais dados combinar. A etapa de edição é ignorada para esta opção e não nos dá controle sobre quais arquivos combinar. A função Combine leva todos os arquivos da pasta para consolidar, o que pode levar a erros.
- Carregar: Esta opção irá apenas carregar a tabela conforme exibida acima na imagem na planilha do Excel ao invés dos dados reais nos arquivos.
- Transformar dados: Ao contrário do comando 'Combinar' , se usarmos este comando, podemos escolher quais arquivos combinar, ou seja, podemos combinar apenas um tipo de arquivo (mesma extensão).
Como em nosso caso, queremos combinar apenas arquivos de texto (.txt); vamos escolher o comando “Transform Data” .
Podemos ver “Passos aplicados” no lado direito da janela. Por enquanto, há apenas uma única etapa feita que é retirar os detalhes dos arquivos da pasta.
Passo 4: Existe uma coluna chamada 'Extensão' onde podemos ver que os valores da coluna são escritos em ambos os casos, ou seja, maiúsculas e minúsculas.
No entanto, precisamos converter todos os valores para minúsculas, pois o filtro diferencia os dois. Para fazer o mesmo, precisamos selecionar a coluna e, em seguida, escolher “minúscula” do “Format” menu de comando do.
Etapa 5: filtraremos os dados usando a coluna 'Extensão' para arquivos de texto.
Etapa 6: precisamos combinar dados para os dois arquivos de texto agora usando a primeira coluna 'Conteúdo'. Vamos clicar no ícone colocado à direita do nome da coluna.
Etapa 7: Uma caixa de diálogo com a legenda 'Combine Files' será aberta, onde precisamos selecionar o delimitador como 'Tab' para arquivos de texto (arquivos com extensão '.txt') e podemos escolher a base para detecção do tipo de dados. E clique em 'OK'.
Após clicar em 'OK' , obteremos os dados combinados dos arquivos de texto na janela 'Power Query' .
Podemos alterar o tipo de dados das colunas conforme necessário. Para a coluna 'Receita' , alteraremos o tipo de dados para 'Moeda'.
Podemos ver as etapas aplicadas aos dados usando uma consulta avançada no lado direito da janela.
Depois de fazer todas as alterações necessárias nos dados, podemos carregar os dados em uma planilha do Excel usando o comando 'Fechar e carregar para' no grupo 'Fechar' na guia 'Página inicial' .
Precisamos escolher se queremos carregar os dados como uma tabela ou conexão. Em seguida, clique em 'OK'.
Agora podemos ver os dados como uma tabela na planilha.
E o painel 'Consultas da pasta de trabalho' no lado direito, que podemos usar para editar, duplicar, mesclar, anexar as consultas e para muitos outros fins.
O Excel Power Query é muito útil, pois podemos ver que 601.612 linhas foram carregadas em poucos minutos.
Coisas para lembrar
- O Power Query não altera os dados de origem originais. Em vez de alterar os dados da fonte original, ele registra cada etapa realizada pelo usuário ao conectar ou transformar os dados e, uma vez que o usuário conclui a modelagem dos dados, ele pega o conjunto de dados refinado e o traz para a pasta de trabalho.
- O Power Query diferencia maiúsculas de minúsculas.
- Ao consolidar os arquivos na pasta especificada, precisamos nos certificar de que usar a coluna 'Extensão' e devemos excluir os arquivos temporários (com a extensão '.tmp' e o nome desses arquivos começa com o sinal '~') como O Power Query também pode importar esses arquivos.
