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

martes, enero 26, 2016

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





4 comments:

José Manuel Agundis 27 enero, 2016 14:14  

Buen día mi estimado Jorge, felicidades por su gran ejemplo, muy bueno y útil de verdad para nosotros.
Saludos.

Antonio Ricaurte Rojas 27 enero, 2016 17:54  

Gracias por tamaña explicación. Julio, una pregunta: ¿Existe alguna posibilidad que todas tus enseñanzas de Excel las puedas vertir en videos? Creo que el aprendizaje sería mucho más productivo observando y escuchando en "vivo y en directo" tus instrucciones.

Jorge Dunkelman 27 enero, 2016 21:14  

Hola Antonio, lo tengo pensado y he publicado algunos videos en YouTube. Tienes razón que cunado se trata de técnicas intrincadas, como la de la nota, el video es mucho más efectivo. Veremos...

Santandereano1970 05 febrero, 2016 01:02  

Excelente Don Jorge... Muchas gracias

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP