Power Query es una nueva herramienta de Microsoft para extraer, transformar y cargar datos. El artículo de hoy trata sobre el procesamiento de todos los archivos en una carpeta.
Power Query está integrado en Excel 2016 y está disponible como descarga gratuita en ciertas versiones de Excel 2010 y Excel 2013. La herramienta está diseñada para extraer, transformar y cargar datos en Excel desde una variedad de fuentes. La mejor parte: Power Query recuerda sus pasos y los reproducirá cuando desee actualizar los datos. En el momento de la impresión de este libro, las funciones de Power Query en Excel 2016 se encuentran en la pestaña Datos, en el grupo Obtener y transformar, en Nueva consulta. Es difícil predecir si Microsoft cambiará retroactivamente el nombre de Power Query a Get & Transform en Excel 2010 y Excel 2013.
Este complemento gratuito es tan asombroso que podría haber un libro completo al respecto. Pero como uno de mis 40 consejos principales, quiero cubrir algo muy simple: traer una lista de archivos a Excel, junto con la fecha de creación del archivo y tal vez el tamaño. Esto es útil para crear una lista de libros de trabajo presupuestarios o una lista de fotos.
En Excel 2016, selecciona Datos, Nueva consulta, Desde archivo, Desde carpeta. En versiones anteriores de Excel, use Power Query, Desde archivo, Desde carpeta. Especifique la carpeta:
Mientras edita la consulta, haga clic con el botón derecho en las columnas que no desee y elija Eliminar.
Para obtener el tamaño del archivo, haga clic en este icono en la columna Atributos:
Aparece una lista de atributos adicionales. Elija tamaño.
Hay disponible una gran lista de opciones de transformación.
Cuando haya terminado de editar la consulta, haga clic en Cerrar y cargar.
Los datos se cargan en Excel como una tabla.
Más tarde, para actualizar la tabla, seleccione Datos, Actualizar todo. Excel recuerda todos los pasos y actualiza la tabla con una lista actual de archivos en la carpeta.
Para obtener una descripción completa de la función anteriormente conocida como Power Query, consulte M is for (Data) Monkey de Ken Puls y Miguel Escobar.
M es para (DATOS) MONO »
Gracias a Miguel Escobar, Rob García, Mike Girvin, Ray Hauser y Colin Michael por nominar a Power Query.
Ver video
- Las herramientas de Power Query están en la pestaña Datos en Excel 2016
- Complemento gratuito para 2010 y 2013
- Enumere todos los archivos de una carpeta en la cuadrícula de Excel usando Power Query
- Elija nueva consulta, desde archivo, desde carpeta
- No es obvio: expanda el campo de atributo para obtener el tamaño
- Si sus datos están en archivos CSV, puede importar todos los archivos a la vez en una sola cuadrícula
- Promocionar la fila de encabezado
- Eliminar las filas de encabezado restantes
- Reemplazar "" con nulo
- Rellenar para la vista de esquema
- Eliminar la columna del gran total
- Desvive los datos
- Fórmula para convertir los nombres de los meses en fechas
- Lista completa de pasos: deshacer más grande del mundo
- Al día siguiente: actualice la consulta para volver a realizar todos los pasos
Transcripción del video
- Power Query está integrado en las versiones de Windows de Excel 2016. Busque en la pestaña Datos en el grupo Obtener y transformar. Si tiene 2010 o
- 2013 siempre que esté ejecutando Windows
- y no Mac todo lo que está aquí en Get & Transform
- se puede descargar gratis de Microsoft. Solo busca
- Descarga Power Query.
- Hoy, estoy interesado en usar Power Query para obtener una lista de archivos. yo
- desea enumerar todos los archivos en una carpeta.
- Quizás necesito ver qué archivos son los
- archivos grandes o necesito ordenar o necesito
- sabes conseguir una combinación de ti
- conocer los archivos de presupuesto que enviamos
- y luego una carpeta diferente, cuáles
- volvimos volvimos.
- Para comenzar, vaya a Datos, Obtener y transformar, Desde archivo, Desde carpeta.
- Pegue en la ruta de la carpeta o use el botón Examinar.
- Haga clic en Aceptar y me muestran esto
- avance. Elija Editar.
- Un par de cosas aquí ves que tenemos
- el nombre del archivo la extensión la fecha
- accedido, fecha de modificación, fecha de creación.
- Realmente no es obvio que este símbolo junto al encabezado Atributos signifique Expandir. Haga clic en ese símbolo y habrá más cosas en
- aquí y si hace clic en este símbolo,
- puede entrar y obtener cosas como el tamaño del archivo
- o si es de solo lectura y cosas como
- que en este caso solo quiero archivo
- Talla. Elija Tamaño de archivo. Haga clic en Aceptar. Le dan un nuevo campo con un nombre de Attributes.Size.
- Puedo ver cuántos bytes hay en
- cada archivo.
- Quizás no necesito todo aquí, quizás
- No necesito la fecha creada para poder
- haga clic derecho y diga que quiero
- eliminar esa columna. Esta
- binario que no necesito que eliminará
- esa columna. Desde la cinta, haga clic en Cerrar y cargar.
- En unos segundos, tendrá una vista ordenable de
- todo en esa carpeta si la carpeta
- cambios puedo entrar aquí y puedo
- actualiza la consulta y volverá
- y extraiga esos datos correctamente.
- para mi este es un problema que soliamos
- tenemos todo el tiempo, enviaríamos 200
- archivos de presupuesto
- y recuperas a alguien, no a todos
- Atrás necesitas poder comparar
- ahora esencialmente puedo hacer un vlookup
- entre carpetas.
- Es simplemente asombroso como
- genial es pero mira, vayamos más allá
- 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 libro te enseñará
- tu todo sobre la consulta de poder
- interfaz es un libro increíble el mejor
- libro sobre el poder consulta todo lo que aprendí
- Aprendí de este libro. Tomé un vuelo desde
- Orlando a Dallas - leí todo el libro
- y mi conocimiento de la consulta de energía solo
- se disparó en dos horas puede estar hasta
- acelerar y reemplazar las cosas que haría
- he tenido un uso con VBA.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2037.xlsx
