Como Usar Power Query e Power Pivot no Excel Como um Profissional

Se você quer ser um verdadeiro usuário avançado de planilhas, precisa aprender sobre os recursos Power Query e Power Pivot no Excel. Embora você possa fazer muito apenas com o Excel, usar esses recursos “Power” integrados o transformará em um usuário profissional avançado do Excel. Neste guia, você aprenderá como usar o Power Query para importar potencialmente centenas de arquivos diferentes com milhões de linhas e usar o Power Pivot para gerar análises complexas nos enormes modelos de dados que você importa.

Índice

• 1. Importando Dados de Múltiplos Arquivos
• 2. Filtrando os Arquivos e Linhas Importados
• 3. Carregando Dados Filtrados em um Modelo de Dados
• 4. Analisando Dados com Tabela Dinâmica
• 5. Apresentando Seus Dados com PowerChart
• Perguntas Frequentes

Quer proteger seu trabalho de olhares curiosos? Aprenda como adicionar uma senha ao seu arquivo do Excel.

1. Importando Dados de Múltiplos Arquivos

Usando o Power Query, você pode importar dados de diferentes arquivos em uma única pasta. Tenha cuidado, pois você ainda deve garantir que todos os dados sigam o mesmo formato. Mantenha o número de colunas e os nomes dos cabeçalhos das colunas iguais e assegure-se de que o tipo de dado para os valores em cada coluna seja consistente.

Para este guia, estamos usando cinco arquivos que compreendem ~100 milhões de linhas de dados do Kaggle. Baixe-os se quiser acompanhar ou use seus próprios dados. Com isso em mente, aqui estão os passos para importar os dados:

1. Clique na guia “Dados” na faixa de opções.
2. Selecione “Obter Dados -> De Arquivo -> De Pasta”, em seguida, navegue até sua pasta e selecione-a.

1. Você deve ver uma nova janela aparecer. No nosso caso, temos cinco arquivos de teste que são nomeados de acordo com seu tamanho: “adult10m” tem 10 milhões de linhas de dados, “adult1m” tem 1 milhão de linhas, “adult100k” tem 100.000 linhas, e assim por diante. Clique em “Transformar Dados” para abrir o Editor do Power Query.

2. Filtrando os Arquivos e Linhas Importados

No Editor do Power Query, você pode aplicar filtros para importar apenas os arquivos que correspondem aos critérios que você definir, o que é muito útil para eliminar fontes de dados que você não deseja.

1. Temos quatro arquivos sem extensão de arquivo, mas um arquivo com a extensão .DATA. Vamos remover o ímpar clicando na seta ao lado de “Extensão.”

1. Podemos ver uma lista de todas as extensões de arquivo dos nossos arquivos. Neste exemplo, estas são apenas (em branco) e .data. Desmarque as extensões que você não deseja.

1. Cada filtro que aplicamos aparecerá na seção “Etapas Aplicadas”.
2. Também podemos filtrar pelo nome do arquivo clicando na seta ao lado de “Nome.”

1. Digamos que quiséssemos filtrar qualquer arquivo que tenha “k” nele, como “adult100k”, porque não queremos trabalhar com arquivos pequenos contendo menos de um milhão de linhas. Podemos aplicar um filtro de texto. Existem muitas opções, mas para nossos propósitos, o filtro “Não contém” é o correto.

1. Digite o texto que você não quer ver no nome do arquivo.

1. Role um pouco para a direita até ver as colunas “Data modificada” e “Data criada”. Vamos filtrar para selecionar apenas arquivos criados em um determinado intervalo de tempo. Clique na seta ao lado de “Data criada.”

1. Escolha “Filtros de Data/Hora -> Entre.”

1. Digite os valores de tempo para as duas condições e confirme pressionando “OK.”

1. Concluímos a filtragem dos nossos dados em nível de arquivo.
2. Vamos combinar nossos arquivos restantes para que possamos filtrar os dados em si. Clique nas setas duplas ao lado do cabeçalho “Conteúdo.”

1. Agora que você pode ver todos os valores para linhas individuais, cada linha tem uma coluna adicional, “Source.Name”, que indica o arquivo de onde se originou.

1. Para melhorar ainda mais nossos dados: como nossos arquivos CSV originalmente vieram sem nomes de cabeçalhos de coluna, você pode clicar com o botão direito em alguns dos cabeçalhos de coluna e clicar em “Renomear” para dar a eles um nome apropriado.

1. Os filtros funcionam aqui também. Podemos aplicar um filtro de intervalo numérico. Por exemplo, clique na seta ao lado do cabeçalho da coluna “Idade”, em seguida, escolha “Filtros Numéricos -> Maior que.”

1. Digite um número como 30, em seguida, clique em “OK”. Todas as linhas com idades abaixo do número especificado serão excluídas.

Dica: ao trabalhar com planilhas no Microsoft Excel, pode ser útil saber como mover uma coluna.

3. Carregando Dados Filtrados em um Modelo de Dados

Você pode continuar limpando e filtrando os dados, mas vamos seguir em frente e carregá-los em um modelo de dados para começar a analisá-los.

1. Finalize com o Power Query clicando em “Fechar e Carregar -> Fechar e Carregar para.”
2. Na janela pop-up que se abre, selecione “Apenas Criar Conexão” e marque “Adicionar esses dados ao Modelo de Dados.”

1. Aguarde o carregamento dos dados. Isso pode levar alguns minutos se você tiver uma grande quantidade de dados.

Usando o Modelo de Dados do Power Pivot no Excel

1. Após o carregamento dos dados ser concluído, podemos fazer alterações (como adicionar novas colunas) ao modelo de dados clicando em “Dados -> Ferramentas de Dados -> Gerenciar Modelo de Dados.”

1. Você deve ver a janela do Power Pivot. Vamos adicionar uma coluna com uma fórmula de Expressões de Análise de Dados (DAX). As fórmulas DAX são muito semelhantes às fórmulas do Excel que você provavelmente já conhece. Role para a direita até ver a última coluna dos seus dados, em seguida, clique em “Adicionar Coluna.”
2. Vamos escrever uma fórmula para esta coluna que aplica um cálculo simples a cada linha. Comparado às fórmulas do Excel, a sintaxe DAX é ligeiramente diferente. Por exemplo, podemos adicionar uma coluna chamada “Descrição” e inserir a fórmula =CONCATENATE([Column9],[Column10]). Note o uso de colchetes ([ ]) e o acesso via os nomes dos cabeçalhos das colunas.

1. Se desejar, você também pode realizar modificações adicionais, como renomear ou remover colunas, clicando com o botão direito no cabeçalho da coluna.

4. Analisando Dados com Tabela Dinâmica

Depois de terminar de fazer alterações no modelo de dados, é hora de analisar os dados em uma planilha do Excel usando Tabela Dinâmica.

1. Clique em “Página Inicial -> Tabela Dinâmica” na faixa de opções da janela do Power Pivot para abrir a janela “Criar Tabela Dinâmica.”

1. Selecione “Nova Planilha” e clique em “OK.”

1. O painel de Campos da Tabela Dinâmica será aberto em uma nova planilha na janela principal do Excel. Na caixa abaixo da barra de pesquisa, você deve ver suas fontes de dados listadas, “kaggle_adult_csv” neste exemplo. Clique nela para expandi-la e exibir todas as suas colunas.

1. Vamos fazer uma análise onde contamos o número de cada “Descrição” em nossos dados. Podemos selecionar as colunas com as quais queremos trabalhar clicando na caixa de seleção ao lado de seus nomes. Selecione “Descrição” e note como o foco automaticamente se desloca para a área “Linhas” abaixo. Isso é o Power Pivot demonstrando sua inteligência – ele pode adivinhar com precisão a área apropriada para as seleções. Como estamos começando de uma Tabela Dinâmica em branco, faz sentido que nossa primeira seleção sejam linhas.

1. Arraste a coluna “Descrição” marcada para o espaço vazio em “Valores.” Note que o Power Pivot novamente assume corretamente que queremos uma “Contagem de Descrição” devido ao tipo de dado ser texto. Além disso, nossa tabela foi atualizada com uma coluna fornecendo as contagens de cada ocorrência de cada valor de “Descrição.”

1. Podemos adicionar outra dimensão à nossa contagem. Por exemplo, arraste a coluna “Idade” para a área “Colunas” vazia para adicionar mais detalhes à tabela contando o número de descrições por cada idade.

1. Você também pode adicionar uma capacidade de seleção de filtro à tabela arrastando a coluna “Classe de Trabalho” para a área “Filtros.” Note a nova célula interativa que aparece acima da nossa tabela.

1. Clique na seta ao lado de “Todos,” em seguida, selecione “Nunca trabalhou.” Isso mudará a tabela para incluir apenas pessoas com uma Classe de Trabalho de “Nunca trabalhou” nos cálculos. Você pode mudar o filtro a qualquer momento para visualizar a tabela através de uma lente diferente.

Está lidando com muitos duplicados em sua planilha do Excel? Aprenda como encontrar e remover duplicados e limpar suas planilhas.

5. Apresentando Seus Dados com PowerChart

Você pode criar mais planilhas para analisar seus dados de diferentes maneiras. Desta vez, vamos criar um resultado mais visualmente impressionante usando o PowerChart.

1. Retorne à janela “Power Pivot para Excel.” Se estiver fechada, você pode abri-la a partir da janela do Excel através de “Dados -> Ferramentas de Dados -> Gerenciar Modelo de Dados.”
2. Clique em “Tabela Dinâmica -> Gráfico Dinâmico -> Nova Planilha.”

1. No painel à direita, clique na fonte de dados (“kaggle_adult_csv”) para expandi-la e exibir todas as suas colunas.
2. Arraste e solte as colunas da fonte de dados em uma das quatro áreas abaixo para construir o gráfico. Mova a coluna “Tipo de Ocupação” para a área “Eixo (Categorias).”

1. Arraste “Tipo de Ocupação” novamente, e desta vez solte-o na área “Valores.” Você notará imediatamente que um gráfico de barras é gerado automaticamente. Ele mostra a contagem de cada tipo de ocupação.

1. Você também pode arrastar a coluna “Etnia” para a área “Legenda (Séries).” Você verá instantaneamente um gráfico mais granular que compara quantos de cada etnia compõem cada ocupação.

1. Em seguida, podemos aplicar filtros à nossa Legenda para simplificar nosso gráfico e olhar apenas para certos valores. Mova o mouse sobre “Etnia” para que uma seta apareça no lado direito. Clique na seta.

1. Clique nas caixas de seleção ao lado dos valores que você deseja remover, deixando apenas dois.

1. Vamos tentar algo novo. Na seção “Valores”, clique em “Contagem de Tipo de Ocupação,” em seguida, clique em “Remover Campo.” Arraste e solte a coluna “Idade” para substituí-la.

1. Você notará que o valor é automaticamente definido como “Soma de Idade,” mas isso não é muito útil no mundo real. Vamos mudar isso. Clique nele e selecione “Configurações do Campo de Valor.”

1. Como a idade é um número, existem muitos cálculos que podemos aplicar a esta seção. Tente selecionar “Média” por exemplo.

1. O visual será atualizado para mostrar as idades médias para cada ocupação por etnias específicas (que filtramos).

Quer revisar os fundamentos do Excel? Temos um guia abrangente sobre todos os atalhos de teclado para o Excel.

Perguntas Frequentes

Quais são as origens do Power Pivot?

A Microsoft introduziu o Power Pivot como um complemento separado fornecido pelo SQL Server 2008 R2 da Microsoft, que foi lançado em 2010. Naquela época, era chamado de “PowerPivot” sem espaço. Em 2013, foi renomeado para “Power Pivot.” Ele só se tornou um recurso integrado do Excel a partir de 2016.

O que são Expressões de Análise de Dados e como elas são diferentes das fórmulas do Excel?

Expressões de Análise de Dados é uma linguagem de fórmula usada pelo Power Pivot no Excel e pelo Power BI. Em essência, é como você escreve fórmulas para cálculos nas aplicações mencionadas: as declarações, sintaxe e funções que você usa.

A principal vantagem do DAX sobre as fórmulas do Excel é que ele é projetado para operar em dados agregados. Fórmulas padrão do Excel podem realizar cálculos linha por linha (como calcular a soma de vários valores em uma linha), mas fórmulas DAX podem operar coluna por coluna (como contar o número de um valor específico em uma coluna), ou até mesmo em uma tabela inteira.

Como consertar o erro “Esta tabela foi criada por uma consulta. Para alterar esta tabela, altere a consulta em vez disso”?

Esse erro no Power Pivot do Excel acontece quando você tenta modificar um modelo de dados dentro do Power Pivot quando esse modelo de dados foi originalmente carregado via Power Query. Você não pode fazer isso, então deve modificá-lo pelo método original de carregamento: Power Query.

No lado direito do Excel, clique no ícone “Consultas e Conexões”, em seguida, encontre e clique duas vezes na sua fonte de dados da consulta para abrir a janela onde você pode modificar a tabela.

Crédito da imagem: Pexels. Todas as capturas de tela por Brandon Li.