jueves, febrero 23, 2017

Listas Desplegables con valores únicos con Power Query

En el pasado remoto de este blog publiqué dos posts sobre cómo crear una lista desplegable con valores únicos a partir de una tabla de datos. El escenario habitual es una tabla de datos, supongamos de ventas, a partir de la cual queremos crear una validación de datos de los nombres de los clientes.
El procedimiento habitual sería crear un nombre definido que se refiera a la columna que contiene los nombres de los clientes. El problema es que los nombres se repiten y lo queremos es una liksta de valores únicos.

El primer post, del año 2006, presentaba una solución compleja con fórmulas. Hasta tal punto compleja que publiqué un post adicional para explicar el funcionamiento de esta fórmulas.

El segundo post, del año 2011, presentaba una solución tal vez más elegante pero también compleja, incluyendo el uso de Vba (macros).

Usando Power Query  podemos construir una solución más sencilla y ésto es lo que mostraré en este post.

El escenario:

  • una hoja que contiene una tabla con datos de ventas;

  • una hoja donde queremos queremos construir una reporte sencillo: una celda que contiene el nombre del cliente, que se elige de una validación de datos y un celda donde calculamos las ventas del cliente com SUMAR.SI, basándonos en el nombre del cliente seleccionado.

donde la celda C3 contiene la fórmula 

=SUMAR.SI(tbl_Sales2016[Cliente],B3,tbl_Sales2016[Monto])

Primer paso: crear una consulta en el Power Query para generar una lista de valores únicos ordenados alfabéticamente.



Segundo paso: crear un nombre definido que se refiera al rango de los nombres de los clientes y crear la lista desplegable con validación de datos.




La gran ventaja de este modelo sobre las soluciones anteriores es que una vez construida la consulta sólo tendremos que procuparnos de actualizar la consulta cada vez que agreguemos o quitemos filas en la tabla de datos (clientes nuevos o clientes que dejan de estar en a lista).
La desventaja es que tenemos que actualizar la consulta! Es decir, tendremos que confiar que el usuario no se olvide de hacerlo.
Podemos evitar este inconveniente creando un evento de manera que cada vez que el usuario sale de la hoja "bd" la consulta se actualice. En el módulo del editor de Vb de la hoja "bd" creamos este evento Worksheet_Deactivate



 De esta manera nos aseguramos que la lista deplegable se actualizará con cada cambio en la tabla de datos.


lunes, febrero 20, 2017

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.


jueves, febrero 16, 2017

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.