Crear modelo tabular a partir de modelo con varios encabezados

ejemplo.rar

Descargar RAR • 25KB

Vamos a ver un ejemplo, donde tenemos que transponer diversas columnas, ero, que tienen un encabezado como este.

Queremos convertir este modelo, en un modelo tabular, para que resulte más fácil, poder trabajar dentro de una tabla dinámica.

La idea, es que una vez terminado el ejemplo, si añadimos nuevos meses, estos se actualicen.

En el modelo, tenemos tres centros comerciales, en cada centro, tenemos la venta de productos para los meses de enero, febrero, marzo, y, abril, para el año 2020, y, el año 2019, junto con sus totales.

Queremos transformar estos datos, en un modelo tabular.

Sabemos que un modelo de datos no puede estar formado por columnas que hagan referencia a los mismo, como es el caso, tenemos varias columnas de meses, y, estas columnas, deberían de estar en filas.

Los totales, debemos de excluirlos de nuestro modelo final, es decir, tenemos las ventas de productos de un centro, y, al final, una línea de totales.

Si nos fijamos en los encabezados, año 2020, y, año 2019, podemos ver que esta centrado, con los meses.

Pero, no podemos usar combinar y centrar, porque, en ese caso, de trabajar en tabla, estas celdas se separarán, para ello, he realizado lo siguiente, selecciono las cuatro celdas, pulsamos CTRL mas 1, para ir a formato de celdas, hacemos clic en la pestaña de alineación, en alineación del texto, desplegamos la ventana horizontal, y, seleccionamos centrar en la selección.

Con este paso, conseguimos que el titulo siempre este centrado, pero cada celda, sigue siendo independiente, si añadimos, o, , quitamos columnas, se volverá a centrar.

Hacemos lo mismo para el año 2019.

Podemos trabajar en rango, o, en tabla.

Voy a trabajar en tabla, para ello, selecciono todos los datos, y, una fila más en la parte de arriba.

Ahora, explicare porque, pulsamos CTRL más T, para ir a la ventana de crear tabla, donde tenemos que marcar, la tabla tiene encabezados.

Vemos que se ha creado la tabla, donde esa fila de más, que hemos puesto arriba, ha colocado los encabezados, esto lo he hecho, porque el encabezado año 2020, y, año 2019, consta de cuatro celdas.

Pero, no necesitamos esa fila de encabezados, para ello, con una celda dentro de la tabla, vamos a la pestaña diseño de tabla, dentro del grupo, opciones de estilo de tabla, desmarcamos, fila de encabezado.

También, vamos a cambiar el nombre de la tabla, le vamos a poner datos.

Ya tenemos nuestro modelo preparado, para llevarlo, a Power Query, para ello, con una celda dentro de la tabla, vamos a la pestaña datos, dentro grupo, obtener y transformar, hacemos clic en de una tabla o rango.

Ya tenemos el modelo en Power Query, pero, podemos fijarnos, que el encabezado de año 2020, y, año 2021, ha desaparecido.

Si vamos a la ventana de pasos aplicados, podemos ver que después del paso origen, tenemos el paso encabezados promovidos, pues ha sido en dicho paso, donde se han quitado los encabezados de año 2020, y, año 2019.

Pues, vamos a eliminar el paso de encabezados promovidos, y, el paso de tipo cambiado, nos quedamos solo con el paso de origen, para eliminarlo, ya sabemos que debemos de hacer clic en la X a la izquierda del paso.

Ya vemos los encabezados de los años.

Vemos que ellos centros comerciales, solo aparecen una vez, el resto aparecen con valores nulos, y, debemos de rellenarlos.

Para ello, seleccionamos la columna, con botón alternativo de ratón, desplegamos, rellenar, y, elegimos abajo.

Ya lo tenemos.

Aun no podemos hacer, unpivoting, o, anulación de dinamización de columnas, porque, tenemos varios encabezados.

Lo siguiente que vamos a hacer, es unir las columnas de centros, y, productos, para ello, selecciono ambas columnas, voy a la pestaña de transformar, dentro del grupo, columna de texto, hago clic en combinar consultas.

Se abre la ventana de combinar consultas, donde como separador, voy a poner la coma, y, dejo el nombre predeterminado.

Aceptamos, y, la tenemos unida en una sola columna.

Lo siguiente que vamos a hacer es, transponer, para ello, de nuevo en la pestaña de transformar, dentro del grupo, tabla, hacemos clic en transponer.

Vemos, que en vertical, tenemos los encabezados de año 2020, y, año 2019, en la siguiente columna, tenemos los meses, y, en las siguientes columnas las cantidades.

El siguiente paso, es rellenar los valores nulos, que tenemos aquí:

Igual que antes, hacemos clic con botón alternativo de ratón sobre el encabezado, desplegamos rellenar, y, seleccionamos abajo.

Ya lo tenemos.

Ya tenemos creado un encabezado, de una sola columna, pues, vamos a promover el encabezado, para ello, desde la pestaña transformar, dentro del grupo, tabla, hacemos clic en, usar la primera fila como encabezado.

Ya podemos hacer el unpivoting, o, en este caso, anulación de dinamización de otras columnas, para ello, teniendo seleccionada la primera, y, segunda columna, hacemos clic con botón alternativo de ratón, y, seleccionamos, anulación de dinamización de otras columnas.

Ya esta todo mejor, lo siguiente va a ser, separar las columnas, que unimos anteriormente, para ello, seleccionamos la columna, desde la pestaña transformar, dentro del grupo, columna de texto, desplegamos, dividir columna, y, seleccionamos por delimitador.

Se abre la ventana, dividir columna por delimitador, donde ya aparece como delimitador, la coma, y, marcado, en cada aparición del delimitador, pues, aceptamos.

Ya la tenemos dividida.

Lo siguiente que vamos a hacer, es dar un nombre a los encabezados.

Debemos de recordar, que tenemos que quitar las líneas de totales, para ello, seleccionamos la columna de producto, que es donde aparece la palabra de total, hacemos clic en el icono de filtro.

Desplegamos filtros de texto, en la ventana de la derecha, seleccionamos, contiene.

Se abre la ventana, filtrar filas, donde en la ventana de la derecha de contiene, ponemos “Tot”, y, aceptamos.

Ya no aparecen las líneas de totales.

Lo siguiente va a ser cerrar y cargar, para ello, desde la pestaña de inicio, hacemos clic en el siguiente icono.

Ya tenemos el modelo, cargado en Excel, en formato de tabla.

Lo siguiente, que vamos a probar, es que si añadimos datos nuevos, estos deben de aparecer en nuestro modelo.

Podemos ver que tengo en dos columnas, los datos del mes de mayo, para el año 2020, y, 2019, pero, sin los totales.

Voy a insertar una columna, en el modelo original, después del mes de abril, del año 2020, selecciono los datos del mes de mayo de 2020, los copio, y, los pego en la columna que acabo de añadir.

Hago lo mismo, para mayo del año 2019.

Vamos a nuestra tabla, dentro de una celda de la tabla, hacemos clic con botón alternativo de ratón, y, seleccionamos actualizar.

Podemos ver, como aparece, el mes de mayo.

Pues y hemos conseguido lo que queríamos, que era crear un modelo con mas de un encabezado, en un modelo tabular, a partir, de aquí podemos crear tablas dinámicas.