Power Query: Como poupar Centenas de Horas Automatizando Planilhas do Excel?

Você perde inúmeras horas elaborando relatórios e planilhas no Excel todo mês? E no mês seguinte tem que fazer tudo de novo?

Eu já passei por isso durante muitos anos…

Eu perdia literalmente centenas de horas anualmente em processos manuais de atualização de planilhas.

Até o dia em que eu descobri uma das ferramentas mais fantásticas do Excel: O Power Query!

Nesse artigo eu vou lhe mostrar como poupar seu valioso tempo utilizando o Power Query para automatizar a atualização de Planilhas do Excel.

E isso sem o uso de fórmulas, programação (VBA), SQL, nada disso!

Nem é necessário muita experiência com Excel, já que como você vai ver, todo o processo poderá ser feito por meio de uma interface super fácil e intuitiva.

Esse tutorial têm o objetivo de revolucionar o modo em que você trabalha com o Excel!

Você vai aprender a:

• Importar arquivos dos mais diferentes formatos;
• Manipular os dados desses arquivos, de modo que eles fiquem no layout que você deseja;
• Atualizar os dados a qualquer momento, com apenas um clique;
• Configurar a atualização automática, para que a planilha seja atualizada com uma frequência pré-determinada;

O melhor de tudo é que qualquer pessoa poderá criar essas automações de uma forma fácil, sem ter que recorrer à utilização de macros!

E para simular um cenário bastante comum, iremos agregar vendas de diversos canais, no intuito de criar um relatório consolidado com as vendas de todos os sistemas de controle da empresa (ERP, plataforma de e-commerce, marketplaces, PDV e controles manuais).

Em um próximo artigo, utilizaremos essa planilha para criar um relatório de segmentação de clientes para exportação para o Google Adwords e Facebook Ads, de modo que você possa criar campanhas específicas ou definir ajustes de lances para:

• Todos os compradores
• Clientes com maiores compras (Curva A)
• Compradores recorrentes
• Clientes que compraram um produto da categoria A
• Clientes que compraram um produto da categoria A mas não comprou a categoria B

Com base no que você aprender, você poderá criar qualquer outra segmentação que preferir, de forma fácil e rápida, a imaginação será o limite. 🙂

Esse artigo será apenas uma pequena introdução para você ao mundo do Self-Service BI.

Em futuras publicações eu irei lhe ensinar de forma didática como utilizar ferramentas como o Power BI, para manipular grandes quantidades de dados desordenados (Big Data) oriundos de todos os seus sistemas, integrando-os e correlacionando-os, de modo que você possa obter preciosos insights sobre o seu negócio!

O aprendizado do conteúdo desse guia será fundamental para sua compreensão dos artigos posteriores. Portanto, leia com atenção e se tiver dúvidas, deixe-a nos comentários, eu certamente irei ajudá-lo 🙂

Sumário:

• O Excel como Ponto de Encontro de Todos os seus Dados
• Importação e Manipulação dos Dados (ETL)
• Power Query: A porta de entrada no mundo do Self Service BI
• Instalação do Power Query
• Atualização do Software
• Tutorial de Como usar o Power Query
  • 1º Etapa: Como criar uma conexão com os arquivos e importar os dados?
    • • Importação de Arquivo TXT/CSV
      • Importação de Arquivo XML
      • Importação de Dados de um Website
  • 2º Etapa: Como manipular os dados e deixa-lo no formato desejado?
• Como Atualizar Automaticamente as Planilhas do Excel
  • Atualização Manual
  • Atualização ao abrir a planilha
  • Atualização a cada intervalo pré-determinado
  • Atualização programada através do Power Update
• Conclusão

O Excel como Ponto de Encontro de Todos os seus Dados

O Excel é sem dúvidas um dos softwares mais versáteis e abrangentes, sendo utilizado por organizações de todos os tamanhos.

É muito comum as empresas (sobretudo as de e-commerce) possuírem dezenas de sistemas diferentes, como ERPs, WMS, CRMs, plataformas de e-commerce, gateways, e por aí vai…

Mas na grande maioria das vezes, por falta de integração entre os softwares, todos esses dados acabam convergindo em várias planilhas do Excel, sendo utilizadas para elaboração dos mais diversos relatórios, como DREs, Fluxos de Caixa, Planilhas de Precificação, etc.

Aí é que surge o grande problema: Como manter os dados atualizados em todas essas planilhas?

Normalmente o processo de atualização é uma rotina que exige uma grande quantidade de esforço manual, que envolve a exportação dos dados de cada um desses sistemas e a sua importação nas planilhas (normalmente por meio de CTRL + C e CTRL + V), tarefas que são totalmente passíveis à erros.

Assim, perde-se tempo e confiabilidade, pois uma breve distração na execução da rotina, pode fazer com que parte dos dados não sejam importados nas planilhas, comprometendo toda a análise que seria feita com base nessas informações.

Importação e Manipulação dos Dados (ETL)

Outro problema recorrente ao trabalhar com dados de inúmeros sistemas é integra-los de maneira consistente, tendo em vista que cada sistema possui um layout específico e um formato de exportação diferente, como por exemplo:

• CSV
• CSV
• TXT
• XML
• JSON
• XLS
• XLSX

O caractere que separa os campos também pode ser diferente em cada arquivo de exportação. Sendo os mais comuns:

• Tabulação
• Vírgula
• Ponto e Vírgula
• Espaço

Os valores também podem vir com padrões diferentes em cada arquivo:

• Ponto como separador de milhar
• Vírgula como separador de milhar
• Data no formato DD/MM/AAAA
• Data no formato AAAA/MM/DD
• Data no formato MM/DD/AAAA

E da mesma forma, os arquivos podem possuir dados com granularidades diferentes. Exemplos:

• Vendas Diárias
• Gastos com Publicidade Mensais
• Reembolsos Quinzenais

Com todas essas possibilidades, fica difícil imaginar uma maneira de juntar todos esses dados em uma mesma planilha de forma rápida, fácil e automatizada.

Mas ela existe!

Power Query: A porta de entrada no mundo do Self Service BI

O Power Query é uma ferramenta fantástica de extração, transformação e carregamento (ETL), que através de uma interface extremamente intuitiva, permite importar de forma fácil dados das mais variadas fontes e com os mais distintos formatos.

E o mais fascinante dessa ferramenta é que ele salva todos os passos que você utilizou para manipular os dados e importa-los no Excel, permitindo assim, que você faça novas importações no futuro com apenas um clique!

A ferramenta foi lançada em 2013, sendo que até então não existia uma solução simples a disposição do público para a manipulação de grande quantidade de dados.

Para os usuários de Excel, as opções que existiam eram complexas e exigiam um grande conhecimento técnico, pois era necessário:

• O uso de uma combinação de Funções como PROCV (VLOOKUP), ÍNDICE (INDEX), CORRESP (MATCH), DESLOC (OFFSET), ESQUERDA (LEFT), DIREITA (RIGHT), NÚM.CARACT (LEN), ARRUMAR (TRIM), TIRAR (CLEAN)
  ou
• Executar a rotina via Programação, usando Visual Basic (VBA) e/ou SQL

O Power Query foi lançado para endereçar exatamente esse problema: ser uma maneira rápida e simples para qualquer pessoa manipular seus dados, sem a necessidade de conhecimentos avançados.

Em um mundo em que o volume de dados cresce exponencialmente e o Big Data é a bola da vez, o Power Query exerce um papel essencial, empoderando o usuário comum, de modo que qualquer pessoa possa manipular uma grande quantidade de dados e extrair informações relevantes.

As automações e os sistemas de self-service BI serão temas recorrentes nesse blog, sobretudo ao que tange ao Microsoft Power BI. E, visando introduzir esses temas da forma mais simples possível, eu optei por começar abordando o Power Query.

Aprender o Power Query é o primeiro passo para a elaboração de soluções de Self-service BI nas plataformas da Microsoft, tendo em vista que ele está presente tanto no Excel como no Power BI, ou seja, tudo que você aprender aqui servirá para ambas as plataformas 🙂

Além disso, apesar do Power Query ser uma ferramenta extremamente poderosa, ele possui uma versatilidade enorme e pode ser usado facilmente através da sua simples interface.

Claro que é possível fazer muito mais ao dominar a sua linguagem interna (M), a qual eu irei endereçar alguns artigos específicos (e mais técnicos) no futuro, mas essa não é uma exigência para se fazer coisas incríveis com o Power Query.

Instalação do Power Query

• Excel 2010/2013
• Excel 2016

Instalação do Power Query no Excel 2010/2013

O Microsoft Power Query não é uma ferramenta nova, ela já existe desde o Excel 2013 e possui retrocompatibilidade com o Excel 2010.

Porém, nessas duas versões do pacote Office (2010 e 2013) ele era disponibilizado como um suplemento gratuito que precisava ser baixado separadamente e, por conta disso, pouquíssimas pessoas o conheciam.

Se você ainda utiliza o Excel 2010 ou 2013 e não tem o Power Query, baixe-o agora no site da Microsoft.

Haverá duas opções para download: 32 bits e 64 bits.

Você deverá verificar qual a versão do seu excel, para isso, você deve clicar no Menu “Arquivo” no canto superior esquerdo, ir em “Conta” e depois em “Sobre o Excel”:

Irá abrir uma tela exibindo o código da versão do Excel, nela estará indicando se a sua instalação é 32 ou 64 bits.

Agora que você já sabe qual a sua versão, baixe o instalador adequado e siga as instruções, o processo de instalação é bem simples.

Após instalado você deverá habilita-lo, para isso você deve ir novamente no menu “Arquivo” e clicar em “Opções”.

Em seguida clique em “Suplementos”, selecione a opção “Suplementos COM” e clique em “Ir”, na tela que abrir habilite a opção “Microsoft Power Query para Excel”.

Após habilitado, você verá uma nova aba denominada “POWER QUERY”.

Instalação do Power Query no Excel 2016

A partir do Excel 2016 o Power Query começou a vir nativo e habilitado no software (não exigindo nenhuma instalação adicional).

Ele está disponível na aba “Dados” agora com um novo nome: Obter e Transformar Dados.

Nas últimas versões do Excel 2016 a Microsoft removeu as antigas opções de importação de dados, deixando apenas o Power Query (sob o nome Obter e Transformar). Porém em versões mais antigas ainda há essas opções disponíveis.

Por conta disso, se o seu Excel não estiver atualizado, sua tela provavelmente será assim:

Se  esse for o seu caso, não precisa se preocupar pois você conseguirá usar o Power Query sem problemas, apenas fique atento para não tentar usar por engano as opções do grupo “Obter Dados Externos”, pois essas são as opções antigas que não usam a engine do Power Query.

Atualizações do Software

Diferente do que estamos acostumados com os softwares da Microsoft, o Power Query (assim como o Power BI) tem recebido atualizações mensais, com novos recursos e otimizações.

Eu tenho acompanhando constantemente todos os novos recursos e devo dizer que a Microsft está fazendo um trabalho excelente em aperfeiçoar cada vez mais a ferramenta.

Caso o seu Power Query não tenha algum recurso apresentado nesse ou em outro artigo, poderá ser necessário atualiza-lo para a última versão.

Tutorial de Como usar o Power Query

1º Etapa: Conexão

Por meio da sua interface, o Power Query permite a conexão com uma infinidade de fontes de dados, entre elas:

• Banco de dados (SQL, Mysql, etc)
• Serviços on-line (Facebook, Sharepoint, etc)
• Arquivos Locais (TXT, XML, CSV, etc)
• Arquivos Web (Sites e outros arquivos na nuvem)

Você pode até mesmo importar para o Excel/Power BI pastas inteiras contendo diferentes tipos de arquivos (cenário que será tópico de um futuro artigo)!

E através da sua linguagem (M) também é possível configurar conexões com APIs de praticamente qualquer sistema, expandindo enormemente o leque de conectores.

Como esse é um artigo introdutório, eu irei focar apenas nos conectores de arquivos locais e da web, pois são os mais usados.

Iremos simular um cenário hipotético, no qual uma empresa possui vários canais de vendas (cada qual com um sistema diferente) e deseja juntar os dados de todos esses sistemas para gerar um relatório consolidado de vendas.

Normalmente cada sistema tem um formato de exportação de dados diferente, gerando arquivos com formatos e nomes de campos próprios.

Por conta disso, em nosso exemplo cada arquivo terá uma extensão diferente e os dados estarão estruturados de maneira distinta.

• • Plataforma de E-commerce: Arquivo CSV com campos separados por vírgulas;
  • Sistema do Marketplace: Arquivo TXT com campos separados por tabulações
  • Sistema ERP: Arquivo XML com campo de data no formato americano: AAAA-MM-DD
  • Controle Manual Televendas: Arquivo CSV gerado à  partir da opção de compartilhamento público de uma planilha do Google Planilhas

Assim, nossa primeira tarefa será padronizar o conteúdo desses arquivos, para em seguida junta-los, criando uma única tabela com a relação de todas as vendas.

Essa tabela final terá uma conexão direta com os arquivos de origem, permitindo importar os dados atualizados a qualquer momento, de forma manual (com apenas um clique) ou automática.

Baixe aqui os arquivos de exemplo para seguir os passos desse tutorial.

Vale ressaltar que os dados dos arquivos de exemplo são fictícios, eles foram gerados à partir do banco de dados AdventureWorks2008R2, que é disponibilizado gratuitamente pela Microsoft para ser usado em testes e treinamentos, fins para os quais estamos utilizando-o.

Importação de Arquivo TXT/CSV

O processo de importação de arquivos TXT e CSV é exatamente o mesmo (tanto é que você pode mudar a extensão de um arquivo TXT para CSV e vice-versa, ainda assim o processo irá funcionar do mesmo jeito).

Mas para exemplificação de alguns cenários de importação mais simples iremos fazer a importação de um arquivo CSV e de um arquivo TXT (cenários mais complexos serão tema de futuros artigos), o primeiro separado por vírgulas e o segundo separado por tabulações (embora seria possível tratar qualquer outro tipo de separador, como vocês verão mais adiante).

Nesse exemplo hipotético, nós estamos considerando um sistema de e-commerce que permite exportar as vendas em formato CSV:

Nome do Arquivo: Vendas.csv
Origem: Plataforma de E-commerce
Tipo de Arquivo Exportado: CSV
Particularidade: Campos separados por vírgulas

Conforme a sua versão do Excel, o local para importar o arquivo irá mudar:

• EXCEL 2016 (Últimas versões): Acesse a aba “Dados” e no grupo de opções “Obter e transformar” clique em “De text/CSV”.

  

• EXCEL 2016 (Versões antigas): Acesse a aba “Dados” e no grupo de opções “Obter e transformar” clique em “Nova Consulta”, vá em “Do arquivo” e em seguida clique em “Do CSV”

• EXCEL 2010/2013: Acesse a aba “POWER QUERY”, no grupo de opções “Obter Dados Externos” clique em “Do Arquivo” e em seguida em “Do CSV”.

Em seguida localize o diretório em que estão os arquivos de exemplo e selecione “Vendas.csv“.

Caso as extensões dos arquivos estejam ocultas, na tela para seleção de arquivos consulte a coluna “Tipo”, o arquivo estará classificado como “Arquivo de valores separados por Vírgulas do Microsoft Excel”.

E eis que o Power Query começa a fazer a sua mágica!!!

Observe que ele já analisou a estrutura do arquivo e reconheceu automaticamente o delimitador (ponto e vírgula).

Também já foram reconhecidos os nomes das colunas e os seus respectivos tipos (“CodigoPedido” como campo numérico, “Data” no formato “DD/MM/AAAA”, etc).

Apesar de já estar tudo correto com os campos, vamos editar essa consulta para renomeá-la, de modo a facilitar sua identificação no futuro.

Clique em Editar:

Conheça então a interface de edição de consultas do Power Query:

Quando você descobrir tudo que é possível fazer nessa tela e quantas horas da sua vida ela pode lhe poupar, eu garanto que você vai se apaixonar por ela 🙂

Mais a frente nesse artigo vou lhes mostrar algumas funções do Power Query que podem ser executadas nessa tela (serão alterações simples, pois irei me aprofundar em todos esses recursos somente em futuras publicações).

De todo modo, por enquanto apenas renomeie a consulta para “Plataforma E-commerce”:

E clique em “Fechar e Carregar”:

Logo em seguida será criada uma nova planilha com o conteúdo do arquivo CSV. Você irá notar que apareceu uma nova interface à direita:

Embora tenha outras formas de fazer a atualização (que irei abordar no final do artigo), através dessa coluna você pode clicar à qualquer momento com o botão direito em cima da consulta e ir em “Atualizar”.

Dessa forma, quando você gerar um novo relatório de vendas da sua plataforma, basta sobrescrever o arquivo “vendas.csv” na pasta que você havia importado no Power Query, abrir o Excel e clicar em “Atualizar” na consulta. Super simples 🙂

Mas calma, é possível fazer muiiiito mais coisa com o Power Query. Esse cenário foi simples de propósito, apenas para você ver como é rápido e fácil começar a usar a ferramenta 🙂

Agora vamos importar o arquivo TXT separado por tabulações:

Nome do Arquivo: Vendas.txt
Origem: Marketplace
Tipo de Arquivo: TXT
Particularidade: Campos separados por tabulações

• EXCEL 2016 (Últimas versões): Nessa versão do Excel, o caminho é o mesmo da importação do arquivo CSV:

  

• EXCEL 2016 (Versões antigas): Acesse a aba “Dados” e no grupo de opções “Obter e transformar” clique em “Nova Consulta”, vá em “Do arquivo” e em seguida clique em “Do Texto”

  

• EXCEL 2010/2013: Acesse a aba “POWER QUERY”, no grupo de opções “Obter Dados Externos” clique em “Do Arquivo” e em seguida em “Do TXT”.

  

Em seguida localize o diretório em que estão os arquivos de exemplo e selecione “Vendas.txt“.

Caso as extensões dos arquivos estejam ocultas, verifique a coluna tipo, o arquivo estará classificado como “Arquivo de Texto”.

Novamente o Power Query consegue identificar automaticamente o caractere delimitador e toda a estrutura do arquivo. Porém nesse caso também vamos clicar em editar para renomeá-lo.

Configure o nome da consulta como “Marketplace” e clique em “Fechar e Carregar”:

Importação de Arquivo XML

Um arquivo XML pode conter estruturas bem complexas com várias hierarquias de dados, o que pode exigir algumas transformações para que os dados sejam importados de forma coerente para o Excel.

No nosso exemplo de hoje não serão necessárias essas transformações, portanto o processo será bem semelhante ao da importação dos arquivos txt/csv:

Nome do Arquivo: Vendas.xml
Origem: Sistema ERP
Tipo de Arquivo: XML
Particularidade: Campo de data no formato americano: AAAA-MM-DD

• EXCEL 2016 (Últimas versões): Acesse a aba “Dados”, vá em “Obter Dados”, clique em “De arquivo” e em seguida em “Do XML”:

• EXCEL 2016 (Versões antigas): Acesse a aba “Dados” e no grupo de opções “Obter e transformar” clique em “Nova Consulta”, vá em “Do arquivo” e em seguida clique em “Do XML”:

• EXCEL 2010/2013: Acesse a aba “POWER QUERY”, no grupo de opções “Obter Dados Externos” clique em “Do Arquivo” e em seguida em “Do TXT”:

Em seguida localize o diretório em que estão os arquivos de exemplo e selecione “Vendas.xml“.

Selecione o elemento “row” do XML e irá abrir a visualização do conteúdo do arquivo. Você poderá ver que o Power Query irá converter o formato do arquivo para o padrão do Excel, com os campos separados por colunas.

Assim como você fez nas etapas anteriores, clique em editar e renomeie o arquivo para: “Sistema ERP”.

Em seguida clique em “Fechar e carregar”.

Importação de dados de um Website

Uma das mais fantásticas e versáteis opções de importação do Power Query é a possibilidade de se conectar com sites e arquivos hospedados na Web. Assim, você consegue manter sua planilha sempre atualizada com informações que são alimentadas por sites de terceiros, como o histórico de cotações do dólar por exemplo.

As possibilidades dessa função são infinitas, com ela é possivel fazer “scraping” de páginas da web, extraindo dados em massa de diversas páginas.

Exemplo: você pode importar um arquivo sitemap.xml para fazer uma auditoria de SEO em lote, analisando as meta tags de todas as páginas simultanemanete. Esse arquivo ficaria conectado diretamente ao arquivo sitemap, lhe permitindo atualizar a planilha a qualquer momento, com um clique de um botão (ou deixar essa atualização automática, se preferir).

Além disso, essa importação não se limita a se conectar apenas com páginas da web, ela tem suporte a todos os formatos de arquivo abordados nos tópicos anteriores, então você consegue se conectar com qualquer arquivo disponível na Web (seja via FTP, um documento no Sharepoint, uma planilha compartilhada do Google Planilhas, etc).

Apesar de não ser tema desse post, vale comentar que é por meio desse método que é possível fazer conexão com APIs externas (em futuros posts irei entrar em mais delhalhes sobre esse assunto).

Em nosso exemplo, iremos importar um arquivo CSV gerado pelo Google Planilhas:

Arquivo: https://docs.google.com/spreadsheets/d/1dAlKGh58SEycK51iJeMrVIMukeboZneva4ETQMCuIZw/pub?gid=0&single=true&output=csv
Origem: Controle Manual Televendas
Tipo de Arquivo: CSV
Particularidade: Arquivo CSV gerado à  partir do compartilhamento público de uma planilha do Google Planilhas

O Google Planilhas tem um recurso bem interessante e que pouca gente conhece que é o compartilhamento on-line do conteúdo da planilha em formato CSV, para ativa-lo basta abrir a planilha, ir em:

E em seguida selecionar a opção de exportação em “valores separados por vírgulas (.csv)”:

Por fim ele irá lhe gerar uma URL que usaremos para conexão do Microsoft Excel ao Google Planilhas: https://docs.google.com/spreadsheets/d/1dAlKGh58SEycK51iJeMrVIMukeboZneva4ETQMCuIZw/pub?gid=0&single=true&output=csv

Agora abra o Microsoft Excel e vamos importar esse arquivo:

• EXCEL 2016 (Últimas versões): Acesse a aba “Dados” e vá em “Da Web”:

  

• EXCEL 2016 (Versões antigas): Acesse a aba “Dados” e no grupo de opções “Obter e transformar” clique em “Nova Consulta”, vá em “De Outras Fontes” e em seguida clique em “Da WEB”:

  

• EXCEL 2010/2013: Acesse a aba “POWER QUERY”, no grupo de opções “Obter Dados Externos” e clique em “Da Web”:

  

Agora informe a URL do CSV do Google e clique em “Ok”:

Irá abrir a tela de autenticação. Como esse arquivo não tem senha de acesso, basta manter em modo anônimo e clicar em “conectar”:

 

Após a conexão, siga exatamente o mesmo procedimento do exemplo de conexão ao arquivo CSV, porém no final renomeie a consulta para: “Controle Manual Televendas”.

Ao finalizar esta estapa você estará com as seguintes consultas configuradas:

2º Etapa: Como manipular os dados e deixa-lo no formato desejado?

Um dos grandes poderes do Power Query está em sua capacidade de transformação e manipulação de dados, de modo que após fazer a importação do arquivo de origem você pode modificar completamente a estrutura dos dados, mas sem perder a conexão com o arquivo original.

Como esse tutorial trata do processo de automação da atualização de planilhas, para não me alongar de mais eu deixarei para me aprofundar no processo de transformação de dados em um outro artigo, desse modo, iremos fazer apenas algumas transformações simples.

Nesse processo de transformação, primeiramente iremos inserir em cada consulta uma coluna com o nome do sistema que originou a venda. Essa coluna servirá de filtro para posteriormente identificamos a origem da venda.

Então, clique com o botão direito na consulta “Plataforma E-commerce” e clique em “Editar”:

E em seguida, vá na aba “Adicionar Colunas” e clique em “Coluna Personalizada”:

Defina o nome dessa coluna como “Origem”, e no conteúdo insira o texto =”Plataforma E-commerce”:

Faça o mesmo nas demais consultas, apenas alterando o conteúdo das colunas para:

• Marketplace
• Sistema ERP
• Controle Manual de Vendas

Agora nós precisaremos normalizar o nome das colunas em todas as consultas, para que na próxima etapa (quando formos juntar as consultas) os valores sejam agrupados corretamente.

Dessa forma, você precisará editar todos os nomes, removendo esse texto “Attribute:”. Para isso, basta dar dois cliques com o botão esquerdo no título da coluna e renomeá-la:

Faça isso em todas as colunas.

Agora na consulta “Controle Manual Televendas” você notará um outro problema: há duas colunas com caracteres especiais:

Isso acontece devido a codificação do arquivo estar em formato americano…

Você poderia simplesmente renomear essas colunas que já iria garantir a importação correta das colunas, porém dessa forma você provavelmente teria vários registros dentro dessas colunas com caracteres inválidos.

Portanto, a melhor solução é editarmos o tipo de codificação do arquivo.

Para isso, vá no primeiro passo de importação da consulta “Fonte” e clique no ícone de engrenagem no lado:

Irá abrir uma tela contendo informações do arquivo que está sendo importado, no campo “Origem do arquivo” altere a codificação para “65001: Unicode (UTF-8)”.

Pronto, agora os valores contendo acentos e cedilhas irão ser exibidos corretamente!

Porém… Você irá notar que o terceiro passo da consulta “Tipo alterado” está com um erro:

Isso acontece porque agora ele não está mais encontrando as colunas que estavam com o nome errado.

Nesse caso, basta excluir esse passo (clicando no “x” ao lado dele”):

E em seguida, pressione “CTRL + A” para selecionar todas as colunas, vá na aba “Transformar” e clique em “Detectar tipo de dados”.

É isso! Agora está tudo certo para juntarmos os arquivos.

Portanto vá na aba “Página inicial”, clique em “Acrescentar Consultas como Novas”:

Clique em “Três ou mais tabelas”, adicione todas as consultas e clique em “Ok”:

Por fim, renomeie essa nova consulta para “Vendas consolidadas” e clique em “Fechar e carregar”.

Pronto, agora você possui uma tabela do Excel consolidando os dados de vendas de quatro sistemas diferentes 🙂

E para atualizar manualmente as informações basta clicar no iconezinho ao lado da consulta:

Vê que incrível? Você perde alguns minutos configurando as consultas na primeira vez, porém depois disso você poderá gerar novamente esses relatórios quantas vezes você precisar, de forma extremamente ágil!

Antes de concluirmos essa etapa, vamos fazer um procedimento adicional: vá nas 4 consultas originais, clique com o botão direito nelas e selecione a opção “Carregar para…”:

Em seguida, selecione a opção “Apenas Criar Conexão”.

Faça isso em todas as 4 consultas:

Isso irá fazer com que apenas a consulta “Vendas Consolidadas” insira dados na sua planilha do Excel, deixando o arquivo mais leve e garantindo atualizações mais rápidas!

Como Atualizar Automaticamente as Planilhas do Excel

Atualização Manual

A primeira opção de atualização das consultas do Power Query é a manual, que pode ser feita individualmente (consulta por consulta) como já explicado nesse tutorial:

Ou em lote, atualizando todas as consultas de uma só vez:

Para as planilhas que você não tem necessidade de manter sempre atualizadas, essa opção já é mais que suficiente.

Atualização ao abrir a planilha

Se as suas consultas não forem muito pesadas, uma opção bem interessante é a de atualizar a planilha ao abri-la.

Para isso, clica com o botão direito em cima de uma das consultas, vá na opção “Propriedades” e habilite a opção “Atualizar dados ao abrir o arquivo”:

Essa opção de atualização é extremamente útil se você usar a planilha com pouca frequência e não precisa configurar atualizações em intervalos regulares.

Porém vale o destaque de que se as suas consultas forem muito pesadas, certamente seu Excel irá travar por alguns minutos sempre que você abrir a planilha (o que pode ser bastante frustrante no longo prazo).

Atualização a cada intervalo pré-determinado

Na mesma tela de propriedades exibida na opção acima, você irá ver uma outra opção denominada “Atualizar a cada X minutos”:

Essa opção pode lhe ser útil caso você mantenha a planilha aberta o dia inteiro e a sua fonte de dados está constantemente atualizando.

Atualização programada através do Power Update

Essa é a opção que eu uso e para mim é sem dúvidas a melhor opção para o agendamento das atualizações de planilhas.

Porém trata-se de um software pago com preço bem salgado: U$ 598,80 dólares…

Mas existe uma versão gratuita que lhe permite configurar a atualização automática de uma planilha sem nenhum ônus.

Você pode baixa-la nesse link (não é link de afiliado, não estou ganhando nada pela recomendação, hehe).

A instalação é bem simples, não exige nenhuma configuração adicional (exceto se você quiser receber notificações sobre o sucesso das atualizações via e-mail) e apesar de estar em inglês, toda a interface do aplicativo é bem simples e intuitiva:

Se você conseguir agrupar todas as suas consultas em uma única planilha, certamente a versão grátis deles já deve lhe servir (confesso que usei ela bastante tempo até finalmente ficar convencido em comprar a versão paga).

Vantagens do Power Update:

• Interface simples e intuitiva
• Alerta de Erros via E-mail
• Possibilidade de configurar retentativas caso dê falha em uma atualização
• Atualização de arquivos hospedados no Sharepoint
• Atualização e publicação de planilhas no Power BI
• Possibilidade de atualizar planilhas protegidas com senhas
• Atualização em lote de todas as planilhas de uma pasta (recurso exclusivo da versão paga)

Para agendar uma atualização automática, clique em “New”:

Defina o nome da tarefa:

Especifique qual a periodicidade da atualização (diária, semanal, mensal ou manual):

Informe o horário da atualização e de quantos em quantos dias/semanas/meses ela deverá ocorrer:

Caso você tenha selecionado a opção de atualização diária, você terá a opção de definir se essa atualização ocorrerá mais de uma vez por dia:

Em seguida informe qual será o local de destino do arquivo atualizado (computador local, Sharepoint, SSAS ou Power BI):

Na próxima etapa você poderá optar por atualizar apenas uma planilha ou uma pasta inteira (exclusivo da versão paga):

Então localize a planilha no seu computador:

E depois selecione o local onde o arquivo atualizado será salvo, caso você escolha a mesma pasta do arquivo de origem receberá um alerta de que essa opção irá sobrescrever o arquivo original (basta confirmar):

Em seguida você chegará na página de configurações avançadas. Nessa tela você poderá especificar se deseja rodar alguma macro antes ou depois da atualização, também poderá informar a senha da planilha (se houver) e o número de retentativas de atualização caso a operação falhe:

No exemplo acima, foi configurado para o aplicativo tentar atualizar 5x a planilha caso dê erro, com um intervalo de 60 segundos entre cada atualização.

Por fim, você poderá configurar o seu e-mail para receber alertas sobre as atualizações (essa configuração está fora do escopo desse artigo).

E é só isso! À partir de agora as atualizações irão ocorrer automaticamente em segundo plano nos horários agendados, sem a necessidade de você manter o Power Update aberto.

Obs: Apesar dessas atualizações rodarem em segundo plano, não é recomendável usar o Excel nos horários que elas rodam, pois ele pode travar, sugiro configurar o processo de atualização sempre em um horário que você não está usando o computador.

Atualização programada através do Power BI

É válido mencionar que também é possível programar a atualização das consultas do Power Query através do Power BI (e de forma gratuita), porém como esse tutorial é específico do Microsoft Excel, irei mostrar como fazer esse procedimento no Power BI em uma futura publicação.

Próximos passos: Segmentação da base de contatos

Agora que você já aprendeu a consolidar os seus pedidos de várias plataformas, vamos dar bom uso à essas informações!

Em um próximo artigo irei mostrar como usar esses dados para criar uma planilha de segmentação de contatos para o Google Adwords e Facebook Ads que permitirá exportar os:

• Todos os compradores
• Clientes com maiores compras (Curva A)
• Compradores recorrentes
• Clientes que compraram um produto da categoria A
• Clientes que compraram um produto da categoria A mas não comprou a categoria B

Esses públicos-alvo segmentados são fantásticos para você incluir ajustes de lances nas campanhas da rede de pesquisa/Google Shopping (investindo mais para os públicos que tem maior propensão de compra), para usar em campanhas para a rede de display e para criar públicos-alvo semelhantes (que também poderão ser usados na segmentação de campanhas e/ou ajuste de lances).

No Facebook Ads mesmo, esses públicos são ouro líquido 🙂

Conclusão

Automatizar as suas planilhas é um passo importante para melhorar a sua produtividade e/ou da sua equipe. Isso garante uma maior confiabilidade nos dados e permite que o tempo poupado seja gasto no que é mais importante: analisar os dados e tomar decisões estratégicas em cima deles.

Mas isso é apenas o início de uma longa jornada que teremos!

Irei produzir aqui no blog uma série de conteúdos ensinando-lhe a construir um sistema de self-service BI, de modo que você tenha todos seus dados consolidados em um único local (Power BI ou Excel), centralizando todas as suas informações e permitindo criar relatórios completos, com visualizações incríveis!

Esses relatórios irão lhe permitir visualizar todas as áreas do seu negócio com um nível de detalhe impressionante.

Convido você a assinar a minha newsletter para receber com exclusividade meus conteúdos. Garanto que essa jornada lhe será muito proveitosa 😉

O que mais vem por aí…

Nos próximos artigos eu vou lhes ensinar a fazer coisas incríveis com o Power Query, Excel, Google Planilhas e Power BI.

Veja alguns conteúdos que eu estou planejando:

• Relatórios automáticos conectados no Google Analytics, Adwords e Facebook Ads;
• Planilha com personalizados dinâmicos de anúncios do Google Adwords que extrai os preços dos produtos do site e insere automaticamente o preço atualizado nos anúncios;
• Planilha de cotações de fretes com integração com a API dos Correios;
• Dashboards com os principais KPIs de uma loja virtual;
• E muito mais!

E embora eu pretenda vir a explorar com vocês a linguagem M e Dax, a maior parte desses conteúdos não será técnico, podendo ser feito por qualquer pessoa, sem conhecimentos prévios.

Então não perca, assine a newsletter e fique atento às novidades!