Unir hojas de un archivo Excel con Power Query

Casi todo usuario de Excel se ha enfrentado en algún momento a la tarea de unir datos de distintas hojas de un cuaderno. Por ejemplo, en un cuaderno Excel tenemos cuatro hojas conteniendo cada una los datos de ventas de cada uno de los trimestres del año. De la misma manera podríamos tener un cuaderno con 12 hojas, una por cada mes; o 48 hojas, una por cada mes de los últimos dos años; o…bien, la idea queda clara.

Si se trata de unir dos o tres tablas, que tienen la misma estructura, un simple Copiar y Pegar nos resuelve el problema. Pero cuando tenemos un número considerable de hojas Copiar y Pegar no sólo es tortuoso sino que puede conducir a omisiones o errores.

Power Query nos permite hacerlo con unos pocos clics y sin necesidad de abrir el archivo, de manera que podemos crear un nuevo archivo sin alterar el original.

Supongamos que nuestro original contiene cuatro hojas con los datos de ventas de cada uno de los trimestres del año. En la pestaña del Power Query (en Excel 2010/13, Datos Obtener y Transformar en 2016) creamos una consulta 

En la ventana del Navegador podemos ver las cuatro hojas del cuaderno. En lugar de seleccionar las hojas una por una, hacemos un clic al nombre del archivo (Ventas anuales por trimestre.xlsx) y apretamos el botón Edit. El resultado es el siguiente

Filtramos la tabla dejando visible sólo los valores “Sheet” en la columna “Kind”

con este resultado

Ahora eliminamos todas las columnas excepto “Data” (opcionalmente podemos dejar también la columna “Name” para identificar el origen de los datos) y apretamos el botón Expand (la doble flecha a la derecha del encabezado de la columna)

No nos dejamos inquietar por la advertencia “List may be incomplete” y apretamos “OK” con decisión. 

Como podemos apreciar, los encabezamientos aparecen en la primer fila por lo que deberemos promoverlos con “Use first row as headers”. Ahora se nos presenta un último problema: cada tabla en cada hoja cuenta con encabezamientos por lo que éstos aparecen en las filas de la tabla unificada y debemos eliminarlos.

Para hacerlo usamos el filtro en alguna de las columnas, por ejemplo en la primera quitamos la marca de "Customers.CompanyName"

Todo lo que nos queda por hacer es apretar Close and Load y ya tenemos nuestros datos integrados en una única tabla.

El curioso caso de las tablas dinámicas que no se actualizan.

A las disculpas presentadas en el postanterior corresponde también alguna explicación. En los últimos meses he estado desarrollando modelos con las (no tan) nuevas herramientas de Excel, Power Query y PowerPivot, lo cual consumió el poco tiempo libre que puedo dedicarle al blog.

Para quien haya trabajado exclusivamente con Excel el aprendizaje de estas nuevas herramientas presupone un esfuerzo adicional: dejar de “pensar Excel”. Además cada una de estas aplicaciones viene con sus propios lenguajes (“M” para Power Query; “DAX” para PowerPivot) y sus propias funciones. Pero, el tema de este post no es “Introducción a Power Query…” o “Como PowerPivot cambió mi vida”, sino compartir un aprendizaje relacionado con Power Query.

Hace unos días finalicé el desarrollo de un modelo para controlar lotes en una empresa farmacéutica. La herramienta ideal era PowerPivot pero por motivos que no detallaré aquí, decidí hacer todo el desarrollo con Power Query. El modelo recibe datos, parte de ellos en forma manual; consultas en Power Query realizan las transformaciones necesarias, combinan y unen datos de distintas consultas y crean una serie de tablas planas que alimentan varios reportes creados con tablas dinámicas.

Mi intención era que el usuario apretará el botón “Actualizar todo” para asegurar que los reportes reflejaran siempre los últimos datos. Pero prudentemente desconfiando de mis usuarios, decidí incluir un evento que disparara una rutina ThisWorkbook.RefreshAll cada vez que el usuario accediera a la hoja de los reportes. Para mi desconcierto, si bien las tablas de datos se actualizaban, no así las tablas dinámicas.

Ahorrándoles la vía crucis de descubrir dónde estaba el problema, voy directamente al grano. El problema pasa por la actualización en segundo plano de las consultas (background refresh), que por definición está habilitado para toda conexión que creamos

Para permitir que todas las consultas se actualicen antes que las tablas dinámicas debemos deshabilitar la actualización en segundo plano. Una posibilidad es hacerlo manualmente, pero más eficiente es hacerlo con esta macro y al mismo tiempo actualizar todas las consultas y las tablas dinámicas

 Sub Refresh_All()  
   Dim iConnectionsCount As Integer, iX As Integer  
   iConnectionsCount = ThisWorkbook.Connections.Count  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = False  
   Next iX  
   ActiveWorkbook.RefreshAll  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = True  
   Next iX  
 End Sub  

La primer parte de la macro deshabilita la actualización en segundo plano de todas las conexiones. Luego realiza la actualización (Refresh.All) y finalmente rehabilita la actualización en segunda plano.

Reemplazar nombres en formulas de Excel por sus referencias.

Hace varios meses que no publico notas en el blog, no por pereza (bueno, tal vez un poco) ni por desidia y espero que mis pocos, pero leales, seguidores sepan disculparme.

Para retomar el diálogo empiezo con una solución a una consulta que suelo recibir cada tanto: ¿cómo reemplazar los nombres definidos por sus referencias? Como sabemos, los nombres definidos nos permiten, entro otras cosas, volver más legibles nuestras fórmulas (aquí pueden ver todos los posts en mi blog sobre el tema).

Por ejemplo, si en lugar de =SUMA(A2:A13) usamos =SUMA(ventas), quedará inmediatamente claro qué está calculando la fórmula.

En esta nota del 2014 mostré como Excel nos permite reemplazar las referencias en las fórmulas por nombres definidos. Pero Excel no incluye ninguna herramienta para hacer lo opuesto, es decir, reemplazar los nombres definidos en las fórmulas por sus referencias.

Si por algún motivo queremos reemplazar nombres por referencias a rangos, tendremos que echar mano a una macro.

Este código reemplazará los nombres por los rangos referidos en las celdas seleccionadas.

 Sub change_to_ref()  
   Dim Nme As Name  
   Dim rngCell As Range  
   For Each Nme In Names  
   For Each rngCell In Selection  
     If rngCell.HasFormula Then  
       If InStr(1, rngCell.Formula, Nme.Name, vbTextCompare) > 0 Then  
         rngCell.Formula = Replace(rngCell.Formula, Nme.Name, Mid(Nme.RefersTo, 2, 9999))  
       End If  
     End If  
   Next rngCell  
   Next Nme  
 End Sub  

Para usarlo lo copiamos a un módulo común en el editor de Vb, preferentemente en el cuaderno Personal.xlsb de manera que podamos utilizarlo en todos los cuadernos activos de Excel.