3 Formas Efectivas de Unpivot en Excel, PowerQuery y Python

3 Formas Efectivas de Unpivot en Excel, PowerQuery y Python

Comparamos cómo anular la dinamización de columnas con Excel, Powerqiery y Python. Tres formas para conseguir el mismo resultado. ¿Con cuál te quedas?

Convertir las cabeceras de columna en valores es una de esas transformaciones que aparece constantemente cuando trabajamos con datos en Excel. Partimos de una tabla con varias columnas (por ejemplo n1, n2, n3) y queremos acabar con una estructura larga donde cada cabecera se repite junto a su valor correspondiente, manteniendo las columnas fijas como el nombre y la ciudad. Esta operación se conoce como unpivot (o "anular la dinamización de columnas"), y en este tutorial la resolvemos de tres formas distintas: con funciones de Excel y LAMBDA, con Power Query y con Python dentro de Excel.

El objetivo

Tenemos una tabla con columnas fijas (nombre y ciudad) y tres columnas de valores (n1, n2, n3). Queremos transformarla para que cada uno de esos valores aparezca en una fila propia, acompañado del nombre de la columna a la que pertenecía. El resultado apila todos los datos: primero la cabecera de la primera columna con sus valores, luego la segunda y finalmente la tercera.

Opción 1: función personalizada con LAMBDA

La primera vía construye una función propia con dos parámetros: las columnas fijas y las columnas que se deben transformar. La idea es apilar todas las columnas de valores una debajo de otra y añadir el nombre de columna correspondiente.

El motor de esta lógica es la función REDUCE, que recorre una matriz acumulando un resultado:

  • Se parte de un valor inicial (por ejemplo, una cadena vacía).
  • Se le pasa un array (como SECUENCIA(3) para tres columnas).
  • Dentro de la LAMBDA se trabaja con el valor previo y el valor de la iteración. Tras cada paso, el resultado pasa a ser el "previo".

REDUCE no solo acumula números o textos: también permite ir generando rangos como salida. Combinándolo con varias funciones obtenemos el unpivot:

  • APILARV para colocar las columnas una debajo de otra de forma vertical.
  • ELEGIRCOLS para escoger, en cada iteración, la columna concreta de la matriz que toca procesar (la columna indicada por el número de la iteración).
  • Una construcción con SI aplicada a una SECUENCIA para propagar un valor fijo y repetir el nombre de la cabecera tantas veces como filas tenga la columna. Usando SECUENCIA(FILAS(...)) se generan tantas repeticiones como valores haya.
  • APILARH para añadir de forma horizontal, en cada fila, los valores fijos, el nombre de columna repetido y el valor que corresponde, en lugar de fijar una cabecera concreta.

La función final ya preparada sigue exactamente esta lógica. Internamente desglosa las dos matrices de entrada, detecta el número de columnas a trasponer con SECUENCIA(COLUMNAS(...)) (en lugar de un 3 fijo), separa cabeceras y valores de la parte fija usando TOMAR y EXCLUIR (para quitar la primera fila), aplica la lógica línea a línea sobre las columnas a transformar y, por último, añade las cabeceras al resultado apilado.

Una vez tenemos la LAMBDA lista, podemos convertirla en una función reutilizable: copiamos la fórmula (excluyendo los datos finales de prueba), vamos a Fórmulas → Nombres definidos → Asignar nombre, le ponemos un nombre como UNPIVOTAR y pegamos la función. A partir de ahí basta con llamarla indicando la parte fija y la parte variable para obtener el resultado al instante.

Opción 2: Power Query

Power Query consigue el mismo resultado con mucho menos esfuerzo, porque la lógica ya viene integrada:

  1. Convertimos los datos en tabla y los cargamos en Power Query (Datos → De una tabla o rango).
  2. Seleccionamos las tres columnas que queremos transformar (n1, n2, n3).
  3. Elegimos la opción Anular la dinamización de columnas, que genera directamente el resultado buscado.
  4. Cerramos y cargamos, y obtenemos el resultado en formato tabla.

La gran ventaja es la rapidez: toda la lógica está condensada en una sola opción. El detalle a recordar es que, al estar conectado a la tabla origen, hay que actualizar la consulta para que recoja los cambios que hagamos en los datos. Como contrapartida positiva, el resultado se entrega ya en formato tabla.

Opción 3: Python dentro de Excel

La tercera opción usa Python en Excel, disponible de momento para el canal Insider. Se escribe código directamente en una celda con =py, lo que cambia el formato de la celda y permite introducir código.

El flujo es el siguiente:

  • Referenciamos la tabla de Excel con xl(...), que recupera el rango completo incluyendo las cabeceras.
  • Usamos la librería pandas (precargada y accesible como pd) y, en concreto, la función pd.melt, que realiza precisamente este tipo de transformación.
  • A pd.melt le indicamos:
  • id_vars: la lista de columnas fijas, entre corchetes y comillas (por ejemplo ["nombre", "ciudad"]).
  • value_vars: la lista de columnas a transformar (["n1", "n2", "n3"]).
  • Ejecutamos el código con Control + Intro.

El resultado se devuelve primero como un objeto de Python. Con la opción de mostrar valores de Excel, lo vemos en las celdas exactamente igual que con los otros dos métodos. La nomenclatura y la lógica recuerdan bastante al lenguaje M de Power Query.

Conclusión

Hemos resuelto el mismo problema de unpivot por tres caminos distintos. La función con LAMBDA exige montar la lógica, pero deja una herramienta reutilizable y totalmente bajo control. Power Query es la opción más rápida y entrega el resultado en formato tabla. Python con pd.melt ofrece una solución muy compacta para quienes ya manejan pandas. Tres enfoques válidos para que elijas el que mejor encaje con tu flujo de trabajo.

Más contenido de Excel en InflueXcel