En este vídeo vamos a ver cómo consolidar hojas de cálculo diferentes.
Tenemos una hoja “Mike” con encabezados de País, Nombre, Fecha, ID e Importe. Igualmente tenemos a “Laura” y “Paul” con la misma configuración, 5 encabezados en cada hoja. Las tablas se llaman TablaMike, TablaLaura, TablaPaul. Vamos a consolidar las hojas de cálculo.
Vídeo
¿En qué consiste la consolidación de datos?
Consolidar datos consiste en unir o agrupar datos que tienen la misma estructura en una única tabla. En nuestro caso tenemos 3 tablas, con los datos de Mike, Laura y Paul. Veremos como unir, consolidar, las tres tablas en una utilizando Power Query.
Consolidando las tablas mediante una consulta
Queremos generar una query, una consulta, en la cual aparezcan las 3 tablas una encima de la otra, creando una única consulta para que nos permita trabajar con tablas dinámicas o cualquier cuestión que necesitemos.
Para ello vamos a cargar directamente una Consulta en blanco, desde Datos – Obtener datos – De otras fuentes – Consulta en blanco. Se abre el editor de Power Query totalmente vacío y escribimos en la barra de fórmulas Excel.CurrentWorkbook().
Con esta instrucción vamos a devolver los contenidos del libro actual de Excel, absolutamente de todas las tablas que tengamos en nuestro libro serán cargadas en Power Query.
Al aplicar Enter nos carga Content en el cual aparecen las tablas.
Si hacemos clic en la parte derecha vemos en la parte inferior vemos las tablas de Mike, Laura y Paul. En la parte de la derecha vemos los nombres de dichas tablas.
Expandimos la columna de Content. Queremos devolver todos los campos. No vamos a utilizar el nombre de la columna original como prefijo.
Al aceptar nos devuelve la Query, la consulta, con todas las filas, 32 filas, almacenadas una encima de otra, las 3 tablas cargadas fusionadas, consolidadas.
La columna de Name la podemos eliminar, clic derecho Quitar. La columna de Fecha vamos a cambiar el tipo a Fecha. Directamente podemos guardar la consulta.
Le asignamos un nombre, por ejemplo, Query. Cerrar y Cargar. Cerrar y cargar en. Vamos a guardarla en una hoja de cálculo nueva.
Depurando la consulta para evitar “datos duplicados”
Al guardar la consulta nos tenemos que dar cuenta en la parte inferior que hace algo extraño, deja una fila en blanco. Además, hay un problema. Si actualizamos la propia consulta sin haber cargado nada de actualizaciones en el resto de las hojas. Originalmente hay 33 filas cargadas y si actualizamos con clic derecho, Actualizar vemos como va a cargar 65 filas, está haciendo una especie de duplicado de datos.
La cuestión es que al actualizar la consulta se están actualizando las 3 tablas, pero además la propia consulta, es como si estuviéramos sumando lo que teníamos y además los propios valores iniciales.
Lo que tenemos que hacer es eliminar en los filtros iniciales, que no aparezca esta consulta filtrada.
Cargamos la query con doble clic. En el paso inicial, en Origen, vamos a realizar un filtro para que exclusivamente aparezcan en Name solamente los nombres de las tablas que además vemos que empiezan con la palabra Tabla y a su vez no esté luego teniendo en cuenta la propia Query que hemos generado. Hacemos un filtro, Filtro de texto, Que comienza por, insertamos un paso, y que se quede exclusivamente con las tablas que comienzan con la palabra Tabla. Aceptamos. Cerrar y Cargar.
Vemos como se actualiza la consulta y ya carga 32 filas.
Añadir nuevas tablas a los datos consolidados
Si ahora hacemos un clic derecho Actualizar ya no duplica la información. No sólo eso sino que se podrá agregar una nueva tabla, cuyo nombre empiece por la palabra Tabla.
Vamos a imaginar que viene un nuevo cliente o comercial, Eva. Añadimos un par de registros a la tabla de Eva con nuevos datos, sus propios datos.
Creamos una nueva tabla Ctrl+T y cargamos el nombre de la tabla, TablaEva. La clave es que como el nombre empieza con Tabla, ya va a incorporarla directamente en la Query, porque es justo lo que ha hecho el filtro.
Cuando cargamos la query, que originalmente tiene 32 filas, hacemos clic derecho, Actualizar y 34 filas aparecen, luego la query se actualiza correctamente.
Vemos que aparecen los datos del nuevo cliente que hemos incorporado.
Si tuviéramos que incorporar más hojas, simplemente tendríamos que tener en consideración que esas tablas comenzaran justo con esa palabra, la palabra “Tabla”. Bastaría que nos situáramos en la propia query, y clic derecho actualizar y se actualizaría correctamente. A partir de la query podríamos insertar sucesivas tablas dinámicas para ya tratar la información.
Esto ha sido todo en cuanto a cómo se pueden gestionar estos tratamientos con Power Query con el tema de cómo consolidar hojas de cálculo diferentes.
