Integrar datos de archivos cerrados con Power Query

viernes, marzo 18, 2016

Consolidar datos de distintos archivos es una tarea frecuente para todo analista que use Excel. Tal es así que este blog ha tratado el tema desde distintos ángulos: usando MS Query, Access, SQL, Vba (macros) y últimamente con Power Query.

La técnica que mostré en el post anterior puede parecer complicada e incluso intimidante para quien recién se inicia en el uso del Power Query (nueva interfaz, nuevo idioma). Para nuestra fortuna, Miguel Escobar publicó en el pasado una técnica sencilla, que pueden ver en esta nota (y continuada luego en esta otra nota), y que reproduzco aquí en una versión simplificada (parece ser que Miguel eliminó la versión en castellano del post).

Power Query nos permite consolidar datos de distintos archivos sin necesidad de abrirlos, sin tener que programar Vba, sin SQL, en pocos pasos y con facilidad.

En nuestro ejemplo deben cumplirse dos condiciones:

1 - los archivos deben tener la misma estructura (número y tipo de columnas);
2 - los archivos se encuentran en la misma carpeta.

Supongamos tres archivos con las ventas de una empesa imaginaria


Nuestra misión en consolidar los datos de los tres años en una única tabla que luego podremos analizar con tablas dinámicas, mejor aún, con PowerPivot.

Empezamos toda la carpeta a la ventana del Power Query


obteniendo este resultado en la ventana del editor del Power Query


Además de los tres de ventas, la carpeta contiene otros archivos así que filtramos la tabla dejando visibles sólo los archivos con la extensión ".xlsx"


Eliminamos todos los campos en la tabla excepto "Content" (Binary ) y "Name". Una de las tantas cosas buenas del Power Query es que podemos seleccionar las columnas que queremos dejar y elegir la opción Remove Other Columns (eliminar las otras columnas)

muy práctico cuando queremos eliminar la mayor parte de las columnas en el ventana.

Ahora viene el paso crítico en el proceso: vamos a crear una nueva columna, "Datos", definida con la fórmula =Excel.Workbook([Content],true)


¿Qué es esta expresión "=Excel.Workbook([Content],true)"? Power Query tiene su propio lenguaje de funciones (conocido como "M"). La función Excel.Workbook pertenece a la categoría de funciones que permiten acceder a distitnas plataformas de datos y extraerlos en forma de tabla. El valor "true" delsegundo argumento de la función hace que la primer fila de la tabla de datos de la hoja sea usada como encabezamiento de las columnas.

Al escribir la fórmula debemos tomar en cuenta que, a diferencia de las de Excel, en las funciones del lenguaje del Power Query las mayúsculas y minúsculas tienen importancia. Si escribimos =excel.workbook, resultará un error. Siguiendo con nuestro ejemplo, introducimos la fórmula y apretamos OK para crear la nueva columna. Este es el resultado




Ahora expandimos la columna Datos apretando la doble flecha en el lado derecho del encabezado de la columna


Power Query nos ofrece abrir todas las columnas de "Table" pero dejamos sólo la marca en "Data" y apretamos OK


Ahora expandimos la columna "Data"



En este caso vamos a dejar todas las columnas



Los próximos dos pasos son:

# - eliminar las columnas "Content" y  "Name" que, en nuestro caso, no cumplen ya ninguna función;
# - cambiar el tipo de dato de la columna Fecha de "any" (general) a "Date" (fecha)



El última paso es pasar (load) la tabla que hemos creado a una hoja de Excel o crear sólo una conexión


Si elegimos la primer opción (Close and Load), Excel exporta la tabla a una hoja del cuaderno



2 comments:

Antonio Ricaurte Rojas 18 marzo, 2016 13:57  

Gracias oor la explicación, Julio. Sin embargo, ¿qué posibilidad existe de que publiques un video explicando el paso a paso de este ejercicio (y de todos, por supuesto)? Observar y escuchar es múcho más efectivo en el proceso de aprendizaje.

Jorge Dunkelman 18 marzo, 2016 14:04  

Ok Antonio pero con una condición. Que me llames Jorge, que es como mis padres quisieron :)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP