Blog
Excel 2020: Limpe os dados com o Power Query
O Power Query é integrado às versões do Windows do Office 365, Excel 2016, Excel 2019 e está disponível para download gratuito nas versões do Windows do Excel 2010 e Excel 2013. A ferramenta é projetada para extrair, transformar e carregue dados no Excel de uma variedade de fontes. A melhor parte: o Power Query lembra seus passos e irá reproduzi-los quando você quiser atualizar os dados. Isso significa que você pode limpar os dados no dia 1 em 80% do tempo normal e pode limpar os dados nos dias 2 a 400 simplesmente clicando em Atualizar.
Eu digo isso sobre muitos dos novos recursos do Excel, mas este é realmente o melhor recurso para atingir o Excel em 20 anos.
Em meus seminários ao vivo, conto uma história sobre como o Power Query foi inventado como uma muleta para clientes do SQL Server Analysis Services que foram forçados a usar o Excel para acessar o Power Pivot. Mas o Power Query estava cada vez melhor, e todas as pessoas que usam o Excel deveriam dedicar um tempo para aprender o Power Query.
Obter Power Query
Você já pode ter o Power Query. Ele está no grupo Get & Transform na guia Data.
Mas se você estiver no Excel 2010 ou Excel 2013, vá para a Internet e pesquise Download Power Query. Seus comandos do Power Query aparecerão em uma guia dedicada do Power Query na faixa de opções.
Limpar dados pela primeira vez no Power Query
Para dar um exemplo de algumas das maravilhas do Power Query, digamos que você obtenha o arquivo mostrado abaixo todos os dias. A coluna A não está preenchida. Os trimestres vão para o outro lado em vez de para baixo na página.
Para começar, salve essa pasta de trabalho em seu disco rígido. Coloque-o em um local previsível com um nome que você usará para aquele arquivo todos os dias.
No Excel, selecione Obter dados, do arquivo, da pasta de trabalho.
Navegue até a pasta de trabalho. No painel de visualização, clique em Plan1. Em vez de clicar em Carregar, clique em Editar. Agora você vê a pasta de trabalho em uma grade ligeiramente diferente – a grade do Power Query.
Agora você precisa corrigir todas as células em branco na coluna A. Se você fosse fazer isso na interface de usuário do Excel, a sequência de comandos complicada é Home, Find & Select, Go To Special, Blanks, Equals, Up Arrow, Ctrl + Enter .
Em Power Query, selecione Transform, Fill, Down.
Todos os valores nulos são substituídos pelo valor de cima. Com o Power Query, são necessários três cliques em vez de sete.
Próximo problema: os trimestres estão atravessando em vez de diminuindo. No Excel, você pode corrigir isso com uma tabela dinâmica Multiple Consolidation Range. Isso requer 12 etapas e mais de 23 cliques.
No Power Query, selecione as duas colunas que não são quartos. Abra a lista suspensa Unpivot Columns na guia Transform e escolha Unpivot Other Columns, como mostrado abaixo.
Clique com o botão direito do mouse na coluna Atributo recém-criada e renomeie-a como Trimestre em vez de Atributo. Mais de vinte cliques no Excel se transformam em cinco cliques no Power Query.
Agora, para ser justo, nem todas as etapas de limpeza são mais curtas no Power Query do que no Excel. Remover uma coluna ainda significa clicar com o botão direito do mouse em uma coluna e escolher Remover coluna. Mas, para ser honesto, a história aqui não é sobre a economia de tempo no dia 1.
Mas espere: o Power Query lembra todas as suas etapas
Observe no lado direito da janela do Power Query. Existe uma lista chamada Etapas aplicadas. É uma trilha de auditoria instantânea de todas as suas etapas. Clique em qualquer ícone de engrenagem para alterar suas escolhas nessa etapa e fazer com que as alterações sejam propagadas nas etapas futuras. Clique em qualquer etapa para ver como eram os dados antes dessa etapa.
Quando terminar de limpar os dados, clique em Fechar e carregar conforme mostrado abaixo.
Dica
Se seus dados tiverem mais de 1.048.576 linhas, você pode usar a lista suspensa Fechar e carregar para carregar os dados diretamente no Power Pivot Data Model, que pode acomodar 995 milhões de linhas se houver memória suficiente instalada na máquina.
Em alguns segundos, seus dados transformados aparecem no Excel. Impressionante.
A recompensa: limpe os dados amanhã com um clique
Mas, novamente, a história do Power Query não é sobre a economia de tempo no dia 1. Quando você seleciona os dados retornados pelo Power Query, um painel Consultas e conexões aparece no lado direito do Excel e nele há um botão Atualizar . (Precisamos de um botão Editar aqui, mas como não existe, você deve clicar com o botão direito na consulta original para visualizar ou fazer alterações na consulta original).
É divertido limpar os dados no dia 1. Adoro fazer algo novo. Mas quando meu gerente vê o relatório resultante e diz “Lindo. Você pode fazer isso todos os dias? ” Eu rapidamente comecei a odiar o tédio de limpar o mesmo conjunto de dados todos os dias.
Portanto, para demonstrar o Dia 400 de limpeza dos dados, mudei completamente o arquivo original. Novos produtos, novos clientes, números menores, mais linhas, conforme mostrado abaixo. Eu salvo esta nova versão do arquivo no mesmo caminho e com o mesmo nome do arquivo original.
Se eu abrir a pasta de trabalho de consulta e clicar em Atualizar, em alguns segundos, o Power Query relatará 92 linhas em vez de 68 linhas.
Limpar os dados no Dia 2, Dia 3, Dia, 4, … Dia 400, … Dia Infinito agora leva dois cliques.
Este único exemplo apenas arranha a superfície do Power Query. Se você passar duas horas com o livro, M is for (Data) Monkey por Ken Puls e Miguel Escobar, você aprenderá sobre outros recursos, como estes:
- Combinação de todos os arquivos Excel ou CSV de uma pasta em uma única grade do Excel
- Converter uma célula com Maçã; Banana; Cereja; Dill; Berinjela em cinco linhas no Excel
- Fazendo uma VLOOKUP para uma pasta de trabalho de pesquisa enquanto você traz dados para o Power Query
- Transformar uma única consulta em uma função que pode ser aplicada a todas as linhas do Excel
Conteúdo original publicado no MrExcel.