Prévia do material em texto
POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados TREINAMENTO POWER QUERY MÓDULO BÁSICO 30/05/2017 2 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Daniel Delgado, Sócio Fundador Administrador de Empresas Microsoft Excel Specialist daniel.delgado@doutoresdoexcel.com.br Weslley Alencar, Consultor Cientista da Computação Microsoft Excel Specialist weslley@doutoresdoexcel.com.br 30/05/2017 3 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados TREINAMENTOS IN-COMPANY CURSOS ONLINE CONSULTORIA 30/05/2017 4 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados LINKS PARA BAIXAR O POWER QUERY • EXCEL 2010 e 2013: • “power query download” (Google) • https://www.microsoft.com/pt-BR/download/details.aspx?id=39379 https://www.microsoft.com/pt-BR/download/details.aspx?id=39379 30/05/2017 5 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados ARQUIVO INICIAL • ABRIR ARQUIVO “01 A) INÍCIO.XLSX” POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados POWER QUERY Excel 2016 Excel 2010 / 2013 DOWNLOAD: https://www.microsoft.com/pt-br/download/details.aspx?id=39379 Ferramenta nativa. Não é necessário download adicional 30/05/2017 7 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Introdução • Ferramenta para conectar, corrigir e importar qualquer tipo de dado de qualquer origem para dentro do Excel. Por exemplo: • Tabelas do Excel • Outros Arquivos do Excel • Listas de Sharepoint • TXT, CSV • Access • Servidores (SQL, MySQL, entre outros) 30/05/2017 8 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Introdução • Exemplo 1: Facilidade na elaboração de uma validação de dados com dados únicos, através de uma tabela. 30/05/2017 9 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Power Query – Retirando Duplicidades 30/05/2017 10 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Introdução • Problema: Lista de carros sem duplicidade • Solução 1: Tabela Dinâmica. Problema: Aumento da lista com novos carros • Solução 2: PowerQuery. Solução: Nomes em uma nova tabela + Criação de nome na nova tabela • Validação de dados: • Adicionar o nome da tabela resultante do PowerQuery na validação de dados: • Listas • =“TbVendasCarro_2” POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados POWER QUERY Abrir arquivo: 02 A) POWER QUERY 30/05/2017 12 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo x Tabela Intervalo Tabela Arquivo “1. Importando Intervalo.xlsx” Arquivo “2. Importando Tabela.xlsx” 30/05/2017 13 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo 2010 / 2013 2016 30/05/2017 14 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo Arquivo “1. Importando Intervalo.xlsx” 30/05/2017 15 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo 30/05/2017 16 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo 1. Remover a primeira coluna 2. Remover as 3 primeiras linhas OU “Remover Vazio” na coluna Dia através do filtro da coluna 3. Promover Cabeçalhos 4. Alterar Tipos de Dados das Colunas 5. Mostrar e editar as Etapas 30/05/2017 17 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – Intervalo 4. Alterar Tipos de Dados das Colunas Importante para o PowerPivot 30/05/2017 18 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados 5. Mostrar e editar as Etapas • Altear o Nome da Consulta • Engrenagens • Renomear Etapas PowerQuery – Intervalo 30/05/2017 19 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados 6. Fechar e Carregar • Fechar e Carregar: Vai para o Excel • Fechar e Carregar Para...: Escolhe a planilha que quer importar OU; Vai para Modelo de dados (PowerPivot) PowerQuery – Intervalo 30/05/2017 20 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Carregar Para: • Excel abre uma nova planilha com o resultado da consulta • Abre o Painel de Consultas • 2010/2013 PowerQuery – Intervalo 2016 30/05/2017 21 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Fechar e carregar Para: • Excel abre uma nova planilha com o resultado da consulta PowerQuery – Intervalo 30/05/2017 22 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Editar Consulta PowerQuery – Intervalo 30/05/2017 23 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases Objetivo: Unificar bases de locais diferentes em uma base única • Importar Dados do Rio de Janeiro (CSV) • Importar Dados de São Paulo e Tratar • Tratar a base São Paulo • Unificar as Consultas • Importar Tabela de Preços dos Produtos • Relacionar a consulta unificada com Preços (PROCV no PowerQuery) 30/05/2017 24 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Importar Dados do Rio de Janeiro (CSV) 2010 2016 Arquivo: “Vendas Rio de Janeiro.CSV” 30/05/2017 25 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Importar Dados do Rio de Janeiro (CSV) 30/05/2017 26 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Importar Dados de São Paulo e Tratar OBS: Nova importação dentro do próprio Power Query Arquivo: “Vendas São Paulo.xlsx” 30/05/2017 27 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Importar a TabVendas_SP 30/05/2017 28 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Renomear a consulta para “Vendas São Paulo” • Trata data de Venda: • Selecionar as colunas Dia, Mês e Ano • Botão direito Mesclar Colunas • Separador Personalizado / • Nome da nova coluna: Data da Venda • Alterar o Tipo para “Data” • Opcional: Transformar Data Analisar 30/05/2017 29 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Separar Loja e Produto • Transformar Dividir Colunas Por Delimitador • Separar Loja e Produto • Personalizado = “-” • Em cada ocorrência do delimitador 30/05/2017 30 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Renomear as Colunas para Loja e Produto Duplo clique no nome da coluna • Criar uma Coluna Personalizada “Estado” = São Paulo 30/05/2017 31 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Criar uma Coluna Personalizada “Estado” = São Paulo 30/05/2017 32 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Na consulta do “Rio de Janeiro”, também criar uma Coluna Personalizada estado. 30/05/2017 33 POWER QUERY MÓDULO BÁSICO Doutores do Excel –www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Tornar uma das consultas como Referência (copiar a consulta) 30/05/2017 34 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery – União de Bases • Renomear para: Vendas – Todos os Estados • Acrescentar Consultas: Página Inicial Acrescentar Consultas • Como tornamos a consulta de São Paulo como referência, então acrescentar os dados do Rio de Janeiro 30/05/2017 35 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Relacionar a consulta unificada com Preços (PROCV no PowerQuery) • Importar Produtos • Relacionar com a Tabela Vendas Todos os Estados • Na tabela “Vendas Todos os Estados”, selecionar Mesclar Consultas 30/05/2017 36 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Selecionar a Coluna “Produtos” • Selecionar a tabela “Produtos” • Selecionar a mesma coluna que corresponde a primeira coluna selecionada • Clicar em “OK” 30/05/2017 37 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Clicar na opção para expandir • Selecionar a coluna que deseja puxar da tabela Produtos. Neste caso, queremos puxar o “Valor” • Alterar o tipo para “Moeda” • Alterar o nome da coluna para “Valor” • Carregar para o Excel 30/05/2017 38 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Abrir arquivo em branco • Do arquivo Da Pasta • Pasta: “arquivos/arquivos de uma pasta” • Clicar em “Editar” 30/05/2017 39 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Arquivos de Texto • Versão 2010 – Atualização Março 2017 • Csv.Document([Content],[Delimiter="#(tab)",Encoding=1252]) 30/05/2017 40 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Verificar se não precisa filtrar pela extensão do arquivo, visto que podem haver outros arquivos na pasta • Excluir as colunas que não precisamos. • Manter coluna Nome do arquivo “Name” • Manter coluna “Content” 30/05/2017 41 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Dica: Selecionar as colunas que deseja manter em seguida, botão direito Remover outras Colunas 30/05/2017 42 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • O mais intuitivo seria clicar na coluna “Content” e pedir para o PowerQuery expandir o conteúdo e nós selecionaríamos o que queremos. Porém, ao fazer isso, o PowerQuery apresenta um erro. Veja: 30/05/2017 43 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Portanto, siga os passos abaixo para funcionar! 1) Clique em “Adicionar coluna Personalizada” 30/05/2017 44 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Portanto, siga os passos abaixo para funcionar! 2) No campo “Fórmula da coluna”, escreva: =Excel.Workbook( • Atenção: Utilize as primeiras maiúsculas sempre na escrita das fórmulas em linguagem “M”! • http://www.devmedia.com.br/power-query-formula-language-introducao/31295 • https://msdn.microsoft.com/en-us/library/mt211003.aspx http://www.devmedia.com.br/power-query-formula-language-introducao/31295 https://msdn.microsoft.com/en-us/library/mt211003.aspx 30/05/2017 45 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Observe que no quadro direito, há uma opção de “nome” e outra “Content” • Após abrir parênteses da fórmula Excel.Workbook, dê um duplo clique na palavra “Content” e veja que a fórmula irá ficar assim: 30/05/2017 46 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Agora, basta fechar o parênteses da fórmula e clicar em “OK” • Verifique se não foi encontrada nenhum erro. 30/05/2017 47 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados PowerQuery Importando arquivos de uma pasta • Pronto! Agora podemos prosseguir e puxar as informações que estão dentro do arquivo • Clique em expandir a nova coluna • No próximo quadro, selecione as opções: • Name: Nome da planilha ou tabela • Data: Dados das tabela (é daqui que vamos puxar os dados) • Kind: indica se é uma planilha ou uma tabela 30/05/2017 48 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • A coluna “Content” agora pode ser Removida • Filtre “Table” na coluna “Kind” indicando que, neste caso, vamos pegar somente os objetos “Tabela” dos arquivos que serão importados PowerQuery Importando arquivos de uma pasta 30/05/2017 49 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Agora estamos prontos para puxar os dados propriamente. Na coluna. Expanda a coluna “DATA” PowerQuery Importando arquivos de uma pasta • Observe que ele irá exibir todas as colunas que possui na planilha. • Desmarque a opção “use o nome da coluna original” para puxar somente o nome das colunas como vem no arquivo original. 30/05/2017 50 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Pronto! Seus dados foram importados para o PowerQuery! • Vamos agora remover as colunas que não nos interessa, como a “Kind” e “Name” PowerQuery Importando arquivos de uma pasta 30/05/2017 51 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Agora, vamos montar uma data através do nome do arquivo, visto que, neste caso, ele está padronizado, trazendo o mês e o ano como parte do nome do arquivo PowerQuery Importando arquivos de uma pasta 30/05/2017 52 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Agora, vamos montar uma data através do nome do arquivo, visto que, neste caso, ele está padronizado, trazendo o mês e o ano como parte do nome do arquivo • OBS: Existem vários jeitos de fazer esta etapa. É utilizando a criatividade que você consegue utilizar o PowerQuery PowerQuery Importando arquivos de uma pasta 30/05/2017 53 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Primeiro, vamos dividir a coluna “Name” por delimitador “_”, uma vez que é o underline que separa a informação mês e ano PowerQuery Importando arquivos de uma pasta 1 2 30/05/2017 54 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Na primeira coluna, vamos substituir a palavra “VENDAS “ (COM ESPAÇO NO FINAL) por NADA PowerQuery Importando arquivos de uma pasta 30/05/2017 55 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Na segunda coluna, vamos substituir “.xlsx” por NADA PowerQuery Importando arquivos de uma pasta 30/05/2017 56 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • Agora, vamos montar uma data com as duas colunas que corrigimos 1) Altere o tipo de da das colunas onde estão o Mês e Ano para “número inteiro” 2) Clique “Adicionar Coluna Personalizada” 3) Indique o nome da coluna como “DATA DO ARQUIVO” PowerQuery Importando arquivos de uma pasta 30/05/2017 57 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • A fórmulapara criar a coluna da data na nova coluna será: • #date(ano,mês,dia) • Com base no quadro da direita, basta selecionar as colunas que possuem as informações que a fórmula “#date” precisa para funcionar e separando-as com vírgula. PowerQuery Importando arquivos de uma pasta 30/05/2017 58 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados • A fórmula final irá ficar: #date([Name.2],[Name.1],1) • Após clicar em “OK” a nova coluna “DATA DO ARQUIVO” será criada • As colunas com o mês e ano que vieram do nome do arquivo pode ser REMOVIDAS • Antes de Fechar e Carregar, altere o tipo de informação que há em cada uma das colunas do arquivo PowerQuery Importando arquivos de uma pasta 30/05/2017 59 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery • Tratar e Importar os dados do arquivo • “EXERCÍCIO – POWERQUERY” • Dicas: Dividir a Importação em 2 Consultas • Ao término do tratamento, adicionar uma consulta dentro da outra 30/05/2017 60 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção OBS: Não é a única resposta / caminho para solução. Após analise, verifica-se que existem 2 tipos de dados em uma mesma base: • Data do Relatório / Unidade • Dados das Vendas Portanto, nestes casos, trate um dado de cada vez. Após finalizar um, faça uma cópia da consulta e inicie o processo para tratar a segunda parte dos dados. 30/05/2017 61 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção TRATAR DATA e UNIDADE 1) Manter somente as 2 primeiras linhas. Página Inicial Manter Linhas Manter Primeiras Linhas 30/05/2017 62 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção TRATAR DATA e UNIDADE 2) Promover Cabeçalhos 3) Remover outras colunas 4) Alterar o Tipo de dado de cada coluna 5) Renomear consulta para “UNIDADE” 30/05/2017 63 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção TRATAR VENDAS 1) Copiar a consulta “UNIDADE” e recomeçar 2) Remover as 4 primeiras linhas Página Inicial Remover Linhas Remover Linhas Principais 30/05/2017 64 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção TRATAR VENDAS 2) Promover Cabeçalhos 3) A partir da coluna “DATA”: • Filtrar tudo que for diferente de null • Filtrar tudo que for diferente de ---------- • Filtrar tudo que for diferente de “DATA” (para retirar os demais cabeçalhos) 30/05/2017 65 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção TRATAR VENDAS 4) Preencher os nomes dos vendedores que estão vazios. • Selecionar a coluna VENDEDOR • Em seguida, utilizar o recurso “Preenchimento para Baixo” 30/05/2017 66 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção NA CONSULTA “VENDAS“ ACRESENTAR A CONSULTA Na consulta VENDAS” Página inicial Acrescentar Consultas 30/05/2017 67 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção NA CONSULTA “VENDAS“ ACRESENTAR A CONSULTA • Observe que a data e a unidade estarão na última linha do relatório. • Portanto, utilize a opção Preenchimento para cima nas duas novas colunas 30/05/2017 68 POWER QUERY MÓDULO BÁSICO Doutores do Excel – www.doutoresdoexcel.com.br Direitos reservados Exercício PowerQuery - Correção Finalização! Agora, é só salvar e enviar para!