Bottom line: Aprenda como esta fantástica funcionalidade do Excel e Power BI chamada Power Query o ajudará a automatizar o processo de importação, transformação e limpeza dos seus dados para poupar uma tonelada de tempo com o seu trabalho.
Nível de matança: Beginner
Video Tutorial
Download the Sample Files
Os ficheiros CSV que utilizo no vídeo estão disponíveis para download abaixo. Terá de extrair os ficheiros do ficheiro zip.
Ficheiros de Encomenda CSV para Power Query Overview.zip (337.0 KB)
Introdução ao Power Query
Neste tutorial dou uma explicação introdutória do Power Query. Aprenderá porque é que esta é a minha nova ferramenta Excel favorita para trabalhar com dados, e como pode ajudar a automatizar processos e poupar-lhe tempo!
The Power Query Data Machine
Estava a ver um programa de televisão sobre como as coisas são feitas, e eles estavam a explicar como uma máquina depositadora funcionava numa fábrica de pastelaria.
Os princípios básicos de uma máquina depositadora são:
- Adiciona-se-lhe ingredientes.
- Alterar algumas definições.
- E cria magicamente pastelaria (bolachas, donuts, biscoitos) que estão prontos para assar.
Após os mostradores estarem configurados, o processo pode ser repetido uma vez mais para fazer sempre pastelaria perfeita. Ficar com fome…? 🙂
P>Power Query funciona de uma forma muito semelhante!
- Adiciona as suas fontes de dados (tabelas Excel, ficheiros CSV, tabelas de bases de dados, páginas web, etc.)
- Pressione botões na janela do Power Query Editor para transformar os seus dados.
li>Envie esses dados para a sua folha de trabalho ou modelo de dados (PowerPivot) que está pronto para tabelas pivot ou relatórios.
P>Power Query é como uma máquina porque uma vez que tenha a sua consulta configurada, o processo pode ser repetido com o clique de um botão (refresh) sempre que os seus dados mudarem.
Se tiver usado macros para transformar os seus dados, pode pensar nisto como uma alternativa muito mais fácil ao VBA que NÃO requer codificação.
Tarefas Comuns de Dados Facilitadas
Trabalha com dados que foram exportados de um sistema de registo? Isto pode ser um razão geral, contabilidade, ERP, CRM, Salesforce.com, ou qualquer sistema de relatórios que contenha dados.
Se assim for, provavelmente passará muito tempo a transformar ou a remodelar os seus dados para criar relatórios adicionais, tabelas pivot, ou gráficos.
Estas transformações de dados podem incluir tarefas como:
- Remover colunas, linhas, espaços em branco
- Converter tipos de dados – texto, números, dates
- Dividir ou fundir colunas
- Adicionar colunas calculadas
- Agregar ou resumir data
- Find & substituir texto
- Dados de pivot a utilizar para tabelas pivot
li>Selecionar &filtrar colunas
Alguma destas tarefas soa familiar? Se sim, então provavelmente também parecem aborrecidas, repetitivas, e demoradas. 🙂 Acredite, passei a melhor parte da minha carreira a fazer estas tarefas e a tentar descobrir formas mais rápidas de as realizar.
Felizmente, o Power Query tem botões que automatizam todas estas tarefas!
Visão geral do Power Query Ribbon
Iniciar no Excel 2016 para Windows, o Power Query foi totalmente integrado no Excel. Está agora no separador Dados da Fita no Get & Transformar grupo.
No Excel 2010 e 2013 para Windows, o Power Query é um add-in gratuito. Uma vez instalado, o separador Power Query será visível no Excel Ribbon.
Você usa os botões no separador Data ou Power Query para obter os seus dados de origem. Mais uma vez, os seus dados podem ser armazenados em ficheiros Excel, ficheiros csv, Access, base de dados SQL server, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites, e mais.
Após ter especificado de onde vêm os seus dados, usa a janela do Power Query Editor para fazer transformações nos dados.
Os botões na Janela do Editor de Power Query permitem-lhe transformar os seus dados.
Pense em algumas das tarefas que faz repetidamente enquanto navega nos botões da imagem acima. Cada vez que premir um botão, as suas acções (passos) são gravadas, e pode rapidamente reaplicar os passos quando receber novos dados, actualizando a consulta.
Após completar os seus passos, pode emitir os dados para uma Tabela na sua pasta de trabalho Excel clicando no botão Fechar & botão Carregar.
P>Pode também modificar as consultas existentes e actualizar as suas tabelas de saída com as alterações ou dados actualizados.
Exemplos de Transformação de Dados
Aqui estão alguns exemplos do que o Power Query pode fazer com os seus dados.
Desfazer Dados para as Tabelas Pivot
A minha característica favorita do Power Query é a sua capacidade de Desfazer dados. Esta é uma técnica utilizada para preparar os seus dados para a origem de uma tabela pivot. Isto também é referido como normalização dos seus dados para os obter num formato tabular.
Os dados podem começar a parecer-se com os seguintes.
E você quer que o resultado final se pareça com isto.
Power Query pode fazer isto com o clique de alguns botões, e preparar os seus dados para utilização numa tabela pivot.
Aqui está um artigo e um vídeo sobre exactamente Como Desviar os Seus Dados com Power Query.
P>Verifica o meu artigo sobre como estruturar os seus dados de origem para uma tabela pivot se não estiver familiarizado com a razão pela qual os seus dados precisam de ter este aspecto para uma tabela pivot.
Apender (Combinar) Tabelas com Power Query
A função Apêndice do Power Query permite-lhe combinar várias tabelas (empilhá-las verticalmente) para criar uma tabela grande. Pode fazê-lo com múltiplas tabelas num só ficheiro, ou pode puxar dados de um monte de ficheiros/fontes diferentes.
Vamos dizer que tem uma pasta que contém ficheiros CSV ou Excel com dados de relatório para cada mês. Atire todos esses ficheiros para a máquina Power Query, e irá cuspir uma bela tabela que poderá depois utilizar para criar tabelas e gráficos pivot.
Se os dados desses relatórios também precisarem de ser transformados (remover linhas, dividir colunas, desdobrar, desdobrar, etc.).), então o Power Query pode tratar disso no mesmo processo.
Após ser configurado, tudo o que tem de fazer é carregar no botão refresh todos os meses quando um novo ficheiro é adicionado à pasta e as linhas serão adicionadas à sua tabela de saída.
Como é fantástico! 🙂
Merge Tables – A VLOOKUP Alternative
Power Query tem a capacidade de fundir ou juntar tabelas. Isto pode ser usado como alternativa às fórmulas VLOOOKUP ou INDEX/MATCH.
Digamos que tem esta tabela de dados de registos de vendas, e que está a utilizar um VLOOKUP para trazer informações sobre o produto com base no nome do produto vendido. A informação sobre o seu grupo de produtos encontra-se noutra tabela numa folha ou livro de exercícios diferente.
Utilizar fórmulas VLOOKUP é óptimo, mas pode muitas vezes significar adicionar milhares de fórmulas à sua pasta de exercícios. O que aumenta o tamanho do ficheiro e o tempo de cálculo.
Power Query faz com que seja muito rápido e fácil fundir duas tabelas com apenas alguns cliques. Basicamente utiliza junções SQL, pelo que pode até fazer fusões mais avançadas como interior, exterior, esquerda, direita, completa, e anti junções.
Criar Funções Personalizadas
Power Query foi concebido para que NÃO tenha de saber como codificar para o utilizar. É muito fácil de usar porque basta clicar nos botões e aplicar filtros como normalmente faria no Excel.
No entanto, o Power Query pode ser programado para criar funções personalizadas. Isto dá-lhe um potencial aparentemente ilimitado para transformar os seus dados de qualquer forma possível.
É baseado na linguagem M, e a maioria das funções são muito semelhantes à escrita de uma fórmula em Excel. Isto também torna mais fácil e fácil de aprender o código.
Esta nova linguagem e conjunto de funções significa que há muito para aprender, mas considero que a parte divertida e desafiante. Além disso, os empregadores do futuro estarão definitivamente à procura de empregados com capacidades de Power Query.
Power Query Grava os seus passos & Automates Processes
Power Query não só torna todas estas tarefas mais fáceis, como também grava os seus passos para que NÃO tenha de os repetir. Poupar-lhe-á muito tempo se estiver a preparar os mesmos dados todos os dias, semanas ou meses.
Também faz um bom trabalho de tratamento de erros. Se a estrutura dos seus dados de origem mudar, o Power Query dir-lhe-á em que passo se partiu e permitir-lhe-á corrigi-los. Isto facilita a manutenção e não tem de refazer completamente o seu processo quando algo muda.
Pode usar o Power Query para preparar os seus dados em tabelas pivot, gráficos, e relatórios do painel de instrumentos. Este é um passo crítico no processo de resumir e analisar dados.
The Power Query Machine & Power BI
Bem, não pode exactamente fazer cookies, mas o Power Query é uma ferramenta bastante espectacular! Poupar-lhe-á uma tonelada de tempo ao transformar os seus dados.
Power Query é apenas uma peça do conjunto de produtos Power BI (Business Intelligence) da Microsoft.
Se voltarmos à analogia de cozer cookies numa fábrica, pode pensar no Power Query como o primeiro passo na linha de montagem. Uma vez formados os cookies, precisamos então de os cozer (Tabelas Pivot, PowerPivot) e depois embalá-los para apresentação (Power View, Power Map, Gráficos, Painéis, etc.).)
Pode pensar no Excel como o edifício da fábrica que alberga todas estas ferramentas. A parte excitante é que existe um futuro muito brilhante para o Excel! Todos estes avanços na tecnologia vão ajudar-nos a dar sentido aos nossos dados de novas formas, poupar-nos tempo e impressionar os nossos chefes. 🙂
How Do I Get Power Query?
A outra parte agradável é que o Power Query está agora integrado no Excel começando com o Excel 2016 para Windows. Se estiver no Excel 2010 ou 2013 então o Power Query é um add-in.
Eu tenho uma página dedicada que o ajudará a determinar se tem a versão certa do Excel para obter o Power Query. Também fornece instruções completas de instalação e o link para download.
Guia Completa para Instalar o Power Query
Para lhe dar uma ideia da importância desta ferramenta, O Power Query foi totalmente integrado no Excel no Excel 2016 para Windows, e está no separador Dados da Fita.
É também conhecido como Get & Transform, embora o termo Power Query seja mais comum.
Recursos adicionais
Este artigo forneceu uma visão geral dos fundamentos do Power Query que deverá ajudá-lo a compreender algumas das principais características. O Power Query tem uma tonelada de características e há definitivamente muito a aprender.
Partilharei mais artigos e vídeos de como fazer nas próximas semanas. Aqui estão alguns recursos que o ajudarão a começar.
Como desfazer os seus dados com o Power Query + Vídeo Tutorial
Webinario de Formação Gratuito sobre as Ferramentas Eléctricas
Direito agora estou a realizar um webinario de formação gratuito sobre todas as Ferramentas Eléctricas em Excel. Isto inclui Power Query, Power Pivot, Power BI, tabelas pivot, macros & VBA, e mais.
Chama-se The Modern Excel Blueprint. Durante o webinar explico o que são estas ferramentas e como podem caber no seu fluxo de trabalho.
Voçê também aprenderá como se tornar o Herói Excel da sua organização, aquela rapariga ou homem em quem todos confiam para a ajuda e projectos divertidos do Excel.
O webinar está a decorrer em vários dias e horas. Por favor clique no link abaixo para se registar e guardar o seu lugar.
Click Here to Register for the Free Webinar