Power Query e Power Pivot são duas ferramentas separadas dentro do Excel. O Power Query traz os dados e os limpa. O Power Pivot mantém várias tabelas juntas, para que você possa resumi-las como uma só. Saber qual usar economiza horas de trabalho manual repetitivo.
Power Query: limpe uma vez, atualize sempre
Você começa na guia Dados, com Obter Dados. O Power Query consegue ler um arquivo CSV ou do Excel, uma pasta inteira de arquivos, um banco de dados ou uma página da web. Os dados abrem no Editor do Power Query, onde você remove linhas em branco, divide colunas, altera tipos, filtra e faz mais.
Cada ação é registrada como uma etapa aplicada em uma lista à direita. Clique em uma etapa para ver os dados como estavam naquele ponto, ou exclua uma etapa que você não quer mais. Quando você escolhe Fechar e Carregar, o resultado vai para uma tabela de planilha ou para o Modelo de Dados.
No mês seguinte, coloque a nova exportação no mesmo lugar e pressione Atualizar Tudo. As mesmas etapas são executadas de novo. Seu arquivo original nunca é alterado: o Power Query apenas lê dele.
Tarefas em que ele se sai bem
- Anexar: empilhe doze arquivos mensais com as mesmas colunas em uma única tabela.
- Mesclar: combine duas tabelas por uma coluna em comum, um pouco como uma pesquisa, mas trazendo todas as correspondências de uma só vez.
- Desdinamizar: transforme uma tabela larga, com uma coluna por mês, em uma lista organizada com as colunas Mês e Valor, bem mais fácil de resumir.
- Limpar texto: remova espaços extras, corrija as maiúsculas e minúsculas, divida "Smith, Ann" em duas colunas.
Se você faz a mesma limpeza à mão mais de duas vezes, provavelmente é uma tarefa para o Power Query.
Power Pivot: várias tabelas, um só resumo
Imagine uma tabela Pedidos com uma coluna ClienteID e uma tabela separada Clientes com a região de cada cliente. Sem o Power Pivot, você copiaria a região para Pedidos com uma pesquisa. Com ele, você adiciona as duas tabelas ao Modelo de Dados e cria um relacionamento entre as duas colunas ClienteID. Uma tabela dinâmica pode então agrupar os pedidos por região diretamente.
O Modelo de Dados também armazena os dados de forma compactada, por isso pode conter muito mais linhas do que o limite de 1,048,576 linhas de uma planilha. Você pode escrever suas próprias medidas em uma linguagem de fórmulas chamada DAX, por exemplo um total que ignora pedidos cancelados.
Como escolher, e alguns cuidados
- Entrada bagunçada ou que muda: Power Query.
- Várias tabelas relacionadas, dados muito grandes, medidas reutilizáveis: Power Pivot (o Modelo de Dados).
- Dados pequenos, organizados e pontuais: fórmulas comuns ou uma tabela dinâmica normal bastam.
- O recurso varia conforme a edição do Excel e a plataforma. A janela do Power Pivot, em especial, não está em todas as edições, e o Excel para Mac tem menos opções que o Excel para Windows.
As duas ferramentas funcionam bem juntas: limpe com o Power Query, carregue para o Modelo de Dados e depois relacione e resuma.