Power Query: Importe e Transforme Dados como um Profissional

Se você passa horas toda semana baixando arquivos CSV, excluindo linhas em branco, formatando datas e escrevendo fórmulas aninhadas complexas apenas para preparar seus dados para análise, você está trabalhando mais do que o necessário. Bem-vindo ao Power Query—a ferramenta de automação de dados mais poderosa incorporada diretamente no Microsoft Excel.

Frequentemente chamado de "Obter e Transformar Dados", o Power Query permite que você se conecte a quase qualquer fonte de dados, limpe e remodele as informações e as carregue em sua planilha. O melhor de tudo? Ele grava suas etapas. Na próxima vez que você receber novos dados, não precisará repetir o trabalho manual; basta clicar em Atualizar.

Neste guia completo, exploraremos o que é o Power Query, como navegar por sua interface e faremos um passo a passo de um exemplo prático de transformação de um conjunto de dados desorganizado em informações limpas e prontas para análise.

O que é o Power Query?

O Power Query é um mecanismo de conexão e preparação de dados. No mundo do gerenciamento de banco de dados, esse processo é conhecido como ETL: Extract, Transform, and Load (Extrair, Transformar e Carregar).

• Extrair: Extraia dados de várias fontes (pastas de trabalho do Excel, arquivos CSV e de texto, páginas da web, bancos de dados SQL ou até mesmo serviços em nuvem).
• Transformar: Limpe e remodele os dados. Isso inclui filtrar linhas, alterar tipos de dados, dividir texto, mesclar tabelas e criar colunas calculadas.
• Carregar: Envie os dados aprimorados diretamente para uma Tabela do Excel, uma Tabela Dinâmica ou para o Modelo de Dados do Power Pivot.

Tradicionalmente, os usuários do Excel contavam com uma combinação de funções como TRIM, PROPER, SUBSTITUTE e VLOOKUP combinadas com o processo de copiar e colar manualmente para lidar com essas tarefas. O Power Query substitui esse fluxo de trabalho tedioso por uma interface visual e amigável.

Por que você deve aprender o Power Query

Se você ainda está em dúvida sobre aprender uma nova ferramenta do Excel, veja por que dominar o Power Query é um divisor de águas para sua produtividade:

• Nenhuma codificação necessária: Embora o Power Query seja executado em uma linguagem de fórmula chamada "M", você pode realizar 95% das transformações de dados usando apenas os botões da Faixa de Opções e menus de clique com o botão direito.
• Automação sem VBA: Você não precisa saber como escrever macros para automatizar tarefas repetitivas. O Power Query lembra de suas "Etapas Aplicadas" e as aplica aos novos dados automaticamente.
• Lida com Big Data: O Excel tem um limite físico de 1.048.576 linhas. O Power Query pode se conectar a conjuntos de dados com milhões de linhas, agregar os dados em segundo plano e carregar apenas os resultados resumidos na sua planilha.
• Edição não destrutiva: O Power Query nunca altera sua fonte de dados original. Ele cria uma conexão, lê os dados e gera uma cópia recém-limpa.

Começando: O Editor do Power Query

Para acessar o Power Query, abra uma pasta de trabalho em branco do Excel e navegue até a guia Dados na Faixa de Opções. Procure o grupo Obter e Transformar Dados no canto esquerdo.

A partir daqui, você pode clicar em Obter Dados para ver um menu suspenso de fontes de dados disponíveis. Depois de selecionar um arquivo e clicar em "Transformar Dados", o Excel abre o Editor do Power Query em uma nova janela. Essa interface consiste em quatro áreas principais:

1. A Faixa de Opções: Localizada na parte superior, contendo guias como Página Inicial, Transformar, Adicionar Coluna e Exibição. É aqui que vivem todas as suas ferramentas.
2. O Painel de Consultas: Localizado à esquerda, listando todas as conexões de dados ativas em sua pasta de trabalho.
3. A Visualização de Dados: A grade central que mostra uma visualização em tempo real de como seus dados estão na etapa selecionada no momento.
4. O Painel de Configurações de Consulta (Etapas Aplicadas): Localizado à direita. Esta é a parte mais importante do editor. Toda vez que você altera algo, o Power Query registra isso aqui como uma etapa sequencial. Você pode excluir, reordenar ou editar essas etapas a qualquer momento.

Passo a Passo: Limpando Dados de Vendas Desorganizados

Vamos dar uma olhada em um exemplo prático do mundo real. Imagine que você exporte um relatório de vendas semanal do CRM da sua empresa. A exportação bruta é desorganizada, contendo cabeçalhos desnecessários, sequências de texto combinadas e formatação inconsistente.

Aqui está uma amostra dos nossos dados brutos e desorganizados:

Exportação do Sistema: Relatório de Vendas do 3º Trimestre	Coluna2	Coluna3
Gerado em: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Se usássemos fórmulas tradicionais, teríamos que usar LEFT, RIGHT, FIND e VALUE para extrair os nomes dos representantes e corrigir os números. Vamos usar o Power Query em vez disso.

Passo 1: Importar os Dados

Salve os dados desorganizados como um arquivo CSV ou Excel. Abra uma nova pasta de trabalho do Excel, vá em Dados > Obter Dados > De Arquivo e selecione seu arquivo. Quando a janela de visualização aparecer, clique em Transformar Dados. O Editor do Power Query será aberto.

Passo 2: Remover as Linhas Principais e Promover Cabeçalhos

As duas primeiras linhas dos nossos dados são metadados de exportação do sistema, não registros de dados reais. Precisamos nos livrar delas.

• Na guia Página Inicial, clique em Remover Linhas > Remover Principais Linhas. Digite "2" e clique em OK.
• Agora, nossos cabeçalhos de coluna reais estão na linha 1. Clique no botão Usar a Primeira Linha como Cabeçalho na guia Página Inicial. Instantaneamente, "Rep_ID_Name", "Order_Date" e "Revenue" se tornam seus títulos oficiais de coluna.

Passo 3: Dividir a Coluna de Representantes

A coluna "Rep_ID_Name" contém o número de identificação e o nome do funcionário separados por um hífen.

• Clique com o botão direito do mouse no cabeçalho da coluna "Rep_ID_Name".
• Selecione Dividir Coluna > Por Delimitador.
• Escolha "Personalizado" e digite um hífen ( - ). Clique em OK.
• O Power Query divide os dados em duas colunas. Agora você pode clicar duas vezes nos novos cabeçalhos das colunas para renomeá-los para "Rep_ID" e "Rep_Name".

Para limpar os sublinhados no nome do Bob (Bob_Jones), clique com o botão direito na coluna Rep_Name, escolha Substituir Valores, digite um sublinhado (_) na caixa "Valor a ser Localizado" e deixe a caixa "Substituir Por" em branco ou adicione um espaço. Clique em OK.

Passo 4: Padronizar Tipos de Dados

Notou como nossas datas e receitas estão em formatos completamente diferentes? O Power Query facilita a padronização disso.

• Clique no pequeno ícone no lado esquerdo do cabeçalho da coluna "Order_Date" (provavelmente diz "ABC" ou "Qualquer"). Mude-o para Data. O Power Query traduz automaticamente os diferentes formatos de data em uma data padrão unificada.
• Clique no ícone ao lado de "Revenue" e altere-o para Moeda (ou Número Decimal). Isso remove os cifrões e formata tudo de maneira limpa.

Passo 5: Adicionar uma Coluna Condicional

Digamos que queremos categorizar as vendas acima de US$ 1.000 como "High Value" (Alto Valor). Em vez de escrever uma função IF complexa como =IF(C2>=1000, "High Value", "Standard") no Excel, podemos usar a interface de usuário do Power Query.

Vá até a guia Adicionar Coluna e clique em Coluna Condicional. Defina as regras: se [Revenue] for maior ou igual a 1000, retorne "High Value", senão "Standard". Nos bastidores, o Power Query gera o seguinte código M para esta etapa:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Mesclando Consultas: A Alternativa Moderna ao VLOOKUP

Uma das tarefas mais comuns na análise de dados é combinar tabelas. Se você tiver uma tabela separada contendo a região de cada Representante de Vendas, geralmente você recorreria ao nosso guia completo sobre o VLOOKUP para trazer esses dados.

No entanto, executar milhares de fórmulas VLOOKUP ou INDEX e MATCH pode deixar sua pasta de trabalho drasticamente mais lenta. No Power Query, você usa o recurso de Mesclar Consultas.

Basta importar ambas as tabelas para o Power Query, selecionar sua tabela de vendas principal e clicar em Mesclar Consultas na guia Página Inicial. Selecione a segunda tabela (a tabela de Regiões), clique na coluna correspondente em ambas as tabelas (ex: "Rep_ID") e clique em OK. O Power Query executa o equivalente a um VLOOKUP ultrarrápido em segundos, não importa se você tem dez linhas ou dez milhões.

Cancelar Dinamização de Dados: A Arma Secreta do Power Query

Muitas vezes, você recebe dados que já estão agrupados em uma estrutura semelhante a uma tabela dinâmica (por exemplo, os meses distribuídos nas colunas: Jan, Fev, Mar, Abr). Embora isso seja fácil para os humanos lerem, é péssimo para criar gráficos ou Tabelas Dinâmicas.

Selecione suas colunas identificadoras (como o Nome do Representante), clique com o botão direito do mouse no cabeçalho e escolha Cancelar Dinamização de Outras Colunas. O Power Query transforma instantaneamente seus dados tabulares cruzados e largos em um layout tabular simples, com uma nova coluna de "Atributo" (Mês) e "Valor" (Vendas). Fazer isso com fórmulas padrão do Excel é quase impossível, tornando o recurso de cancelar dinamização um dos mais celebrados do Power Query.

Carregando os Dados Transformados

Depois que seus dados estiverem perfeitamente limpos, é hora de enviá-los de volta para o Excel.

Na guia Página Inicial, clique em Fechar e Carregar. Por padrão, isso carregará seus dados transformados em uma nova Tabela do Excel verde em uma nova planilha. Se você preferir enviar os dados direto para sua fase de análise, pode clicar na seta do menu suspenso, escolher Fechar e Carregar Para... e, em vez disso, selecionar um Relatório de Tabela Dinâmica. Se você precisar relembrar como criar esses resumos, confira nosso tutorial sobre como criar tabelas dinâmicas para iniciantes.

A Mágica do Botão "Atualizar"

O verdadeiro poder do Power Query fica evidente na próxima semana, quando você receber uma nova exportação bruta de vendas. Não repita os passos acima!

Basta salvar o novo arquivo CSV sobre o antigo (mantenha exatamente o mesmo nome de arquivo e local da pasta). Em seguida, abra a sua pasta de trabalho do Excel, clique com o botão direito em qualquer lugar na sua tabela de dados limpa e clique em Atualizar.

O Power Query se conecta ao arquivo, reaplica todas as etapas – removendo linhas, promovendo cabeçalhos, dividindo colunas, substituindo textos, verificando condições e mesclando tabelas – e atualiza seu resultado final em uma fração de segundo. Este é um componente vital dos fluxos de trabalho de automação no Excel.

Simplificando Sua Jornada de Dados com a IA

Embora o Power Query lide brilhantemente com transformações estruturais, às vezes você precisa de uma lógica condicional específica ou de uma análise de texto complexa que exija fórmulas avançadas do Excel ou código M personalizado. Em vez de vasculhar fóruns em busca de respostas, você pode aproveitar a inteligência artificial.

Se você se deparar com dificuldades para escrever o cálculo perfeito da coluna personalizada, o GPTExcel é o companheiro ideal. Basta descrever o que você está tentando alcançar de forma simples e direta – por exemplo, "Preciso de uma fórmula para extrair apenas os números de uma sequência de texto mista" – e o GPTExcel gerará instantaneamente a fórmula ou o código M correto. Combinar o Power Query com a IA para limpeza de dados oferece a você um conjunto de ferramentas imbatível para a análise de dados.

Perguntas Frequentes

O Power Query altera meu arquivo de dados original?

Não. O Power Query cria uma conexão unidirecional com a sua fonte de dados. Ele lê os dados, aplica as transformações na memória e gera um novo resultado no Excel. O seu CSV, banco de dados ou pasta de trabalho original permanece totalmente intacto e seguro.

Posso usar o Power Query no Mac?

Sim, a Microsoft melhorou significativamente o suporte ao Power Query no Excel para Mac. Embora a versão para Mac tradicionalmente não tivesse alguns dos conectores avançados e recursos de interface do usuário disponíveis no Windows, agora você pode se conectar a arquivos locais, bancos de dados e atualizar as consultas existentes sem problemas nas versões modernas do Microsoft 365.

Qual é a diferença entre Mesclar e Acrescentar no Power Query?

Mesclar é o equivalente a um VLOOKUP ou INDEX/MATCH. Você o utiliza para adicionar novas colunas de dados fazendo a correspondência de um ID comum entre duas tabelas. Acrescentar é como copiar e colar dados na parte inferior de uma planilha. Você o utiliza para empilhar tabelas umas sobre as outras, adicionando novas linhas (por exemplo, combinando as vendas de janeiro e fevereiro).

Por que a atualização do meu Power Query falhou?

O motivo mais comum para a falha na atualização de uma consulta é que o arquivo de origem foi movido, renomeado ou excluído. Outro problema frequente é que um cabeçalho de coluna nos dados brutos mudou (ex: "Revenue" foi alterado para "Total Revenue" pelo sistema). Você pode corrigir isso abrindo o Editor do Power Query, acessando o painel de Etapas Aplicadas e atualizando a etapa de Origem ou renomeando a coluna na lógica da sua etapa.