Usos del Power Query - importar una lista de archivos a Excel

Las nuevas herramientas que Microsoft ha incorporado a Excel hace que las versiones anteriores a Excel 2010 parezcan prehistóricas. Me refiero a las herramientas de BI (Business Intelligence, Inteligencia de negocios), específicamente Power Pivot y Power Query.
A quien le interese el Power Pivot  (y a todo analista de datos le debe interesar) le recomiendo visitar el sitio de Miguel Escobar, Powered Solutions. Miguel ofrece cursos de Power Pivot y quien esté interesado puede acceder a la información con un clic en el banner que aparece en la columna derecha (aclaración: por cada inscripción al curso recibo una comisión)

En esta y otras futuras notas me ocuparé de usos prácticos del complemento Power Query. Algunos de mis memoriosos lectores recordará la nota sobre cómo importar una lista de archivos usando funciones XLM (las viejas macrofunciones de Excel 4). Una nota posterior trataba sobre como generar la lista usando Vba. Ambas técnicas tienen un cierto grado de complejidad y requieren cierto nivel de conocimientos en Excel.

El complemento Power Query simplifica la tarea, lo que mostraremos con este ejemplo (el menú del Power Query en mi máquina está en inglés por lo que me discuplo de antemano). Supongamos que quiero crear una lista con todos los archivos que aparecen en mi carpeta "Blog".

Como puede apreciarse, bajo la carpeta Blog hay otras y también archivos de diversos tipos.
Después de descaragar e instalar el complemento (Excel 2010 y 2013), veremos una nueva pestaña en la ventana de Excel: Power Query

En el grupo "Get External Data" (datos externos), elegimos la opción "From File - From Folder"; Excel abre una ventanilla donde seleccionamos la ubicación deseada

Al apretar OK dos veces, Excel transfiere los datos a la ventana del editor del Query (más  adelante transferiremos los datos a la hoja de Excel).

En esta ventana del editor podemos ordenar, seleccionar y transformar datos, agregar o eliminar columnas y muchas otras operaciones. Nótese además que el editor cuenta con una barra de fórmulas (pero de todo esto nos ocuparemos en otras notas).

Después de examinar los datos, todo los que nos queda por hacer es apretar "Done". Excel transfiere los datos del editor del Query a una hoja de Excel

En esta nota tampoco nos ocuparemos de la ventanilla de las definiciones del Query (Query Setup) que aparece a la derecha de la hoja. Sencillamente la cerramos apretando el "x" en el icono "Show Query Settings" en la barra de opciones. ¡Listo! En la hoja de Excel tenemos una tabla con todos los datos de los archivos de la carpeta

En la próxima nota veremos, basados en este ejemplo, como podemos transformar los datos antes de transferirlos a Excel.

Gráficos cascada (Waterfall) con valores negativos

Hace poco más de cuatro años atrás publiqué este post sobre cómo crear gráficos de tipo "Waterfall" ("Cascada" o "Flying Bricks") con Excel.
Este tipo de gráficos permite ver el efecto acumulado de valores positivos y negativos en una secuencia. En el post mostramos este ejemplo, partiendo de los resultados anuales de una cadena de tiendas

mostramos este gráfico para mostrar el aporte de cada tiena al cambio en el resultado anual de la tienda

Este gráfico lo creamos usando el gráfico de columnas apiladas elaborando los datos con una tabla auxiliar (las fórmulas y técnicas pueden verse en la nota del enlace, también podrán descargar el archivo del ejemplo).

Esta técnica tiene un inconveniente, como me lo hace notar uno de mis lectores. Los valores de los cambios no cruzan el eje horizontal. Para demostrarlo, supongamos que nuestra cadena de tiendas ha tenido un año 2013 terrible

Aplicando la técnica que usamos hasta ahora obtendríamos este gráfico

La columna ("ladrillo") tendría que aparecer cruzando el eje horizontal, entrando en la zona de los números negativos:

Para solucionar este problema tendremos que cambiarlas fórmulas en la tabla auxiliar. Esta es la tabla del modelo sencillo

Esta es la nueva tabla auxiliar sobre la cual construimos el gráfico

A diferencia del modelo sencillo, calculamos dos columnas para los valores positivos (Positivo +; Positivo -) y dos columnas para los valores negativos (Negativo +; Negativo -). "Positivo +" y "Negativo +" para los valores que se mostrarán por encima del eje horizontal; "Positivo -" y "Negativo -" para los valores que aparecerán por debajo del eje de la X.

Las fórmulas de las columnas G y H son obvias.

La fórmula de la columna I (Acumulado) es:

=MAX(0,MIN(SUMA(G$3:G3),SUMA(G$3:G4)))+MIN(0,MAX(SUMA(G$3:G3),SUMA(G$3:G4)))

Esta fórmula crea la parte "invisible" de la columna.

La fórmula en la columna J (Positivo +): =MAX(0,MIN(SUMA(G$3:G4),G4))

La fórmula en la columna K (Positivo -): =-MAX(0,G4-J4) (prestar atención al "-")

La fórmula en la columna L (Negativo +): =MAX(0,M4-G4)

La fórmula en la columna M (Negativo -): =MIN(0,MAX(SUMA(G$3:G4),G4))

Para crear el gráfico seleccionamos primero el rango F2:F8 y manteniendo el botón Ctrl apretado seleccionamos el rango H2:N8. Seleccionamos el gráfico de columnas apiladas

El último paso es aplicar los formatos necesarios.

El archivo se puede descargar aquí.

El extraño caso del cálculo manual persistente

Excel tiene una manera peculiar de manejar las opciones de cálculo. Estas pueden ser manual, automático o automático excepto en tablas de datos. En las Opciones de Excel, en Fórmulas podemos ver las posibilidades

Las opciones de cálculo son hereditarias (ya verán por qué) y siguen estas reglas:

• El primer documento abierto utiliza el modo de cálculo con la que se guardó. Documentos abiertos posteriormente utilizan el mismo modo.

• Al cambiar el modo de cálculo de un documento abierto se cambia el modo de todos los documentos abiertos.

• Si se cierran todos los documentos y crear un documento nuevo, el nuevo documento utiliza el mismo modo de cálculo como los documentos cerrados previamente.

• Si ha cambiado el modo de cálculo en un libro y se guarda el archivo, se guarda el modo de cálculo actual.


Si cerramos una sesión de Excel y el último cuaderno que guardamos estaba definido con cálculo automático, al abrir una nueva sesión esperamos que también el primer cuaderno que creamos esté definido con cálculo automático.

Sin embargo mi Excel insistía en abrir el primer cuaderno de cada sesión en estado de cálculo manual. Intenté todos los remedios conocidos (guardar un cuaderno en Automático y cerrar Excel, revisar si hay un WorkbookOpen event en algún Add-in, si el cuaderno en XLSTART está definido con cálculo manual). ¡Nada! Es más, no tengo ningún archivo en XLSTART, así que mi sesión se abre sin ningún "Libro1".

Al borde de la desesperación encontré el problema: de alguna manera que no logro entender, había guardado el cuaderno Personal.xlsb con la opción Manual.

Podemos solucionar este problema de dos maneras:

Por defecto, el cuaderno Personal está oculto. Este cuaderno tiene una hoja y para cambiar sus propiedades tenemos que volverlo visible

Una vez visible cambiamos la propiedad, guardamos y volvemos a ocultar.

Otra posibilidad, sin necesidad de mostrar el Personal, es abrir el editor de Vb (Alt+F11), nos aseguramos que el Personal sea el cuaderno activo y en la ventanilla Inmediato ponemos:

Workbooks("Personal.xlsb").Parent.Calculation = xlCalculationAutomatic: Workbooks("Personal.xlsb").Save