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
