Anular dinamización de columnas con PowerQuery

Este artículo te presenta la opción de anular dinamización de columnas con PowerQuery, una potente herramienta de tratamiento de datos incluida en Excel y, en general, gran desconocida.

En el artículo sobre Anular dinamización de columnas con VBA te mostré una forma de realizar esta operación con una macro y quedó pendiente realizar lo mismo con PowerQuery. Ahora vamos a ver como hacerlo y podrás elegir el método que mejor se adapte a tus necesidades.

Datos de partida

Sean nuestros datos los mostrados en la siguiente imagen, que nos da las ventas por artículo, mes a mes.

De nuevo, este formato está bien para su análisis final, pero si queremos disponer de ellos para cargarlos a una tabla dinámica o modelo de datos de PowerPivot para poder hacer determinados cálculos sobre ellos, este formato no puede ser procesado. Para ello, necesitamos tenerlos en un formato como el mostrado a consitnuación:

Puedes ver mas sobre el formato de datos adecuado en el artículo Análisis De Datos Con Tablas Dinámicas (I).

El editor de PowerQuery

Para modificar los datos, deberemos comenzar por abrir el editor de PowerQuery. Para ello, nos situaremos en cualquier punto dentro de nuestros datos e iremos a Datos y en Obtener y transformar datos, haremos click, en este caso, en Desde una tabla o rango.

Nos aparecerá una ventana para seleccionar el rango de nuestros datos y, una vez elegido el rango correcto, al hacer clikc en Aceptar nos mostrará la ventana del editor de PowerQuery con una previsualización de los datos.

El editor de PowerQuery nos muestra los datos en la ventana principal, el menú con las distintas opciones en la parte superior y, a la derecha, en PROPIEDADES, podemos ver el nombre que se asignará a la tabla de datos, una vez transformados y en PASOS APLICADOS, cada paso de modificación que vayamos haciendo sobre los datos originales.

De momento, solo muestra dos pasos: Origen y Tipo cambiado, pero cuando vayamos añadiendo pasos, esta ventana funciona a modo de navegador, de forma que los datos serán visualizados transformados con los pasos efectuados hasta el seleccionado.

Transformando los datos

Poner encabezados

Lo primero que haremos será poner los encabezados de los datos de forma correcta. Para ello, solo tenemos que hacer click en la opción de Inicio, Usar la primera fila como encabezado. Con ello, veremos que los encabezados iniciales son reemplazados por los valores que aparecían en la primera fila.

Esto, además, inserta un paso automáticamente, para adecuar los formatos de los datos a los detectados. Hay que tener cuidado con esto, porque la previsualización de PowerQwery no carga todos los datos cuando estos son muchos y, a veces, la detección de tipo de dato no es correcta. Es una buena practica repasar el formato de cada columna para confirmar que dichos tipos son correctos.

Eliminar columnas innecesarias

El siguiente paso será eliminar las columnas innecesarias. En este caso, la columna final de totales, que ahora aparece como Column15, ya que no tenia valor en la fila que hemos usado para el encabezado. Esta columna es innecesaria porque después podremos calcular en nuestra tabla dinámica o PowerPivot dicho total.

Para eliminarla, la seleccionamos y en Inicio, hacemos click en quitar columnas. Este botón tiene una flecha que permite desplegar las dos opciones de eliminación de columnas existentes. Una es Quitar columnas, que es la que hemos usado. Con ella, eliminaremos las columnas que tengamos seleccionadas. Pero también tenemos la opción de seleccionar las columnas a mantener y clicar el la opción Quitar otras columnas. La mas adecuada dependerá de cual nos lleve menos tiempo. Si queremos eliminar la mayoría de columnas, es mas sencillo seleccionar las pocas que queramos mantener, y eliminar el resto.

Anular dinamización

Este es el paso fundamental de nuestro proceso. El obetivo es mantener las columnas Cadena y Código-Nombre y hacer que se repitan para cada una de las demás, de forma que tengamos dichas columnas fijas, una columna para el mes de la cabecera y otra para las ventas de Cadena / Código-Nombre.

Será tan sencillo como seleccionar las dos primeras columnas (las fijas a repetir), hacer click con el botón derecho del ratón sobre la cabecera de cualquiera de las dos columnas y seleccionar la opción de Anular dinamización de otras columnas.

De forma mágica, los datos se han transformado en lo siguiente:

Como vemos, a cada Cadena / Código-Nombre le ha asignado los meses (Ene, Feb,..) y las unidades correspondientes. Las cabeceras de estas nuevas columnas aparecen como Atributo y Valor.

Cambiar el nombre de las nuevas columnas

Vamos a cambiar el nombre de las columnas Atributo y Valor por Fecha y Unidades respectivamente. Para ello, seleccionamos la columna Atributo, nos ponemos en la cabecera de la columna Atributo, hacemos click con el botón derecho y seleccionamos Cambiar nombre. Esto nos edita el título de la columna y podemos ya escribir Fecha. Seguiremos el mismo procedimiento con la columna Valor para renombrarla a Unidades.

Cambiar los nombres de mes por la fecha

Podríamos dejar así los datos. Todo dependerá de como los queramos utilizar después. De todas formas, si estamos haciendo esto, lo mas probable es que en vez de ver Ene queramos ver un valor de fecha con el que luego se pueda operar, así que, vamos a ver como hacerlo.

Tendremos que repetir el proceso para cada nombre de mes. Para modificar Ene por 31/01/2020, tendremos que seleccionar la columna que ahora es Fecha y la opción Inicio, Reemplazar los valores. Esto nos mostrará una ventana de diálogo donde escribiremos el valor que queremos reemplazar y por que valor lo queremos cambiar.

Modificar el tipo de datos de la columna Fecha

Cuando hayamos hecho todos los reemplazos de valores, deberemos tener en cuenta una última cosa. Si te fijas, el tipo que tiene la columna de fechas es ABC, es decir, texto. Pero lo que queremos es que sea un campo de tipo Fecha para poder operar con él.

Para cambiarlo, seleccionamos la columna y en Inicio, Transformar, elegimos el tipo de datos que queremos. En este caso, Fecha.

Vemos que ha hecho el cambio correctamente y nuestra columna Fecha ahora es del tipo que queríamos.

Cargar datos a nuestra hoja

Para finalizar, ya solo nos queda cargar los datos a nuestro libro de Excel para poder utilizarlos. LO haremos yendo a Cerrar y cargar.

Vemos que hay dos opciones: Cerrar y cargar o Cerrar y cargar en…

Es importante no equivocarse en este paso, porque no es reversible. Con la primera opción, los datos transformados serán cargados en una nueva hoja de nuestro libro, sobre una tabla que tendrá por nombre el que aparece en el campo PROPIEDADES.

La segunda opción, nos mostrará un cuadro de diálogo en el que podremos elegir varias cosas.

Vemos que nos permite elegir como queremos ver los datos en nuestro libro (Tabla, Informe de tabla dinámica, Gráfico dinámico o Crear solo conexión).

También nos permite elegir donde cargarlos, en el caso de que elijamos alguna de las tres primeras opciones.

Y, finalmente, nos deja cargar los datos transformados al Modelo de datos del libro.

En función de lo que estemos haciendo, nos interesará una u otra opción, pero especialmente, si estas generando un modelo de datos para después trabajarlo con PowerPivot, creando relaciones entre diferentes tablas y calculando KPI, lo mas aconsejable es seleccionar la opción de Crear solo conexión junto con la de Agregar estos datos al Modelo de datos.

De esta forma, tendrás todos los datos necesarios a tu disposición sin recargar excesivamente tu libro con un montón de hojas únicamente contenedoras de datos que nunca vas a mirar.

Conclusión

Ya has visto lo sencillo que es anular una dinamización de columnas con PowerQuery. Este es solo un pequeño ejemplo de la potencia de PowerQuery para manipular datos desde cualquier fuente y cargarlos a Excel.

Con PowerQuery puedes cargar datos desde otros ficheros (excel, csv, txt, …), desde múltiples archivos en un directorio, desde tablas de bases de datos (Access, SQL Server, …). Si echas un vistazo a las opciones bajo Obtener datos, te quedarás impresionado.

Las opciones son, prácticamente infinitas. Desde sencillas manipulaciones, como este ejemplo introductorio, hasta las cosas mas complejas.

Te recomiendo que curiosees un poco por dichas opciones y por el editor que hemos visto para que, al menos, conozcas las posibilidades. Si no las conoces, nunca las investigarás y te estarás perdiendo una gran herramienta.

Finalmente, solo decirte que estaré encantado de recibir tus comentarios o sugerencias o de intentar resolver tus dudas en el foro,