Consulta Avançada do Excel (Sumário)
- Introdução ao Power Query no Excel
- Como usar o Power Query no Excel?
Introdução ao Power Query no Excel
Antes de começar a analisar os dados, a etapa envolvida é importar os mesmos dados. No Excel, temos uma maneira generalizada de inserir dados manualmente célula por célula, linha por linha e coluna por coluna. No entanto, fica difícil quando você tem que inserir grandes linhas de dados. Considere um conjunto de dados com 100 mil linhas. Será um trabalho demorado, certo? Mesmo não vale a pena inserir manualmente muitas das linhas no Excel, pois elas são propensas a erros. O que fazer nesses casos? Existe alguma alternativa para entrada manual? Certamente, existe uma alternativa para o mesmo. Consulta de energia! Boa palavra para ouvir? É ainda melhor se você aprender como usá-lo no Excel para importar e transformar os dados no Excel. Neste artigo, discutiremos o Power Query no Excel.
Consulta de energia para automatizar o processo de importação de dados
O Power Query é uma poderosa ferramenta de excel que permite ao usuário importar os dados de várias fontes para o Excel. Essas fontes incluem alguns bancos de dados relacionais, como Microsoft SQL Server, Microsoft Access, Azure, etc., além de arquivos de dados como Excel, Texto, CSV, XML, JSON etc. O Power Query pode ajudá-lo a automatizar a tarefa de importação de dados no Excel com esforços mínimos e é muito mais fácil que você possa navegar pelo próprio Excel. Idealmente, pode ser considerada uma ferramenta ETL que permite extrair, transformar e carregar os dados no Excel.
Como usar o Power Query no Excel?
Suponha que tenhamos um arquivo de texto em uma pasta que consiste em 1000 linhas de dados. Usaremos o Power Query para importar esse arquivo no Excel e tentar manipular o mesmo usando o próprio Power Query.
Etapa 1: Primeira coisa primeiro. Precisamos criar uma conexão para poder extrair os dados de um arquivo de texto no Excel. Abra um arquivo do Excel> clique na guia Dados na faixa de opções do Excel> clique no menu suspenso Obter Dados na seção Obter e Transformar Dados .
Etapa 2: assim que você clica no menu suspenso Obter dados, você obtém várias opções de onde você pode realmente extrair os dados. Navegaremos em direção à opção denominada Da pasta, que está no menu suspenso Do arquivo . Esta opção está sendo usada sobre a opção From Text / CSV, porque possui mais versatilidade do que a última.
Etapa 3: Assim que você clicar na opção Da Pasta, uma nova janela será exibida. Lá, você deve procurar o caminho no qual o arquivo está localizado. Clique no botão Procurar … e navegue em direção ao caminho onde o arquivo de dados está localizado.
Clique em OK quando terminar de navegar no caminho.
NOTA: O caminho mencionado aqui na captura de tela é o local em que localizei o arquivo. No momento em que você segue este tutorial, seu arquivo de dados pode estar armazenado em outro lugar. Procure esse caminho.Etapa 4: Uma nova janela será aberta com uma lista de todos os arquivos presentes no caminho pesquisado com atributos de arquivo diferentes, como nome do arquivo, tipo de dados, data de modificação e caminho em que o arquivo está localizado, extensão de arquivo etc.
Existem mais 4 opções no lado inferior desta janela que lê Combinar, Carregar, Transformar dados e Cancelar, respectivamente.
Combinar permite escolher entre os conjuntos de dados que você deseja combinar. No entanto, ele não possui uma opção de edição separada, o que a torna menos versátil, pois você não pode decidir quais colunas serão combinadas.
Carregar permite carregar os dados como uma tabela / Pivot em uma planilha do Excel, independentemente do formato real dos dados no arquivo de origem.
Transformar dados permite transformar os arquivos de dados de origem. Você pode adicionar a coluna calculada, alterar o formato de determinadas colunas, adicionar ou remover colunas, agrupar colunas etc.
Cancelar é um botão que cancela todas as outras operações na consulta de energia.
Etapa 5: clique no botão Transform Data e selecione o arquivo chamado Source Data.txt, você pode ver o layout dos dados, como mostrado abaixo:
Na faixa de opções superior, existem várias opções, como Página inicial, Transformar, Adicionar coluna, Exibir. No lado esquerdo do layout, há uma janela para a Configuração da consulta, podemos ver todas as consultas executadas até aqui, uma a uma. Observe que cada consulta é um código formulado no Power Query.
Etapa 6: Agora, alteramos o tipo de dados da primeira coluna como Data. Selecione a primeira coluna denominada Data > Clique com o botão direito do mouse> barra de navegação Alterar tipo > Selecionar Data como uma opção para representar todos os valores numéricos como datas para a coluna especificada.
Etapa 7: Assim que você clicar em Data, uma nova janela será exibida, chamada Alterar Tipo de Coluna . Clique em Adicionar nova etapa para alterar o formato da coluna como Data.
Dê uma olhada na coluna Data agora, ela deveria ter alterado o formato de números inteiros para Datas.
Etapa 8: também podemos adicionar uma coluna calculada / calculada nesse layout antes de carregar o mesmo. Siga a navegação como Adicionar coluna> Coluna personalizada na seção Geral . Isso permitirá que você crie uma nova coluna calculada com base na fórmula personalizada.
Etapa 9: altere o nome da coluna como % da margem e formule-o como Margem / Vendas em Coluna personalizada, como mostrado abaixo:
Clique no botão OK e você pode ver a coluna sendo adicionada no layout.
Etapa 10: Para carregar esses dados no Excel, navegue até Início> Fechar e carregar barra de navegação > opção Fechar e carregar em … (Essa opção permite carregar os dados como Tabela, Tabela dinâmica, Tabela dinâmica, etc.)
Etapa 11: No assistente Importar Dados, selecione o layout da Tabela, Planilha Existente e clique em OK .
Você pode ver uma tabela de dados carregada como mostrado abaixo:
É assim que podemos usar o Power Query para automatizar a tarefa de importação de dados no Excel. Vamos embrulhar as coisas com alguns pontos a serem lembrados.
Coisas para lembrar
- O Power Query é a maneira mais fácil de automatizar a tarefa de importação de dados no Excel. Economiza mão de obra, tempo e reduz o erro humano.
- Podemos importar dados de diferentes bancos de dados, como SQL Server, servidor Access, etc., bem como arquivos como texto, CSV, XML, etc.
Artigos recomendados
Este é um guia para o Power Query no Excel. Aqui discutimos como usar o Power Query no Excel e como importar dados de diferentes bancos de dados com exemplos. Você também pode consultar nossos outros artigos sugeridos -
- Como usar o Hacks do Excel com teclas de atalho
- Como criar um modelo de dados no Excel?
- Exemplos de lista de verificação no Excel
- Gráfico de caixa e bigode no Excel