Combinar datos de varios libros de trabajo en Excel (con Power Query)

Power Query puede ser de gran ayuda cuando desee combinar varios libros de trabajo en un solo libro de trabajo.

Por ejemplo, suponga que tiene los datos de ventas para diferentes regiones (Este, Oeste, Norte y Sur). Puede combinar estos datos de diferentes libros de trabajo en una sola hoja de trabajo con Power Query.

Si tiene estos libros de trabajo en diferentes ubicaciones / carpetas, es una buena idea moverlos todos a una sola carpeta (o crear una copia y poner esa copia del libro de trabajo en la misma carpeta).

Entonces, para empezar, tengo cuatro libros de trabajo en una carpeta (como se muestra a continuación).

Ahora, en este tutorial, estoy cubriendo tres escenarios en los que puede combinar los datos de diferentes libros de trabajo usando Power Query:

• Cada libro tiene los datos en una tabla de Excel y todos los nombres de las tablas son iguales.
• Cada libro tiene los datos con el mismo nombre de hoja de trabajo. Este puede ser el caso cuando hay una hoja llamada "resumen" o "datos" en todos los libros de trabajo y desea combinar todos estos.
• Cada libro tiene muchas hojas y tablas, y desea combinar tablas / hojas específicas. Este método también puede resultar útil cuando desee combinar tablas / hojas que no tengan un nombre coherente.

Veamos cómo combinar los datos de estos libros de trabajo en cada caso.

Cada libro tiene los datos en una tabla de Excel con la misma estructura.

La siguiente técnica funcionaría cuando sus tablas de Excel se hayan estructurado de la misma manera (los mismos nombres de columna).

El número de filas de cada tabla puede variar.

No se preocupe si algunas de las tablas de Excel tienen columnas adicionales. Puede elegir una de las tablas como plantilla (o como la "clave", como la llama Power Query), y Power Query la usaría para combinar todas las demás tablas de Excel con ella.

En caso de que haya columnas adicionales en otras tablas, se ignorarán y solo se combinarán las especificadas en la plantilla / clave. Por ejemplo, si la plantilla / tabla de claves que selecciona tiene 5 columnas y una de las tablas de algún otro libro tiene 2 columnas adicionales, esas columnas adicionales se ignorarán.

Ahora tengo cuatro libros de trabajo en una carpeta que quiero combinar.

A continuación se muestra una instantánea de la tabla que tengo en uno de los libros de trabajo.

Estos son los pasos para combinar los datos de estos libros de trabajo en un solo libro de trabajo (como una sola tabla).

1. Vaya a la pestaña Datos.
2. En el grupo Obtener y transformar, haga clic en el menú desplegable Nueva consulta.
3. Coloca el cursor sobre "Desde archivo" y haz clic en "Desde carpeta".
4. En el cuadro de diálogo Carpeta, ingrese la ruta del archivo de la carpeta que tiene los archivos, o haga clic en Examinar y ubique la carpeta.
5. Haga clic en Aceptar.
6. En el cuadro de diálogo que se abre, haga clic en el botón Combinar.
7. Haga clic en "Combinar y cargar".
8. En el cuadro de diálogo "Combinar archivos" que se abre, seleccione la Tabla en el panel izquierdo. Tenga en cuenta que Power Query le muestra la tabla del primer archivo. Este archivo actuaría como plantilla (o clave) para combinar otros archivos. Power Query buscaría ahora la "Tabla 1" en otros libros de trabajo y la combinaría con este.
9. Haga clic en Aceptar.

Esto cargará el resultado final (datos combinados) en su hoja de trabajo activa.

Tenga en cuenta que, junto con los datos, Power Query agrega automáticamente el nombre del libro de trabajo como la primera columna de los datos combinados. Esto ayuda a realizar un seguimiento de qué datos provienen de qué libro de trabajo.

En caso de que desee editar primero los datos antes de cargarlos en Excel, en el paso 6, seleccione "Combinar y editar". Esto abrirá el resultado final en el editor de Power Query donde puede editar los datos.

Algunas cosas que debes saber:

• Si selecciona una tabla de Excel como plantilla (en el paso 7), Power Query utilizará los nombres de las columnas de esta tabla para combinar los datos de otras tablas. Si otras tablas tienen columnas adicionales, se ignorarán. En caso de que esas otras tablas no tengan una columna, que está en su tabla de plantilla, Power Query simplemente pondría "nulo" para ella.
• No es necesario que las columnas estén en el mismo orden, ya que Power Query usa encabezados de columna para asignar columnas.
• Como ha seleccionado Table1 como clave, Power Query buscará Table1 en todos los libros de trabajo y combinará todos estos. En caso de que no encuentre una tabla de Excel con el mismo nombre (Tabla1 en este ejemplo), Power Query le dará un error.

Agregar nuevos archivos a la carpeta

Ahora, tomemos un minuto y comprendamos lo que hicimos con los pasos anteriores (que solo nos tomaron unos segundos).

Combinamos los datos de cuatro libros de trabajo diferentes en una sola tabla en unos segundos sin ni siquiera abrir ninguno de los libros de trabajo.

Pero eso no es todo.

El verdadero PODER de Power Query es que ahora, cuando agrega más archivos a la carpeta, no es necesario que repita ninguno de estos pasos.

Todo lo que necesita hacer es mover el nuevo libro de trabajo en la carpeta, actualizar la consulta y automáticamente combinará los datos de todos los libros de trabajo en esa carpeta.

Por ejemplo, en el ejemplo anterior, si agrego un nuevo libro de trabajo: "Mid-West.xlsx" a la carpeta y actualice la consulta, instantáneamente me dará el nuevo conjunto de datos combinado.

Así es como se actualiza una consulta:

• Haga clic con el botón derecho en la tabla de Excel que cargó en la hoja de trabajo y haga clic en Actualizar.
• Haga clic con el botón derecho en la consulta en el panel "Consulta del libro de trabajo" y haga clic en Actualizar.
• Vaya a la pestaña Datos y haga clic en Actualizar.

Cada libro tiene los datos con el mismo nombre de hoja de trabajo

En caso de que no tenga los datos en una tabla de Excel, pero todos los nombres de las hojas (de las que desea combinar los datos) sean iguales, puede utilizar el método que se muestra en esta sección.

Hay algunas cosas con las que debe tener cuidado cuando se trata solo de datos tabulares y no de una tabla de Excel.

• Los nombres de las hojas de trabajo deben ser los mismos. Esto ayudará a Power Query a revisar sus libros de trabajo y combinar los datos de las hojas de trabajo que tienen el mismo nombre en cada libro de trabajo.
• Power Query distingue entre mayúsculas y minúsculas. Esto significa que una hoja de trabajo llamada "datos" y "Datos" se consideran diferentes. Del mismo modo, una columna con el encabezado "Tienda" y una con "tienda" se consideran diferentes.
• Si bien es importante tener los mismos encabezados de columna, no es importante tener el mismo orden. Si la columna 2 en "East.xlsx" es la columna 4 en "West.xlsx", Power Query la hará coincidir correctamente asignando los encabezados.

Ahora veamos cómo combinar rápidamente datos de diferentes libros de trabajo donde el nombre de la hoja de trabajo es el mismo.

En este ejemplo, tengo una carpeta con cuatro archivos.

En cada libro de trabajo, tengo una hoja de trabajo con el nombre "Datos" que contiene los datos en el siguiente formato (tenga en cuenta que esta no es una tabla de Excel).

Estos son los pasos para combinar datos de varios libros de trabajo en una sola hoja de trabajo:

1. Vaya a la pestaña Datos.
2. En el grupo Obtener y transformar, haga clic en el menú desplegable Nueva consulta.
3. Coloca el cursor sobre "Desde archivo" y haz clic en "Desde carpeta".
4. En el cuadro de diálogo Carpeta, ingrese la ruta del archivo de la carpeta que tiene los archivos, o haga clic en Examinar y ubique la carpeta.
5. Haga clic en Aceptar.
6. En el cuadro de diálogo que se abre, haga clic en el botón Combinar.
7. Haga clic en "Combinar y cargar".
8. En el cuadro de diálogo "Combinar archivos" que se abre, seleccione "Datos" en el panel izquierdo. Tenga en cuenta que Power Query le muestra el nombre de la hoja de trabajo del primer archivo. Este archivo actuaría como clave / plantilla para combinar otros archivos. Power Query revisará cada libro de trabajo, buscará la hoja denominada "Datos y combinará todos estos".
9. Haga clic en Aceptar. Ahora Power Query revisará cada libro de trabajo, buscará la hoja de trabajo denominada "Datos" y luego combinará todos estos conjuntos de datos.

Esto cargará el resultado final (datos combinados) en su hoja de trabajo activa.

En caso de que desee editar primero los datos antes de cargarlos en Excel, en el paso 6, seleccione "Combinar y editar". Esto abrirá el resultado final en el editor de Power Query donde puede editar los datos.

Cada libro tiene los datos con diferentes nombres de tabla o nombres de hoja

A veces, es posible que no obtenga datos estructurados y consistentes (como tablas con el mismo nombre u hoja de trabajo con el mismo nombre).

Por ejemplo, suponga que obtiene los datos de alguien que creó estos conjuntos de datos pero nombró las hojas de trabajo como Datos del Este, Datos del Oeste, Datos del Norte y Datos del Sur.

O la persona puede haber creado tablas de Excel, pero con nombres diferentes.

En tales casos, aún puede usar Power Query, pero debe hacerlo con un par de pasos adicionales.

1. Vaya a la pestaña Datos.
2. En el grupo Obtener y transformar, haga clic en el menú desplegable Nueva consulta.
3. Coloca el cursor sobre "Desde archivo" y haz clic en "Desde carpeta".
4. En el cuadro de diálogo Carpeta, ingrese la ruta del archivo de la carpeta que tiene los archivos, o haga clic en Examinar y ubique la carpeta.
5. Haga clic en Aceptar.
6. En el cuadro de diálogo que se abre, haga clic en el botón Editar. Esto abrirá el editor de Power Query donde verá los detalles de todos los archivos en la carpeta.
7. Mantenga presionada la tecla Control y seleccione las columnas "Contenido" y "Nombre", haga clic con el botón derecho y seleccione "Eliminar otras columnas". Esto eliminará todas las demás columnas excepto las columnas seleccionadas.
8. En la cinta del Editor de consultas, haga clic en "Agregar columna" y luego haga clic en "Columna personalizada".
9. En el cuadro de diálogo Agregar columna personalizada, asigne un nombre a la nueva columna como "Importación de datos" y utilice la siguiente fórmula = Excel.Workbook ([CONTENIDO]). Tenga en cuenta que esta fórmula distingue entre mayúsculas y minúsculas y debe ingresarla exactamente como se muestra aquí.
10. Ahora verá una nueva columna que tiene Tabla escrita en ella. Ahora déjame explicarte lo que pasó aquí. Proporcionó a Power Query los nombres de los libros de trabajo, y Power Query ha obtenido los objetos, como hojas de trabajo, tablas y rangos de nombres de cada libro de trabajo (que reside en la celda Tabla a partir de ahora). Puede hacer clic en el espacio en blanco junto a la tabla de texto y verá la información en la parte inferior. En este caso, dado que solo tenemos una tabla y una hoja de trabajo en cada libro de trabajo, puede ver solo dos filas.
11. Haga clic en el icono de doble flecha en la parte superior de la columna "Importación de datos".
12. En el cuadro de datos de la columna que se abre, desmarque la casilla "Usar columna original como prefijo" y luego haga clic en Aceptar.
13. Ahora verá una tabla expandida donde verá una fila para cada objeto en la tabla. En este caso, para cada libro de trabajo, el objeto de hoja y el objeto de tabla se enumeran por separado.
14. En la columna Tipo, filtre la lista para mostrar solo la Tabla.
15. Mantenga presionada la tecla de control y seleccione la columna Nombre y datos. Ahora, haga clic derecho y elimine todas las demás columnas.
16. En la columna Datos, haga clic en el icono de doble flecha en la parte superior derecha del Encabezado de datos.
17. En el cuadro de datos de la columna que se abre, haga clic en Aceptar. Esto combinará los datos en todas las tablas y se mostrará en Power Query.
18. Ahora puede realizar cualquier transformación que necesite y luego ir a la pestaña Inicio y hacer clic en Cerrar y cargar.

Ahora déjeme intentar explicar rápidamente lo que hicimos aquí. Como no había coherencia en los nombres de las hojas o de las tablas, usamos la fórmula = Excel.Workbook para buscar todos los objetos de los libros en Power Query. Estos objetos pueden incluir hojas, tablas y rangos con nombre. Una vez que tuvimos todos los objetos de todos los archivos, los filtramos para considerar solo las tablas de Excel. Luego expandimos los datos en las tablas y combinamos todos estos.

En este ejemplo, filtramos los datos para usar solo tablas de Excel (en el paso 13). En caso de que desee combinar hojas y no tablas, puede filtrar hojas.

Nota: esta técnica le proporcionará los datos combinados incluso cuando haya una discrepancia en los nombres de las columnas. Por ejemplo, si en East.xlsx, tiene una columna mal escrita, terminará con 5 columnas. Power Query completará los datos en columnas si los encuentra, y si no puede encontrar una columna, informará el valor como "nulo".

Del mismo modo, si tiene algunas columnas adicionales en cualquiera de las hojas de trabajo de las tablas, estas se incluirán en el resultado final.

Ahora, si obtiene más libros de trabajo de los que necesita combinar datos, simplemente cópielos y péguelos en la carpeta y actualice Power Query