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