Como usar o Power Query no Excel: o guia completo

O Power Query é uma poderosa ferramenta de business intelligence no Excel que permite importar, limpar e transformar dados de acordo com suas necessidades. Se você já passou horas limpando dados (nós temos) ou lutou para mesclar dados de várias fontes (nós também temos), esse recurso facilitará sua vida.

A melhor maneira de aprender a usar o Power Query no Excel é realizar algumas tarefas comuns, como importar dados e usar o Editor do Power Query para transformar as tabelas resultantes e combinar os dados.

Este artigo mostra exemplos claros de Power Query com guias passo a passo . Ao dominar o Power Query, você pode elevar seu trabalho para o próximo nível.

Você está pronto? Vamos fazer isso.

Índice

• Como saber se você pode acessar o Power Query
• Como importar dados com Power Query
• Como usar o editor do Power Query
  • Visualização de dados
  • Propriedades
• Como trabalhar com várias fontes de dados
• Como anexar dados com Power Query
• Como mesclar dados com Power Query
• 5 maneiras de transformar dados com o Power Query
  • 1. Expansão e condensação de dados
  • 2. Classificação de dados
  • 3. Dados de formatação
  • 4. Adicionando e removendo colunas
  • 5. Alterando tipos de dados
• Trabalhando com fórmulas e linguagem M
  • Barra de Fórmula
  • Escrevendo e Modificando Código M
• O que é a sintaxe da linguagem M?
• Como atualizar conexões de dados
• Como integrar o Power Query com o modelo de dados
• Como automatizar com Power Query e VBA
• Recursos avançados do Power Query
• Colaboração e Compartilhamento
• Vamos Encerrar Isso

Como saber se você pode acessar o Power Query

Antes de começar a usar o Power Query, você precisa ter certeza de que possui uma versão do Excel que possui o recurso.

Aqui está uma breve visão geral do Power Query por versão:

• Excel 2010 e Excel 2013: disponíveis como um suplemento que precisa ser baixado e instalado.

• Excel 2016: integrado ao Excel como “Get & Transform”.

• Excel 2019: melhor desempenho e funcionalidade.

• Excel 2021 e Excel para Microsoft 365: integração com fontes de dados na nuvem.

• Excel para Mac: disponível apenas no Microsoft 365 versão 16.69 e posterior.

Para verificar se a ferramenta está integrada à sua pasta de trabalho do Excel (2016 ou posterior), vá para a guia Dados e procure a seção “Obter e transformar dados” nesta imagem:

Se você tiver o Excel 2010 ou 2013 no Windows, poderá baixar , instalar e habilitar o suplemento Power Query.

Como importar dados com Power Query

A ferramenta Power Query permite importar e conectar-se a uma ampla variedade de fontes de dados:

• Arquivos, incluindo formatos CSV, XML e JSON

• URLs da Web ou endpoints de API

• Bancos de dados, incluindo Access, SQL Server, Oracle e os bancos de dados mais modernos

• Serviços do Azure, arquivos do SharePoint e feeds OData

A fonte de dados mais fácil para começar é um arquivo CSV simples com várias linhas de dados. Crie um arquivo de texto e adicione estes quatro nomes e sobrenomes, separados por vírgula:

• Joe, Blogs

• Ana, Ryan

• James, Stewart

• Maria, Marrom

Certifique-se de salvar o arquivo com a extensão .CSV. Seu arquivo ficará assim:

Para carregar os dados com o Power Query, siga estas etapas:

1. Vá para a guia Dados.

2. Clique no botão “Obter arquivos” na guia Obter e transformar dados.

3. Selecione seu arquivo de amostra.

Pode ser necessário aguardar alguns segundos para que a ferramenta extraia os dados para o painel de visualização. O Power Query usa muitos recursos de máquina, mesmo para uma pequena importação, portanto, sejam pacientes.

Dica: desligue outros aplicativos para acelerar o desempenho.

O Power Query exibirá a janela “Importação de texto/CSV” com uma visualização dos seus dados.

Se os dados parecerem corretos, você poderá carregar ou transformar os dados:

• o botão Carregar carregará os dados diretamente no Excel.

• o botão Transform Data permite modificar os dados antes de carregá-los.

Você notou que o arquivo de amostra não tinha um cabeçalho? Se for carregado diretamente no Excel, os cabeçalhos serão “Coluna 1” e “Coluna 2”.

Em vez disso, você pode extrair os dados para o editor do Power Query e fazer algumas alterações. Para isso, clique no botão “Transformar Dados”.

O Editor do Power Query agora é aberto, o que nos leva à próxima seção.

Como usar o editor do Power Query

Nesta seção, exploraremos alguns aspectos essenciais do trabalho com o Editor do Power Query.

Visualização de dados

Na área de visualização de dados, você pode visualizar e interagir com seus dados importados. Você pode filtrar linhas, modificar tipos de dados de coluna e remodelar os dados para atender às suas necessidades.

Quando terminar, o Excel carregará os dados transformados em uma planilha.

Usando o arquivo de amostra, você pode editar os nomes das colunas diretamente neste painel.

Para fazer isso:

1. Clique com o botão direito do mouse no cabeçalho da coluna.

2. Clique em "Renomear" no menu suspenso.

3. Digite os nomes preferidos nas caixas de cabeçalho.

À medida que você faz alterações, a seção “Etapas aplicadas” à direita do painel de visualização é atualizada para refletir cada transformação, permitindo que você rastreie e modifique suas etapas de processamento de dados conforme necessário.

Propriedades

Definir propriedades para suas consultas é essencial para organizar e acompanhar seu trabalho.

O painel Query Settings está à direita da janela Power Query.

1. Clique no link "Todas as propriedades".

2. Edite o nome da Consulta para ser descritivo.

3. Adicione uma descrição para fornecer mais informações.

É fácil esquecer o que as consultas da semana passada foram configuradas para fazer. Nesse caso, o nome da consulta foi alterado e foi adicionada uma descrição que fornece a finalidade da consulta.

Se você quiser saber mais sobre as opções disponíveis, confira esta visão geral do Editor do Power Query .

Como trabalhar com várias fontes de dados

O Editor do Power Query oferece dois métodos principais para combinar dados de várias fontes: anexar e mesclar.

Anexar junções de tabelas verticalmente, empilhando-as umas sobre as outras. A fusão permite unir tabelas com base em colunas comuns.

Para ver como isso funciona, crie um segundo arquivo CSV pequeno com a mesma estrutura do primeiro. Desta vez, coloque dois nomes (primeiro e último) no arquivo:

• Joe, Blogs

• Laura, Cana

Observe que um desses nomes é repetido do arquivo anterior.

Agora siga estas etapas para extrair os dados para sua pasta de trabalho do Microsoft Excel.

1. Feche a janela do Editor do Power Query e salve suas alterações.

2. Adicione uma nova planilha.

3. Siga as etapas da seção anterior para importar o segundo arquivo.

4. Clique no botão Transformar.

5. Renomeie os cabeçalhos das colunas como “Nome” e “Sobrenome”.

O painel direito na planilha do Excel mostra as consultas e conexões atuais. Agora você deve ver duas consultas no painel.

Como anexar dados com Power Query

Nesta seção, você anexará o segundo arquivo CSV de amostra ao primeiro.

Siga estas etapas para anexar dados:

1. Clique com o botão direito do mouse na primeira tabela (Nomes de clientes) no painel Consultas e conexões.

2. Escolha “Editar” para abrir a tabela no Editor do Power Query.

3. Clique em Anexar consultas na seção Combinar.

4. Escolha a segunda tabela na lista suspensa de tabelas para anexar.

O resultado será duas linhas extras na primeira tabela.

Observe que uma linha é repetida – é assim que funciona a operação de acréscimo. Ele adiciona os dados de uma tabela em outra, independentemente de os dados já existirem.

Você pode remover os dados duplicados no Editor do Power Query com estas etapas:

1. Clique no cabeçalho da coluna "Nome".

2. Segure a tecla “Ctrl” (ou “Cmd” no Mac).

3. Clique nos cabeçalhos de ambas as colunas.

4. Clique em "Remover linhas" na guia Início da faixa do Editor do Power Query.

5. Selecione "Remover duplicados" no menu suspenso.

Como mesclar dados com Power Query

Ao mesclar dados, você deve escolher as colunas correspondentes entre as consultas.

Suponha que você tenha dois arquivos delimitados por vírgulas, onde um contém números de telefone de clientes e o outro contém endereços. Os dois arquivos ficam assim:

Siga as instruções nas seções anteriores para carregar cada arquivo em sua própria consulta de energia. Então você pode praticar a fusão das tabelas.

No caso de nossos dados de amostra, ambas as colunas (Nome e Sobrenome) devem ser escolhidas como as colunas correspondentes. Mantenha pressionada a tecla Ctrl para escolher várias colunas.

Siga esses passos:

1. Clique com o botão direito do mouse na primeira tabela no painel Consultas e conexões.

2. Escolha “Editar” para abrir a tabela no Editor do Power Query.

3. Selecione “Merge Queries” no grupo “Combine”.

4. Escolha a segunda tabela na lista suspensa de tabelas.

5. Clique na primeira coluna da primeira tabela.

6. Mantenha pressionada a tecla Ctrl e clique na segunda coluna da primeira tabela.

7. Mantenha pressionada a tecla Ctrl e clique em ambas as colunas na segunda tabela.

8. Mantenha o operador de junção padrão como “Esquerda Externa”.

9. Clique em “OK” para aplicar as alterações.

O operador de junção “Left Outer” garante que os dados duplicados sejam eliminados.

No Editor do Power Query, você verá uma nova coluna chamada “Tabela” ou “Tabela Aninhada” contendo os dados mesclados da segunda consulta.

1. Clique no ícone de expansão (duas setas) no cabeçalho desta coluna.

2. Desmarque a caixa “usar coluna original como prefixo” (isso evita nomes de coluna longos).

3. Clique OK".

4. Clique em “Fechar e carregar”.

A tabela mesclada tinha quatro colunas, com os detalhes de telefone e endereço mesclados. Você ainda pode ter um pouco de arrumação para fazer.

Por exemplo, você pode querer editar o nome da tabela (Merge1) e a coluna de endereço na figura abaixo.

5 maneiras de transformar dados com o Power Query

A transformação de dados em seu arquivo do Excel é facilitada com o Power Query, permitindo que você limpe, organize e manipule seus dados com eficiência.

Algumas das formas mais comuns de transformar dados são:

• Expansão e condensação de dados

• Classificando dados

• Formatando dados

• Adicionando e removendo colunas

• Alterando tipos de dados

1. Expansão e condensação de dados

Expandir e condensar dados é importante para tornar seus conjuntos de dados mais gerenciáveis. O Power Query permite expandir ou recolher tabelas para uma exibição mais organizada.

A seção anterior mostra como expandir incluindo dados de tabelas relacionadas.

Você também pode condensá-lo para incluir apenas dados resumidos com base em uma agregação de sua escolha, como soma, contagem ou porcentagem.

2. Classificação de dados

Para classificar os dados, você pode alterar rapidamente a ordem por valores crescentes ou decrescentes.

Clique na seta para baixo ao lado do nome de uma coluna e escolha “Classificar em ordem crescente” ou “Classificar em ordem decrescente”.

3. Dados de formatação

A guia Transformar no Editor do Power Query fornece muitas opções.

Por exemplo, o menu suspenso Formato permite alterar todos os dados em uma coluna ou a tabela inteira para letras maiúsculas ou minúsculas.

4. Adicionando e removendo colunas

O Power Query simplifica o processo de adição, exclusão e modificação de colunas.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Por exemplo, você pode precisar converter pontos decimais em porcentagens ou converter porcentagens baseadas em texto em numéricas para cálculos e comparações mais fáceis.

Trabalhando com fórmulas e linguagem M

Ao usar o Power Query no Excel, você encontrará o M Language, que é o coração da criação de fórmulas avançadas, manipulação de dados e produção de consultas poderosas. Nesta seção, você aprenderá sobre:

• a barra de fórmula

• código M

• os elementos essenciais da sintaxe da linguagem M

Barra de Fórmula

A barra de fórmulas no editor Power Query do Excel é a área onde você inserirá suas fórmulas e o código M.

Para usá-lo, vá para o editor do Power Query selecionando uma consulta no painel Consultas e Conexões. A barra de fórmulas está localizada acima do painel de visualização de dados.

Escrevendo e Modificando Código M

Para criar consultas versáteis e poderosas no Excel, você precisará escrever e modificar a linguagem Power Query conhecida como código M. Aqui estão algumas dicas sobre como começar:

• Use o painel Applied Steps para escrever o código passo a passo, facilitando o rastreamento e a modificação da lógica de sua consulta.

• Combine o código M com uma tabela do Excel, tabelas dinâmicas e fontes de dados externas, como Azure e arquivos de texto, para criar relatórios dinâmicos e automatizados.

• Ajuste as opções de consulta para refinar sua preferência de carregamento de dados e melhorar o desempenho.

Ao se familiarizar com o código M, você descobrirá que pode automatizar e otimizar muitos processos, tornando sua análise de dados no Excel mais eficiente e poderosa.

Para visualizar o código por trás de uma consulta, siga estas etapas:

1. Abra a consulta no Editor do Power Query.

2. Clique em “Editor avançado” na guia Início.

Este é o código por trás de nossa tabela mesclada de amostra (a sua pode parecer diferente):

O que é a sintaxe da linguagem M?

Compreender a sintaxe da M Language é crucial para criar fórmulas eficazes do Power Query. Tenha em mente os seguintes pontos:

• A linguagem M diferencia maiúsculas de minúsculas, portanto, tenha cuidado com letras maiúsculas.

• Use colchetes [] para fazer referência a colunas, registros e listas.

• Para fazer referência a uma etapa anterior em sua consulta, use o nome da variável (por exemplo, #”Step Name”).

• Emita uma etapa de fórmula de consulta com a instrução “in”.

Ao ganhar experiência com o fluxo de linguagem do Power Query , você descobrirá novas maneiras de transformar e analisar seus dados, tornando suas pastas de trabalho do Excel um ativo ainda mais valioso.

Confira o vídeo abaixo, é hora de pegar a escova e limpar seus dados confusos.

Como atualizar conexões de dados

Um dos principais recursos do Power Query é a capacidade de atualizar facilmente sua conexão do Power Query.

Quando suas fontes de dados tiverem sido atualizadas e você precisar das informações mais recentes em sua pasta de trabalho, poderá atualizar as conexões de dados no painel Consultas e conexões.

Se você não vir o painel à direita de sua planilha, poderá abri-lo acessando a tabela Dados e clicando em “Consultas e conexões”.

Quando o painel Consultas e Conexões está aberto, ele lista todas as consultas. Siga esses passos:

1. Clique com o botão direito do mouse na consulta que deseja atualizar.

2. Selecione Atualizar no menu de contexto.

Isso atualizará sua pasta de trabalho com os dados mais recentes de suas fontes de dados conectadas.

Como integrar o Power Query com o modelo de dados

O modelo de dados do Excel é um recurso poderoso que permite criar ferramentas de business intelligence, como tabelas dinâmicas, com base em relacionamentos de dados.

O modelo de dados é basicamente um banco de dados oculto que contém os dados importados e os relacionamentos entre as tabelas.

Faz parte do recurso Power Pivot que está embutido nas versões atuais do Excel. Pode ser necessário instalar o Power Pivot como um suplemento para acessá-lo em versões anteriores).

Você pode integrar suas conexões de dados do Power Query com o modelo de dados com estas etapas:

1. Carregue seus dados usando o Power Query conforme descrito nas seções anteriores.

2. Em vez de carregá-lo diretamente na pasta de trabalho, escolha Carregar em no menu Carregar.

3. Marque a caixa de seleção Adicionar estes dados ao modelo de dados na caixa de diálogo Importar dados.

Como automatizar com Power Query e VBA

Com a integração do VBA (Visual Basic for Applications), você pode criar conexões de dados personalizadas e automatizar as etapas de transformação de dados no Power Query.

Você pode aproveitar suas habilidades VBA existentes para criar modelos de dados avançados incorporando dados de plataformas como Microsoft Access, SharePoint e Analysis Services.

Embora o Power Query não tenha suporte nativo para VBA, você pode usar o código VBA para abrir, atualizar e manipular consultas do Power Query indiretamente.

Isso permite que você crie fluxos de trabalho de dados poderosos e automatizados e simplifique seu processo de análise de dados.

Aqui está um código de exemplo que atualiza uma consulta avançada na pasta de trabalho atual.

:

Dim wb As Workbook, pq As Object

Definir wb = ThisWorkbook

Set pq = wb.Queries(“Nomes de clientes”)

 pq.Atualizar

Aqui está o código dentro de uma macro VBA do Excel:

Recursos avançados do Power Query

O Power Query do Excel oferece uma infinidade de recursos avançados para simplificar e aprimorar seu processo de análise de dados. Ao aprender a usar esses recursos com eficácia, você pode levar suas habilidades de análise de dados a um nível totalmente novo.

As ferramentas de análise de dados integradas incluem:

• Colunas condicionais para aplicar lógica complexa às suas transformações de dados.

• Funções de agrupamento e agregação para resumir seus dados de maneira significativa.

• Operações dinâmicas e não dinâmicas para remodelar seus dados para uma análise mais fácil.

Você encontrará muitos desses recursos na guia Transformar no Editor do Power Query.

Além disso, você pode conectar o Power Query a ferramentas de Business Intelligence, como o LuckyTemplates, para obter opções de visualização e análise de dados mais avançadas .

Colaboração e Compartilhamento

Compartilhar suas conexões de dados, consultas e transformações com os membros de sua equipe é essencial para uma colaboração eficaz.

Você pode compartilhar uma pasta de trabalho com consultas salvando-a em uma unidade de rede, SharePoint ou algum outro local de armazenamento compartilhado.

Você também pode exportar o código M por trás de uma única consulta com estas etapas:

1. Edite a consulta no Editor do Power Query.

2. Clique em “Editor avançado” na guia Início.

3. Copie todo o código M na janela do Editor Avançado.

4. Abra um editor de texto (por exemplo, bloco de notas) e cole o código M copiado no editor de texto.

5. Salve o arquivo de texto e envie para seu colega.

Vamos Encerrar Isso

Com uma variedade de recursos e sua interface amigável, o Power Query é uma ferramenta essencial para usuários do Excel que desejam trabalhar de maneira eficaz com dados externos.

Esse recurso valioso ajuda a simplificar o processo de criação de relatórios, limpeza de dados e realização de análises complexas em suas tabelas de dados. Usando

O Power Query economiza tempo e aumenta a eficiência ao trabalhar com arquivos do Excel.