Usos del Power Query - integrar archivos de una carpeta

miércoles, febrero 12, 2014

La nota anterior de la serie terminaba con una (casi existencial) pregunta:

¿qué pasa si tenemos que integrar una cantidad considerables de hojas? ¿Hay alguna manera de automatizar el proceso?
En la nota vimos como integrar datos de tres archivos en una única tabla usando el Power Query; pero si el número de archivos a integrar es considerable, es natural que busquemos una forma menos "pedestre" de hacerlo.

Las buena noticia es que se puede; la mala es que no es trivial. No sólo no es trivial sino que para hacerlo deben cumplirse ciertas condiciones "sine qua non" (por lo menos hasta donde dan mis conocimientos):


  1. los archivos/cuadernos deben estar en la misma carpeta;
  2. las tablas de datos deben tener exactamente la misma estructura (campos y orden);
  3. la hoja con los datos en cada cuaderno debe tener el mismo nombre. 

A partir de aquí esta nota se basa en este post del excelente blog Bacon Bits de Mike Alexander.

Iré mostrando el proceso paso por paso, con explicaciones lo menos técnicas posible donde sea necesario.

En nuestro ejemplo tenemos tres cuadernos Excel datos de ventas de tres sucursales (Norte, Sur y Centro); cada cuaderno contiene una única hoja con el nombre "reporte".

Empezamos por crear una consulta a uno de los cuadernos

Como mostramos en las notas anteriores, seleccionamos la hoja "Reporte" y usamos la opción Edit para abrir la consulta en la ventana del editor de Query


En la ventana del Query usamos la opción "Use first row as headers"


En la imagen arriba podemos apreciar que, similar a lo que conocemos de una hoja de Excel, existe una barra de fórmulas. Despues de convertir la primer fila de la hoja en encabezamiento de la tabla podemos ver que Query a aplicado la función Table.PromoteHeaders

Para combinar, integrar y transformar datos de diversas fuentes Power Query usa un lenguaje de fórmulas informalmente llamado "M". Por el momento no vamos a agregar nada más sobre el tema; sólo recordemos que Power Query usa fórmulas.

Nuestro próximo paso consiste en abrir el editor avanzado del Query (View-Advanced Editor)


Como puede verse, Power Query ha ido creando un código con las operaciones que hemos realizado (algo así como una macro grabada). La idea ahora es buscar la forma de reemplazar la referencia  C:\Ventas\Sucursal Centro.xlsx por una referencia variable.
Para lograr esto vamos a crear una función con dos variable "FilePath" y "FileName" (aclaración: no es obligatorio el uso de inglés para los parámetros o para el nombre de la función).

Modificamos el código en el editor avanzado agregando la función repVentas(FilePath,FilesName), y haciendo los cambiosseñalados en la imagen de abajo


Cerramos el editor avanzado; en el editor del Query le damos un nombre representativo a la funcion


y finalmente apretamos "Apply & Close" con este resultado

Lo que acabamos de hacer es crear una función en "M" que nos permitirá combinar los cuadernos que se encuentren el una misma carpeta (y que cumplas las condiciones que señalamos al principio de esta nota)

En la pestaña del Query, usamos la opción From Folder


Ingresamos el path de la carpeta que contiene los archivos y apretamos Ok. El query abre esta consulta

Abrimos el menú contextual con un clic del botón derecho sobre alguna de las columnas y eligimos la opción Insert Custom Colum; en el diálogo invocamos la función que creamos (funcVentas)

Al apretar Ok, Query agrega una nueva columna "Custom"

Ahora expandemos la columna Custom apretando la doble flecha que aparece en el encabezamiento


Aquí podemos decidir que columnas expandir y cuales no. En nuestro ejemplo expandemos todas. Antes de transferir la consulta a una hoja de Excel, eliminamos las columnas innecesarias y apretamos Apply & Close. Excel abre la tabla en una hoja



¡Misión cumplida! Como dicho, no es trivial, pero excelente herramienta si tenemos que integrar cantidades considerables de archivos.

9 comments:

Anónimo,  05 agosto, 2014 20:25  

Hola sigue todos los pasos al pie de la letra y no me funciono, tienes algún correo donde podamos hablar? gracias

Jorge Dunkelman 05 agosto, 2014 20:39  

Hola, fijate en el enlace Ayuda (en la parte superior de la plantilla).

Anónimo,  05 agosto, 2014 22:17  

Es que lo cómico es que me aparece en el recuadro donde ingrese la formula que esta bien escrita y cuando lo voy a aplicar en el siguiente paso me dice ¿se ha escrito correctamente? y ya revise ayuda y la verdad no me ayudo mucho

Jorge Dunkelman 06 agosto, 2014 07:16  

Por lo visto sigue habiendo un problema de sintaxis.

Anónimo,  08 agosto, 2014 19:43  

Lo escribí tal cual, lo das en el ejemplo.

Jorge Dunkelman 08 agosto, 2014 19:58  

Mandame el archivo por mail privado.

Anónimo,  08 agosto, 2014 21:50  

Y como es tu email?

Jorge Dunkelman 08 agosto, 2014 22:49  

Como te puse en mi primer comentario, fijate en el enlace Ayuda, en la parte superior de la plantilla del blog.

Gerson Pineda 28 octubre, 2016 21:27  

Hola Jorge!

No funciona, lo hice 3 veces, despacio y nada, creo que al tutorial se le escapa algo, por ejemplo no explicas nada sobre la imagen que aparece debajo de este comentario: *** En la pestaña del Query, usamos la opción From Folder ***

Saludos cordiales

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP