Dicas de Excel

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.

The Get & Transform Data group includes Get Data, From Text/CSV, From Web, From Table/Range, Recent Sources, Existing Connections.

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.

Products are in column A, but in an outline view where Applie appears in A2, followed by several blank cells. Banana is in A8 followed by more blank cells. Customers are in column B. Quarters are going across the worksheet in C, D, E, and F.

No Excel, selecione Obter dados, do arquivo, da pasta de trabalho.

Get Data, From File, From Workbook.

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 .

The blank cells in column A now say "null" in the Power Query Editor.

Em Power Query, selecione Transform, Fill, Down.

Choose column A. Open the Fill drop-down menu and choose 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.

Select columns A and B in Power Query. On the Ribbon, choose Unpivot other columns.

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.

You have four times as many rows. Columns A & B appear the same (except there are four rows for each previous one row). The Quarters that were going across columns C, D, E, and F now go down column C. The revenue from the data set is now in column D.

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.

On the right side of the Power Query Editor, a list of Applied Steps. For this example, you have Source, Navigation, Promoted Headers, Changed Type, Filled Down, Unpivoted Other Columsn, Renamed Columns.

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.

On the Home tab in Power Query, choose Close & Load.

Em alguns segundos, seus dados transformados aparecem no Excel. Impressionante.

The transformed data is returned to a table in Excel.

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).

The Queries & Connections panel lists one query called Sheet1 with 68 rows loaded. If you make the panel wider and hover over Sheet1, a refresh icon appears.

É 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.

Change some data in the original worksheet. Add more rows. Type new customers. Change numbers.

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.

Click the Refresh icon in the Queries & Connections panel and it reports you have 92 rows loaded.

Limpar os dados no Dia 2, Dia 3, Dia, 4, … Dia 400, … Dia Infinito agora leva dois cliques.

The new customers appear at the bottom of the table in Excel.

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.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *