Calcular registros únicos con PowerPivot

En la nota anterior vimos como calcular los registros únicos de un campo usando la técnica "pivotear una pivot", es decir, crear una tabla dinámica cuya base de datos es a su vez una tabla dinámica. Esta técnica nos permite sobrellevar los problemas de tiempo de proceso que surgen al usar las técnicas más tradicionales: campo auxiliar con CONTAR.SI y Filtro Avanzado. Recordemos que si estamos usando Excel 2013 podemos agregar la base de datos al modelo de datos y usar luego la función Recuento Distinto para resumir el campo (ver en la parte final de la nota mencionada).

Existe una forma aún más sencilla de hacer el cálculo de registros únicos (o Recuento Distinto) si usamos Excel 2010. Primero deberemos asegurarnos de instalar el complemento PowerPivot (¿cómo??!!!! ¿Todavía no lo han hecho?!!!).

Volviendo al ejemplo de la nota anterior, empezamos por cargar la base de datos en la ventana del PowerPivot para lo cual seleccionamos alguna de las celdas de la tabla de datos y usamos "Crear tabla vinculada"

Una vez cargada creamos una tabla dinámica con la opción "Tabla Dinámica" en la ventana del PowerPivot

con el campo País en el área de las filas e Importe en el área de los valores

Para poder calcular la cantidad de clientes por país, vamos a crear una "Medida". En PowerPivor una "medida" (measure, en inglés) es similar a los campos calculados de las tablas dinámicas tradicionales pero mucho más flexibles y potentes . No entraré aquí en el tema, pero a los interesados en el potencial de PowerPivot les sugiero visitar el sitio de Powered Solutions (en español)

Volviendo al tema, creamos una medida apuntando al nombre de la tabla en la lista de campos y eligiendo la opción "Agregar nueva medida"

En la ventana que se abre para definir la medida vamos a usar la función DISTINCTCOUNT (esta es una función DAX que pertenece a PowerPivot, tema que no tocaremos en esta nota).

Este video muestra el proceso



El resultado final es este

Calcular registros únicos en rangos extensos - otra versión

Supongamos que tenemos una tabla de datos de ventas donde cada registro (fila) nos muestra el país, la ciudad, el cliente, el producto, la cantidad y, por suspuesto, el importe.
Ahora supongamos que nuestro jefe (o jefa) nos pide saber cuantos clientes hay en cada país. Esto significa hacer un recuento de valores únicos.

Cuando se trata de rangos extensos nada mejor que usar tablas dinámicas. Hemos mostrado en el pasado cómo hacerlo, como pueden ver en esta nota.

Excel 2013 cuenta con la función incorparada para registros únicos tal como mostré en esta nota.

Si todavía usamos Excel 2010 y la tabla de datos es extensa, por ejemplo 500 K filas, las técnicas que usan CONTAR.SI son prácticamente inútiles por el tiempo de proceso que demandan. Tampoco Filtro Avanzado con la opción Registro Únicos ofrece una solución eficiente.

El MVP Roger Govier propuso una solución que consiste en construir una tabla dinámica cuya base de datos es otra tabla dinámica; es decir, pivotear una pivot table.

Veamos como es esta técnica. Esta es nuestra tabla de datos

Como nos piden el "recuento distinto", cuántos clientes hay en cada país, construimos esta tabla dinámica

Para que la etiqueta del país se repita en todas las filas usamos Configuración de campo - Diseño e Impresión - Repetir etiquetas de elementos

Otros detalles importantes:

• dar a la tabla formato tabular (por defecto la tabla tiene el formato Compacto);
• quitar los Subtotales del campo País;
• quitar los Totales de las filas y las columnas.

Ahora vamos a construir una segunda tabla dinámica basada en la primera

No es indispensable ubicar la segunda tabla dinámica en la misma hoja como la primera, pero lo hacemos por comodidad.
En esta tabla dinámica ponemos el campo País en el área de las filas y el campo Cliente en el área de los datos. Como Clliente no es un campo numérico, Excel usa la función Cuenta lo que nos da el número de clientes únicos por país

Para completar nuestro modelo debemos agregarle dinamismo, es decir, la capacidad de ampliarse dinámicamente (en ingles: "scalabilty", la capacidad de ir acomodándose a incrementos de datos; la palabra "escalabilidad" no existe en castellano, por lo menos por ahora).
Para hacerlo tenemos que crear un rango dinámico que se refiera a la primer tabla dinámica. Si intentamos definir la tabla dinámica como Tabla (Insertar-Tabla) veremos que esto no es posible. Así que echaremos manos a las técnicas "tradicionales", creando un nombre que se refiera al rango en forma dinámica usando la INDICE y CONTARA. En nuestro ejemplo definimos el nombre "rngTablaDatos" que se refiere a esta fórmula:

=Hoja1!$A$3:INDICE(Hoja1!$C:$C,CONTARA(Hoja1!$A:$A)+2)

Otra mejora posible es crear un evento que actualize la tabla cada vez que se genera un cambio en la base de datos, como muestro en esta nota.

Como aplanar ("despivotear") una tabla de datos con dos niveles

Power Query es, sin lugar a dudas, la más útil de las herramientas que Microsoft agregó a Excel desde las tablas dinámicas. Mi primer post sobre Power Query mostró como  aplanar ("despivotear") tablas de datos con más facilidad que la técnica que usabamos antes de la aparición de esta herramienta.

La necesidad de "aplanar" o "despivotear" una tabla de datos surge cuando queremos organizar los datos de manera que tal que podamos explotar todas las posibilidades de las tablas dinámicas.

Una tabla de datos "clásica" tiene un solo nivel, es decir, una única línea de encabezamientos y una única columna de etiquetas, como el ejemplo que mostré en la nota mencionada

Pero también existen tablas con más de un "nivel", como ésta:

donde tenemos un nivel para el año y un segundo para los meses. Como si esto no fuera suficiente, tenemos una columna para las zonas y una segunda para las sucursales. Es decir, los datos que queremos "despivotear" están encapsulados entre dos filas y dos columnas de etiquetas. Para empeorar la situación tenemos tambien celdas combinadas.

Para poder usar todo el potencial de las tablas dinámicas necesitamos que los datos estén organizados de esta manera

Veamos como Power Query nos permite realizar la tarea con facilidad. El primer paso es cargar la tabla en la ventana del Power Query, para lo cual selecionamos alguna de las celdas de la tabla y usamos la opción "From Table"

Un detalle importante es quitar la marca de la opción "My table has headers" ("mi tabla tiene encabezamientos").
La ventana del Power Query se verá así

Empezamos por eliminar las columnas de Totales  (columna 6 y columna 10) para lo cual las seleccionamos, primero la 6 y luego la 10 apretando el botón Ctrl, y usando la opción Remove

Power Query no tiene la posibilidad de eliminar filas selectivamente, así que para eliminar los totales de las zonas, invertimos la tabla usando la opción Transpose

Ahora podemos eliminar los totales de las zonas. Antes de invertir nuevamente la tabla, vamos a completar las etiquetas que faltan en la columna 1 (año) usando la opción Fill

con este resultado

Vamos a combinar estas columnas en una única usando la opción Merge Columns, para lo cual debemos primero selecionar ambas columnas. Como separador usamos "espacio" y le damos a la nueva columna el nombre "Período"



El resultado es es siguiente

Ahora volvemos a invertir la tabla y rellanos los vacíos en la primer columna a la derecha (Zonas)

Ahora tenemos que promover la fila 1 al área de los encabezados, lo que hacemos con "Use First Row as Headers"

Nuestro próximo paso es "despivotear" (aplanar) las columnas que contienen los datos (2014 1, 2014 2, etc.) seleccionándolas  y usando el comando "Unpivot"

Esto genera dos columnas: Attribute, que contiene las etiquetas de las columnas seleccionadas y Value que contiene los valores (las ventas en nuestro ejemplo) de la tabla. También podemos ver que la primer columna no tiene encabezado.

Antes de ponner encabezados vamos a separar ("Split") la columna Attribute en dos: Mes y Año. Para eso usamos "Split Column by Delimiter"

Ahora ponemos los encabezamientos "Zona", "Sucursal" (en lugar de "Año Mes"),,, Año (en lugar de Attribute.1), Mes (en lugar de Attribute.2) y Ventas (en lugar de Value).

Podemos ver quel os nuevos campos Año y Mes son valores Texto, lo cual es inconveniente para nuestros objetivos. Para convertirlos en valores numéricos seleccionamos las collomnas y usamos el comando "Data Type"

con lo que concluimos nuestra tarea. Todo lo que nos queda por hacer es cargar la consulta como tabla en una hoja de Excel

Podemos hacer otra transformación combinando los campos Mes y Año com Merge Columns y usando "/" como separador

creando la columna "Fecha". Ahora podemos seleccionar esta columna y cambiar el tipo de dato a "Date" (fecha), removemos las columnas Año y Mes y  movemos la nueva columna Fecha a la derecha de Sucursal