Power Query è un nuovo strumento di Microsoft per estrarre, trasformare e caricare i dati. L'articolo di oggi riguarda l'elaborazione di tutti i file in una cartella.
Power Query è integrato in Excel 2016 ed è disponibile come download gratuito in alcune versioni di Excel 2010 ed Excel 2013. Lo strumento è progettato per estrarre, trasformare e caricare dati in Excel da una varietà di origini. La parte migliore: Power Query ricorda i tuoi passaggi e li riprodurrà quando desideri aggiornare i dati. Durante la stampa di questo libro, le funzionalità di Power Query in Excel 2016 si trovano nella scheda Dati, nel gruppo Ottieni e trasforma, in Nuova query. È difficile prevedere se Microsoft rinominerà retroattivamente Power Query in Recupera e trasforma in Excel 2010 ed Excel 2013.
Questo componente aggiuntivo gratuito è così sorprendente che potrebbe esserci un intero libro su di esso. Ma come uno dei miei primi 40 suggerimenti, voglio coprire qualcosa di molto semplice: portare un elenco di file in Excel, insieme alla data di creazione del file e forse alle dimensioni. Ciò è utile per creare un elenco di cartelle di lavoro del budget o un elenco di foto.
In Excel 2016 si seleziona Dati, Nuova query, Da file, Da cartella. Nelle versioni precedenti di Excel, usa Power Query, Da file, Da cartella. Specifica la cartella:
Durante la modifica della query, fai clic con il pulsante destro del mouse sulle colonne che non desideri e scegli Rimuovi.
Per ottenere la dimensione del file, fare clic su questa icona nella colonna Attributi:
Viene visualizzato un elenco di attributi aggiuntivi. Scegli la taglia.
È disponibile un ampio elenco di opzioni di trasformazione.
Al termine della modifica della query, fare clic su Chiudi e carica.
I dati vengono caricati in Excel come tabella.
Successivamente, per aggiornare la tabella, selezionare Dati, Aggiorna tutto. Excel ricorda tutti i passaggi e aggiorna la tabella con un elenco corrente di file nella cartella.
Per una descrizione completa della funzionalità precedentemente nota come Power Query, controlla M is for (Data) Monkey di Ken Puls e Miguel Escobar.
M sta per (DATA) MONKEY »
Grazie a Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser e Colin Michael per aver nominato Power Query.
Guarda un video
- Gli strumenti di Power Query si trovano nella scheda Dati in Excel 2016
- Componente aggiuntivo gratuito per il 2010 e il 2013
- Elenca tutti i file da una cartella nella griglia di Excel utilizzando Power Query
- Scegli Nuova query, Da file, Da cartella
- Non ovvio: espandi il campo dell'attributo per ottenere la dimensione
- Se i tuoi dati sono in file CSV, puoi importare tutti i file contemporaneamente in una singola griglia
- Promuovi la riga di intestazione
- Elimina le restanti righe di intestazione
- Sostituisci "" con null
- Riempi per la vista struttura
- Elimina la colonna del totale generale
- Annullare il pivot dei dati
- Formula per convertire i nomi dei mesi in date
- Elenco completo dei passaggi: il più grande annullamento del mondo
- Il giorno successivo: aggiorna la query per ripetere tutti i passaggi
Trascrizione del video
- Power Query è integrato nelle versioni Windows di Excel 2016. Cerca nella scheda Dati nel gruppo Recupera e trasforma. Se hai il 2010 o
- 2013 fintanto che esegui Windows
- e non Mac tutto ciò che è qui in Ottieni e trasforma
- puoi scaricare gratuitamente da Microsoft. Cerca solo
- Scarica Power Query.
- Oggi sono interessato a utilizzare Power Query per ottenere un elenco di file. io
- desidera elencare tutti i file in una cartella.
- Forse ho bisogno di vedere quali file sono i file
- file di grandi dimensioni o ho bisogno di ordinare o ho bisogno di
- sai per ottenere una combinazione di te
- conoscere i file di budget che abbiamo inviato
- e poi una cartella diversa quali
- siamo tornati.
- Per iniziare, vai su Dati, Ottieni e trasforma, Da file, Da cartella.
- Incolla il percorso della cartella o utilizza il pulsante Sfoglia.
- Fare clic su OK e mi mostrano questo
- anteprima. Scegli Modifica.
- Vedete, abbiamo un paio di cose qui
- il nome del file l'estensione la data
- accesso, data di modifica, data di creazione.
- Non è davvero ovvio che questo simbolo accanto all'intestazione Attributi significhi Espandi. Fai clic su quel simbolo e ci sono più cose dentro
- qui e se fai clic su questo simbolo, allora io
- può entrare e ottenere cose come la dimensione del file
- o se è di sola lettura e cose come
- quindi in questo caso voglio solo file
- taglia. Scegli la dimensione del file. Fare clic su OK. Ti danno un nuovo campo con un nome di Attributes.Size.
- Posso vedere quanti byte ci sono
- ogni file.
- Forse non ho bisogno di tutto qui forse
- Non ho bisogno della data creata così posso
- fare clic con il tasto destro e dire che lo voglio
- rimuovere quella colonna. Questo
- binario non ho bisogno che rimuoverà
- quella colonna. Dalla barra multifunzione, fare clic su Chiudi e carica.
- In pochi secondi avrai una visione ordinabile di
- tutto in quella cartella se la cartella
- cambiamenti posso entrare qui e posso
- aggiorna la query e tornerà indietro
- fuori e tirare quei dati a destra questo è
- per me questo è un problema a cui eravamo abituati
- avere tutto il tempo che vorremmo inviare 200
- file di budget
- e ti riprendi qualcuno, non tutti
- indietro devi essere in grado di confrontare così
- ora posso essenzialmente fare un vlookup
- tra le cartelle.
- È semplicemente incredibile come
- è bello ma guarda andiamo oltre
- 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.
- Questo libro insegnerà
- tutto ciò che riguarda la query di alimentazione
- l'interfaccia è un libro fantastico, il migliore
- prenota su power query tutto quello che ho imparato
- Ho imparato da questo libro. Sono salito su un volo da
- Da Orlando a Dallas - ho letto tutto il libro
- e solo la mia conoscenza del potere
- salito in due ore puoi essere fino a
- velocizza e sostituisci le cose che vorresti
- avevano usato per fare con VBA.
Download file
Scarica il file di esempio qui: Podcast2037.xlsx
