Power Query é uma nova ferramenta da Microsoft para extrair, transformar e carregar dados. O artigo de hoje é sobre o processamento de todos os arquivos em uma pasta.
O Power Query é integrado ao Excel 2016 e está disponível para download gratuito em certas versões do Excel 2010 e Excel 2013. A ferramenta foi projetada para extrair, transformar e carregar dados no Excel de uma variedade de fontes. A melhor parte: o Power Query lembra suas etapas e as reproduz quando você deseja atualizar os dados. No momento em que este livro vai para a impressão, os recursos do Power Query no Excel 2016 estão na guia Data, no grupo Get & Transform, em New Query. É difícil prever se a Microsoft renomeará retroativamente o Power Query para Get & Transform no Excel 2010 e Excel 2013.
Este add-in gratuito é tão incrível que poderia haver um livro inteiro sobre ele. Mas, como uma das minhas 40 dicas principais, quero cobrir algo muito simples: trazer uma lista de arquivos para o Excel, junto com a data de criação do arquivo e talvez o tamanho. Isso é útil para criar uma lista de pastas de trabalho de orçamento ou uma lista de fotos.
No Excel 2016, você seleciona Dados, Nova consulta, Do arquivo, Da pasta. Em versões anteriores do Excel, use Power Query, From File, From Folder. Especifique a pasta:
Ao editar a consulta, clique com o botão direito em qualquer coluna que você não deseja e escolha Remover.
Para obter o tamanho do arquivo, clique neste ícone na coluna Atributos:
Uma lista de atributos extras é exibida. Escolha o tamanho.
Uma grande lista de opções de transformação está disponível.
Quando terminar de editar a consulta, clique em Fechar e carregar.
Os dados são carregados no Excel como uma tabela.
Mais tarde, para atualizar a tabela, selecione Dados, Atualizar tudo. O Excel se lembra de todas as etapas e atualiza a tabela com uma lista atual de arquivos na pasta.
Para obter uma descrição completa do recurso anteriormente conhecido como Power Query, consulte M is for (Data) Monkey de Ken Puls e Miguel Escobar.
M é para (DATA) MACACO »
Agradecimentos a Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser e Colin Michael por nomear o Power Query.
Assistir vídeo
- As ferramentas do Power Query estão na guia Dados do Excel 2016
- Add-in grátis para 2010 e 2013
- Liste todos os arquivos de uma pasta na grade do Excel usando o Power Query
- Escolha nova consulta, do arquivo, da pasta
- Não é óbvio: expanda o campo do atributo para obter o tamanho
- Se seus dados estiverem em arquivos CSV, você pode importar todos os arquivos de uma vez em uma única grade
- Promova a linha de título
- Exclua as linhas de cabeçalho restantes
- Substitua "" por nulo
- Preencha para ver o esboço
- Exclua a coluna de total geral
- Desviar os dados
- Fórmula para converter nomes de meses em datas
- Lista completa de etapas - o maior Undo do mundo
- No dia seguinte - atualize a consulta para refazer todas as etapas
Transcrição do vídeo
- O Power Query está integrado nas versões do Windows do Excel 2016. Veja a guia Dados no grupo Obter e transformar. Se você tem 2010 ou
- 2013, contanto que você esteja executando o Windows
- e não Mac tudo que está aqui em Get & Transform
- você pode baixar gratuitamente da Microsoft. Basta pesquisar por
- Baixe o Power Query.
- Hoje, estou interessado em usar o Power Query para obter uma lista de arquivos. Eu
- deseja listar todos os arquivos em uma pasta.
- Talvez eu precise ver quais arquivos são os
- arquivos grandes ou preciso classificar ou preciso
- você sabe obter uma combinação de você
- conheça os arquivos de orçamento que enviamos
- e então uma pasta diferente
- nós voltamos.
- Para começar, vá para Data, Get & Tranform, From File, From Folder.
- Cole no caminho da pasta ou use o botão Procurar.
- Clique em OK e eles me mostram isso
- visualização. Escolha Editar.
- Algumas coisas aqui você vê que temos
- o nome do arquivo a extensão a data
- acessado, data de modificação, data de criação.
- Não é realmente óbvio que este símbolo próximo ao título Atributos significa Expandir. Clique nesse símbolo e há mais coisas em
- aqui e se você clicar neste símbolo, então eu
- pode entrar e obter coisas como o tamanho do arquivo
- ou se for somente leitura e coisas como
- então, neste caso, eu só quero o arquivo
- Tamanho. Escolha o tamanho do arquivo. Clique OK. Eles fornecem um novo campo com um nome de Attributes.Size.
- Eu posso ver quantos bytes estão em
- cada arquivo.
- Talvez eu não precise de tudo aqui talvez
- Eu não preciso da data criada para que eu possa
- clique com o botão direito e diga que eu quero
- remova essa coluna. este
- binário não preciso disso vou remover
- essa coluna. Na faixa de opções, clique em Fechar e carregar.
- Em alguns segundos, você terá uma visão classificável de
- tudo nessa pasta, se a pasta
- mudanças eu posso entrar aqui e eu posso
- atualize a consulta e ela voltará
- para fora e extraia esses dados corretamente, isto é
- para mim este é um problema que costumávamos
- teríamos todo o tempo que enviaríamos 200
- arquivos de orçamento
- e você consegue alguém de volta, não todos eles
- de volta você precisa ser capaz de comparar
- agora posso essencialmente fazer um vlookup
- entre pastas.
- É simplesmente incrível como
- legal é mas olha, vamos além
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Este livro vai ensinar
- tudo sobre a consulta de energia
- interface é um livro incrível, o melhor
- livro sobre consulta avançada tudo que aprendi
- Aprendi com este livro. Eu peguei um vôo de
- Orlando para Dallas - eu li o livro inteiro
- e meu conhecimento de consulta de energia apenas
- disparou em duas horas, você pode chegar a
- acelerar e substituir coisas que você faria
- tive um acostumado a ter feito com o VBA.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2037.xlsx
