Tudo sobre o Power Query no Excel

Aos amantes de Excel como eu, resolvi criar este artigo sobre tudo o que você precisa para fazer coisas incríveis no Power Query no Excel.

Você já deve ter ouvido falar que o conhecimento em Excel pode mudar vidas e posso te garantir que isso mudou a minha vida profissional.

Além disso, quero compartilhar com você as coisas mais úteis e incríveis que poderá fazer com o Power Query no Excel e se dar bem no seu trabalho.

Seja qual for a área que você trabalha, financeiro, suprimentos, administrativo ou até com dados, todos poderão tirar bom proveito deste post.

O que é o Power Query?

O Power Query é uma ferramenta da Microsoft, que além do Excel está disponível em várias outras soluções do power platform, que pode te ajudar a pegar informações de várias fontes diferentes, como bancos de dados, arquivos, APIs e até mesmo páginas da internet.

Contudo, com o Power query, você pode limpar e organizar dados, deixando-os prontos para usar em planilhas do Excel ou outros modelos de dados.

Em resumo, com o Power query você pode usar o processamento ETL (extrair, transformar e carregar) os dados.

Principais usos do Power Query no Excel

Se você quer conhecer um caso de uso prático, escrevi um artigo sobre como usar o Power Query para buscar e tratar dados de uma API com paginação e tem vários exemplos mão na massa, inclusive utilizando a Linguagem M no Power Query.

1. Limpeza e transformação de dados: Você pode usar o Power Query para limpar e transformar dados de diversas fontes, como bancos de dados, arquivos CSV, XML, JSON e outros. Ele permite filtrar, agrupar e ordenar dados, remover duplicatas, aplicar cálculos e muitas outras transformações para preparar os dados para uso em análises.
2. Combinação de dados de outras fontes: Você também pode combinar dados de várias fontes diferentes em uma única tabela. Por exemplo, você pode combinar dados de vendas de diferentes filiais ou dados de várias planilhas do Excel em uma única tabela.
3. Criação de consultas personalizadas: O Power Query permite criar consultas personalizadas para atender às suas necessidades específicas de análise de dados pois com ele você pode aplicar filtros, criar campos calculados ou consultas personalizadas. Em suma, automatizar o processo de análise de dados.
4. Atualização de dados: Uma vez que você tenha criado uma conexão com uma fonte de dados no Power Query, é possível atualizar os dados automaticamente sempre que houver uma alteração na fonte. Em resumo, isso ajuda a manter seus dados sempre atualizados e a evitar a necessidade de atualizar manualmente suas planilhas.

Como usar o Power Query no Excel?

Primeiramente é preciso avaliar se tem o ele na versão do seu Excel. Mas já adianto que vou dar um passo a passo de como instalar caso sua versão seja um pouco mais antiga.

Você não terá a mesma experiência em recursos do Excel 2010 e do Office365, por exemplo, mas abaixo listo alguns as versões disponíveis dele no Excel e como habilitar.

Ativando e Instalando o Power Query no Excel

Versões do Excel que tem o Power Query:

• Planos de assinatura do Office365
• Excel 2016 ou posteriores do Windows

Veja uma lista completa dos recursos disponíveis em cada versão no site oficial da Microsoft.

Versões do Excel que são possíveis de instalar o suplemento do Power Query:

• Microsoft Office 2010 Professional Plus com Software Assurance
• Microsoft Office 2013

Para instalar o suplemento do power query, você pode baixar neste link. Basta você escolher o seu idioma e clicar em baixar.

Depois disso só seguir as instruções na tela de instalação

Como abrir o Power Query no Excel

Com uma nova planilha aberta, clique no menu Dados > Obter Dados > Iniciar o Editor do Power Query conforme imagem abaixo:

Fundamentos do Power Query no Excel

Agora que você já sabe o que é o Power Query e algumas de suas capacidades incríveis, vamos mergulhar nos fundamentos para garantir que você tenha uma base sólida antes de explorar funcionalidades mais avançadas. Seja você um entusiasta de dados ou alguém que está apenas começando a brincar com números, esses passos iniciais são essenciais para dominar o Power Query.

Introdução ao Ambiente do Power Query

Ao abrir o Power Query pela primeira vez, você pode se sentir um pouco perdido com tantas opções. Mas não se preocupe, eu estou aqui para te guiar. O ambiente do Editor do Power Query é intuitivo depois que você entende o layout básico. Na parte superior, você encontrará a barra de ferramentas, que inclui tudo o que você precisa para realizar transformações nos seus dados. Abaixo, há uma grande área de trabalho onde seus dados serão exibidos e manipulados.

Carregando Seus Primeiros Dados

A primeira coisa que você vai querer fazer é carregar alguns dados para trabalhar. O Power Query facilita isso com suporte para várias fontes de dados. Você pode importar dados diretamente de arquivos como Excel e CSV, ou até conectar-se a bancos de dados externos ou APIs. Aqui vai um passo a passo rápido:

1. No Excel, vá até a aba ‘Dados’ e selecione ‘Obter Dados’.
2. Escolha sua fonte de dados desejada – neste exemplo vamos usar o CSV – e siga as instruções para conectar seus dados.
3. Uma vez carregados, os dados aparecerão no Editor do Power Query.

Depois que clicar em “De Text/CSV”, vai escolher um arquivo CSV no seu computador, vou deixar um arquivo csv de exemplo pra você fazer o teste. Veja abaixo:

Como pode ver na imagem acima, onde apresento dados fictícios, o Power Query já identificou as colunas de dados que tem no arquivo e poderá utilizar duas alternativas de funções.

• Carregar: Que vai pegar os dados do arquivo e colocar em uma tabela dentro da sua planilha
• Transformar Dados: vai abrir o editor avançado do Power Query para que possa transformar os dados.

Transformações Básicas de Dados

Agora que seus dados estão carregados, vamos fazer algumas transformações básicas:

• Filtrando Dados: Você pode facilmente filtrar suas tabelas para mostrar apenas as informações que você precisa. Por exemplo, se você estiver trabalhando com contatos e quiser ver apenas os contatos de uma cidade específica, basta aplicar um filtro.
• Ordenando Dados: Ordenar seus dados pode ajudá-lo a fazer análises mais rapidamente. Quer ver os contatos por ordem alfabética? Simplesmente ordene a coluna de vendas de forma crescente.
• Agrupar Dados: No Power Query, você pode agrupar dados conforme necessário. Por exemplo, se você quiser saber quantos contatos tem por cidade, você pode fazer isso de forma bem simples. Veja nas imagens abaixo,

Abaixo veja como a informação fica fácil de ver e entender.

Salvando e Carregando Consultas

Uma das melhores coisas sobre o Power Query é que você não precisa repetir todo o trabalho toda vez. Você pode salvar suas consultas:

1. Após ajustar seus dados, selecione a opção ‘Fechar & Carregar’ para salvar a consulta.
2. Os dados transformados serão carregados de volta ao Excel, onde você pode usá-los como qualquer outra tabela.

Dessa maneira, você pode facilmente atualizar seus dados quando novas informações forem disponibilizadas, sem ter que refazer todo o processo manualmente.

Primeiros Passos com a Linguagem M

Depois de entender os fundamentos do Power Query, você está pronto para dar um passo adiante e começar a explorar a Linguagem M, o coração programático que impulsiona as transformações de dados no Power Query. Se você já leu meu post anterior sobre a Linguagem M, sabe que ela é uma poderosa ferramenta para personalizar e automatizar o tratamento de dados. Agora, vamos colocar esse conhecimento em prática com alguns exemplos básicos e dicas para iniciantes.

Introdução à Linguagem M

A Linguagem M é uma linguagem de programação funcional usada pelo Power Query para realizar transformações de dados complexas de maneira eficiente e controlada. Ela permite que você vá além das funcionalidades padrão da interface do Power Query, oferecendo um controle mais granular sobre seus dados.

Exemplos Simples de Fórmulas M

Vamos começar com alguns exemplos simples para você sentir o poder da Linguagem M:

• Adicionando uma Coluna Calculada: Suponha que você queira adicionar uma coluna que mostre o dobro do valor de uma coluna existente. Você pode criar essa nova coluna com uma fórmula simples em M:m

= Table.AddColumn(#"Tabela Anterior", "Dobro", each [Coluna Existente] * 2)

• Filtrando Dados: Se você deseja filtrar linhas baseadas em uma condição específica, como todos os itens com vendas superiores a 100 unidades, sua fórmula seria:

= Table.SelectRows(#"Tabela Anterior", each [Vendas] > 100)

Dicas para Começar com a Linguagem M

1. Estude o Contexto: Entender o contexto de cada função é crucial. Cada função em M tem um propósito e saber quando usar cada uma pode economizar muito tempo.
2. Experimente no Editor: Use o Editor Avançado no Power Query para experimentar suas fórmulas M. Isso oferece um feedback imediato e ajuda a entender melhor o impacto de suas transformações.
3. Use Documentação e Recursos Online: Além dos recursos que eu compartilho aqui, a documentação oficial da Microsoft sobre a Linguagem M é um excelente lugar para aprender mais e resolver dúvidas específicas.

Dicas e Truques Avançados no Power Query

Agora que você já tem uma boa noção de como o Power Query pode transformar sua maneira de trabalhar com dados no Excel, vamos mergulhar em algumas dicas e truques avançados que podem elevar ainda mais seu nível de eficiência. Essas dicas são para quem já está familiarizado com o básico e quer explorar o verdadeiro potencial desta ferramenta poderosa.

Automatizando Tarefas Repetitivas

Quem não gosta de economizar tempo? No Power Query, você pode automatizar várias das tarefas repetitivas que, normalmente, consumiriam uma parte significativa do seu dia. Por exemplo, se você regularmente importa dados de uma fonte específica e aplica os mesmos passos de transformação, pode criar uma consulta que faz todo esse trabalho com apenas alguns cliques.

Uma maneira de fazer isso é utilizando o recurso de “Gravar Macro” enquanto executa as etapas no Power Query. Depois, sempre que precisar realizar a mesma tarefa, é só rodar a macro gravada.

Utilizando Funções Avançadas da Linguagem M

A Linguagem M é o coração do Power Query, e dominá-la pode abrir um mundo de possibilidades. Aqui vão algumas funções avançadas que você pode começar a explorar:

• Text.Start e Text.End: Ótimas para manipular strings de texto, permitindo que você extraia facilmente partes específicas de dados textuais.
• Date.Day, Date.Month e Date.Year: Use essas funções para decompor campos de data em seus componentes, facilitando análises específicas por período.

Não tenha medo de experimentar com a Linguagem M. Às vezes, uma pequena função pode economizar uma enorme quantidade de tempo!

Otimização de Consultas para Melhor Desempenho

Se você está trabalhando com grandes conjuntos de dados, otimizar suas consultas no Power Query é essencial para garantir que elas sejam executadas rapidamente e sem consumir recursos excessivos. Aqui estão algumas dicas para otimizar suas consultas:

• Minimize as Colunas Carregadas: Antes de começar a transformar os dados, remova todas as colunas que não são necessárias para sua análise. Isso reduz a carga de processamento e acelera a consulta.
• Filtre os Dados o Mais Cedo Possível: Aplicar filtros nas etapas iniciais de sua consulta pode significativamente diminuir o volume de dados com os quais você estará trabalhando nas etapas subsequentes.

Lembre-se, o Power Query é uma ferramenta incrivelmente poderosa, mas seu verdadeiro potencial é desbloqueado quando você começa a explorar além das funcionalidades básicas. Experimente, teste e não tenha medo de falhar – cada erro é uma lição aprendida no caminho para se tornar um mestre do Power Query!

Solucionando Problemas Comuns no Power Query

Conforme você se aprofunda no uso do Power Query, pode encontrar alguns desafios e obstáculos pelo caminho. Não se preocupe! Aqui, vou compartilhar algumas das dificuldades mais comuns e como superá-las, garantindo que sua experiência com o Power Query seja o mais suave possível.

Problema 1: Dados Não Carregam Corretamente

Às vezes, ao importar dados, você pode notar que eles não aparecem como esperado ou alguns registros estão faltando.

• Solução: Verifique se todas as fontes de dados estão configuradas corretamente e se as conexões estão ativas. Certifique-se também de que não existem filtros ou condições inadvertidamente aplicadas na consulta que poderiam estar excluindo dados. Revisar as etapas aplicadas no Editor do Power Query pode ajudar a identificar onde o problema está ocorrendo.

Problema 2: Erros de Performance ao Manipular Grandes Conjuntos de Dados

Manipular grandes volumes de dados pode levar a problemas de desempenho, como lentidão ou travamentos.

• Solução: Utilize a funcionalidade de carregar apenas uma parte dos dados para testar suas transformações antes de aplicá-las ao conjunto completo. Isso pode ser feito através da opção ‘Carregar para’ e selecionando ‘Carregar apenas uma parte dos dados’. Além disso, otimize suas consultas evitando transformações desnecessárias e utilizando a filtragem o mais cedo possível na sua sequência de transformações.

Problema 3: Dificuldades com Formatos de Data e Hora

A manipulação de datas e horas é um desafio comum, especialmente quando os dados vêm de múltiplas fontes com formatos diferentes.

• Solução: Use as ferramentas de transformação de data/hora do Power Query para padronizar todos os dados de entrada para o mesmo formato. Isso pode ser feito selecionando a coluna de data/hora e utilizando as opções sob o menu ‘Transformar’ para ajustar formatos, fusos horários e outras peculiaridades de data/hora.

Problema 4: Consultas Quebrando após Atualizações de Fonte de Dados

Quando a estrutura da fonte de dados muda, como a adição ou remoção de colunas, isso pode causar falhas nas consultas existentes.

• Solução: Mantenha uma comunicação clara com os administradores das fontes de dados para ser alertado sobre mudanças. Implemente verificações de sanidade em suas consultas para detectar alterações nas estruturas de dados e adaptar as consultas conforme necessário. Usar a funcionalidade de ‘Detecção de erros’ do Power Query também pode ajudar a identificar e resolver esses problemas rapidamente.

Integrando o Power Query com Outras Ferramentas do Excel

O Excel é uma caixa de ferramentas completa quando se trata de análise de dados, e o Power Query é apenas uma das muitas ferramentas poderosas disponíveis. Integrar o Power Query com outras ferramentas como o Power Pivot e as Tabelas Dinâmicas pode elevar sua capacidade de análise a um novo nível. Vamos explorar como você pode fazer essas integrações e maximizar o potencial dos seus dados.

Power Query e Power Pivot: Uma Parceria Poderosa

O Power Pivot permite analisar grandes conjuntos de dados e criar modelos de dados complexos. Quando combinado com o Power Query, você pode automatizar o processo de coleta e transformação de dados antes de carregá-los no Power Pivot para análise.

• Como Integrar:
  1. Use o Power Query para importar e transformar seus dados.
  2. Carregue os dados transformados diretamente para o Power Pivot utilizando a opção ‘Carregar para’ e escolhendo ‘Modelo de Dados’.
  3. No Power Pivot, você pode criar relações entre diferentes tabelas, calcular colunas e medidas, e preparar seus dados para análises complexas.

Power Query e Tabelas Dinâmicas

As Tabelas Dinâmicas são excelentes para resumir dados e realizar análises interativas. Com dados preparados e otimizados pelo Power Query, suas Tabelas Dinâmicas serão mais robustas e flexíveis.

• Como Integrar:
  1. Após transformar seus dados no Power Query, carregue-os para uma tabela no Excel.
  2. Com os dados já no Excel, insira uma Tabela Dinâmica selecionando os dados e usando a opção ‘Inserir Tabela Dinâmica’.
  3. Configure sua Tabela Dinâmica para analisar os dados da forma que você precisar, aproveitando a qualidade e a estrutura dos dados preparados pelo Power Query.

Automatizando Relatórios e Dashboards

Uma vez que você tem seus dados limpos e estruturados pelo Power Query e modelos analíticos prontos via Power Pivot, você pode automatizar a atualização de dashboards e relatórios no Excel.

• Como Integrar:
  1. Assegure que seus dados no Power Query estejam configurados para atualizar automaticamente ou mediante solicitação.
  2. Vincule seus modelos de dados do Power Pivot e Tabelas Dinâmicas aos dashboards do Excel.
  3. Utilize recursos do Excel, como gráficos e controles de formulário, para criar dashboards interativos que se atualizam conforme os dados mudam, pois poderá apresentar melhor a resposta do problema que os dados foram demandados.

Estudos de Caso e Exemplos Práticos

Além disso, explorar estudos de caso e exemplos práticos é uma excelente maneira de entender como o Power Query pode ser aplicado em cenários do mundo real. Portanto, nesta seção, vamos mergulhar em alguns casos onde o Power Query foi utilizado para resolver problemas específicos de dados, proporcionando soluções eficientes e inovadoras.

Estudo de Caso 1: Automatização da Geração de Relatórios Financeiros

Um grande desafio para muitas empresas é a automatização de relatórios financeiros mensais. Tradicionalmente, este processo pode ser tedioso e propenso a erros, especialmente se realizado manualmente. No entanto, com o Power Query, uma empresa conseguiu automatizar a coleta e a preparação dos dados, reduzindo significativamente o tempo de geração do relatório.

• Solução Implementada: Primeiramente, a empresa utilizou o Power Query para importar dados de várias fontes, incluindo sistemas ERP e planilhas Excel. Em seguida, utilizou-se a ferramenta para limpar e transformar esses dados. Por fim, os dados foram carregados em um modelo de dados no Power Pivot, onde foram criadas medidas e relações para análises complexas.
• Resultado: Como resultado, o tempo necessário para preparar os relatórios foi reduzido em 70%, e a precisão dos dados melhorou, permitindo à equipe financeira focar em análise e estratégia em vez de preparação de dados.

Exemplo Prático: Integração de Dados de Vendas Multi-Canal

Outro exemplo prático é a integração de dados de vendas provenientes de diferentes canais. Uma empresa de e-commerce estava enfrentando dificuldades para combinar dados de vendas online e offline em um único relatório.

• Solução Implementada: Inicialmente, o Power Query foi usado para acessar e combinar dados de diferentes plataformas de e-commerce e sistemas de ponto de venda. Além disso, foram aplicadas transformações para padronizar os formatos de data e categorização de produtos.
• Resultado: Eventualmente, essa integração permitiu que a empresa obtivesse uma visão holística de suas vendas, o que foi crucial para otimizar as estratégias de marketing e estoque.

Estudo de Caso 2: Limpeza de Dados de Pesquisa de Cliente

Por fim, consideremos um departamento de marketing que precisava analisar grandes conjuntos de dados de pesquisa de cliente. Os dados, no entanto, vinham de várias fontes e continham numerosos erros e inconsistências.

• Solução Implementada: O departamento utilizou o Power Query para consolidar e limpar os dados, utilizando funções de transformação para corrigir erros comuns e remover duplicatas.
• Resultado: Graças ao Power Query, a qualidade dos dados melhorou drasticamente, proporcionando insights mais precisos sobre a satisfação e preferências dos clientes.

Mantendo-se Atualizado com o Power Query

À medida que o Power Query evolui, também cresce a necessidade de se manter informado sobre as últimas atualizações e melhores práticas. Felizmente, há várias maneiras eficientes de se manter atualizado com as novidades e garantir que você está aproveitando ao máximo essa poderosa ferramenta. Aqui estão algumas dicas práticas para não ficar para trás.

Acompanhe as Atualizações Oficiais da Microsoft

Primeiramente, é crucial acompanhar as atualizações oficiais fornecidas pela Microsoft, que desenvolve e mantém o Power Query. O site oficial da Microsoft frequentemente posta atualizações sobre novas funcionalidades, correções de bugs e melhorias de desempenho.

Participe de Comunidades e Fóruns

Além disso, participar de comunidades online e fóruns é uma excelente maneira de aprender com outros usuários e especialistas. Espaços como o Tech Community da Microsoft e fóruns dedicados ao Excel e ao Power BI são locais onde você pode fazer perguntas, compartilhar conhecimento e descobrir soluções inovadoras.

Explore Recursos Educacionais e Tutoriais

Outra forma eficaz de se manter atualizado é explorar recursos educacionais e tutoriais disponíveis online. Meu canal do YouTube, por exemplo, oferece uma variedade de vídeos tutoriais sobre como usar o Power Query, desde conceitos básicos até técnicas avançadas. Você pode se inscrever aqui para não perder nenhum novo conteúdo que pode ajudar a ampliar seu domínio sobre esta ferramenta.

Inscreva-se na Newsletter do Blog

Por fim, para garantir que você receba as últimas notícias e atualizações diretamente no seu e-mail, inscreva-se na newsletter do meu blog. Ao fazer isso, você terá acesso não apenas a novidades sobre o Power Query, mas também a uma ampla gama de tópicos relacionados ao Excel e análise de dados. Isso pode ser feito facilmente acessando o site do blog e preenchendo o formulário de inscrição.

Conclusão

Em resumo, manter-se informado sobre as atualizações do Power Query é essencial para maximizar sua eficácia e eficiência ao trabalhar com dados. Ao seguir estas dicas e utilizar os recursos disponíveis, como vídeos educativos e newsletters, você estará sempre à frente no seu jogo de análise de dados. Não perca a oportunidade de aprimorar suas habilidades e estar sempre atualizado com as últimas tendências e técnicas no mundo do Excel e do Power Query.