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 foi projetada para extrair, transformar e carregar dados no Excel a partir de um variedade de fontes. A melhor parte: o Power Query lembra suas etapas e as reproduz quando você deseja 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.
Digo isso sobre muitos dos novos recursos do Excel, mas esse realmente é o melhor recurso a chegar ao Excel em 20 anos.
Conto uma história em meus seminários ao vivo 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á deve 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 por Download Power Query. Seus comandos do Power Query aparecerão em uma guia dedicada do Power Query na faixa de opções.
Limpe os 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, Seta para cima, Ctrl + Enter .
No Power Query, selecione Transform, Fill, Down.
Todos os valores nulos são substituídos pelo valor acima. Com o Power Query, são necessários três cliques em vez de sete.
Próximo problema: as moedas estão cruzando 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 trimestres. Abra a lista suspensa Unpivot Columns na guia Transform e escolha Unpivot Other Columns, como mostrado abaixo.
Clique com o botão direito 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 primeiro dia.
Mas espere: o Power Query lembra todas as suas etapas
Observe o 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 se propaguem pelas etapas futuras. Clique em qualquer etapa para ver a aparência dos 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 o menu suspenso Fechar e carregar para carregar os dados diretamente no Power Pivot Data Model, que pode acomodar 995 milhões de linhas se você tiver 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 há, 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 primeiro dia. 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 reporta 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 lendo o livro, M is for (Data) Monkey de Ken Puls e Miguel Escobar, você aprenderá sobre outros recursos, como estes:
- Combinando todos os arquivos Excel ou CSV de uma pasta em uma única grade do Excel
- Converter uma célula com maçã; banana; cereja; endro; 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
Para obter uma descrição completa do Power Query, consulte M Is for (Data) Monkey de Ken Puls e Miguel Escobar. No final de 2019, a segunda edição renomeada, Master Your Data, estará disponível.
Agradecimentos a Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser e Colin Michael por nomear o Power Query.
