Power Query. Tabla dinámica a partir de varias hojas

dinamica_variashojas.xlsx

Descargar XLSX • 39KB

Tenemos tres hojas, cada una de ellas pertenece a una provincia, pero si nos fijamos en los datos la columna de provincia no está, solo están los datos, sabemos de qué provincia es por el nombre de la hoja.

Además, tenemos otra hoja llamada provincias, donde tenemos los nombres de las provincias.

El ejemplo, va a consistir en crear una tabla dinámica de las tres hojas, que lo haremos con power Query, y, después vamos a crear una columna personalizada con las provincias para poder filtrar por provincia desde la tabla dinámica.

Vamos a la primera hoja de la provincia, que es Albacete, con una celda dentro del modelo, vamos a la pestaña datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.

Aparece la ventana de crear tabla, donde debemos de asegurarnos, que está marcada la casilla, la tabla tiene encabezados, y, aceptamos.

Se abre power Query con la hoja de Albacete.

Cambiamos el nombre a la consulta, y, le ponemos Albacete.

Vemos que la fecha aparece junto con la hora, que es cero, pues vamos a poner la columna fecha, como solo fecha, para ello, hacemos clic en:

Y, seleccionamos fecha.

Aparece una ventana, donde nos indica, si vamos a sustituir el paso.

Marcamos sustituir la actual, y, ya tenemos la columna fecha, en formato solo de fecha.

Duplicamos la Query, haciendo clic con botón alternativo de ratón, sobre la consulta, y, seleccionamos duplicar.

En la ventana, pasos aplicados, seleccionamos el primer paso que es origen, donde vemos, en la barra de fórmulas, la ruta, vemos que como nombre aparece Albacete, pues nos vamos a traer la siguiente hoja, que es Cádiz.

Para ello, cambiamos Albacete por Cádiz.

Ya tenemos la siguiente hoja cargada en power Query.

Cambiamos el nombre de la Query, y, le ponemos Cádiz.

Hacemos lo mismo para traernos la tercera hoja.

Ahora, vamos a unir las tres Query en una.

Seleccionamos la primera Query, desde la pestaña de inicio, dentro del grupo combinar, desplegamos anexar, y, elegimos anexar en una nueva consulta.

Se abre la ventana de, anexar, pues, elegimos tres o más tablas.

Vemos dos ventanas, la ventana de la izquierda, llamada tablas disponibles, donde tenemos todas las consultas, y, la ventana de la derecha, llamada tablas para anexar, donde aparece la consulta que teníamos marcada.

Pasamos a la ventana tablas para anexar, las consultas faltantes, seleccionándola, y, haciendo clic en agregar .

Aceptamos, vemos en el panel de consultas, una consulta nueva, donde se han unido las tres tablas.

Cambiamos el nombre.

Cerramos y cargamos como una conexión, para ello, desde la pestaña de inicio, desplegamos cerrar y cargar, y, seleccionamos cerrar y cargar en.

Se abre la ventana, importar datos, pues, marcamos crear únicamente la conexión, y, aceptamos.

Si miramos en consultas, y, conexiones, veremos nuestras consultas.

Añadimos una hoja nueva.

Seleccionamos con el botón alternativo del ratón la consulta anexada, y, elegimos cargar en.

Se vuelve a abrir la ventana, importar datos, donde, elegimos informe de tabla dinámica, y, hoja de cálculo nueva.

Aceptamos.

Vamos a llevarnos a filas el centro comercial, y, a valores el total.

Esto está bien, pero tenemos los totales para las tres provincias, porque no tenemos una columna de provincia en cada hoja para poder filtrar.

Pero si tenemos una hoja con las provincias.

Pues, accedemos de nuevo a power Query.

Seleccionamos la primera consulta.

Vamos a la pestaña agregar columna, y, agregamos una columna personalizada.

Como nombre ponemos provincias, y, en la ventana de fórmulas ponemos entre comillas Albacete.

Aceptamos.

Esto lo hacemos para las otras dos consultas.

En las tres consultas, la columna se debe de llamar igual

Cerramos y cargamos.

Actualizamos la tabla dinámica.

Vemos que ya aparece la columna provincias.

Creamos una segmentación de datos, con una celda dentro de la tabla dinámica, vamos a la pestaña analizar tabla dinámica, dentro del grupo, filtrar, hacemos clic en segmentación de datos.

Se abre la ventana, insertar segmentación de datos, con todos los encabezados del modelo, pues, marcamos en encabezado de provincias.

Aceptamos.

La ponemos en tres columnas, para ello, seleccionamos la segmentación de datos, se habilita la pestaña segmentación, dentro del grupo botones, la ventana de columnas, la ponemos en 3.

Si segmentamos vemos como se filtra por la provincia que hemos seleccionado.

Pues ya lo tenemos, el ejemplo consistía en filtrar por provincia, donde no había provincia que seleccionar en ninguna de las hojas de las provincias.