Consolidar múltiples archivos Excel con Power Query desde carpeta

Un miembro necesitaba consolidar datos de múltiples archivos Excel almacenados en una carpeta. El reto adicional: cada archivo contenía varias tablas/hojas, lo que complicaba el proceso habitual de conexión desde carpeta en Power Query. Al intentar cargar desde origen carpeta, Power Query pedía seleccionar una tabla específica y no sabía cómo avanzar.

Varios miembros coincidieron al instante en la solución: Power Query con conexión a origen carpeta. Rafa explicó el proceso paso a paso para manejar archivos con múltiples hojas:

1. Conectar a carpeta en Power Query (Obtener datos → Desde carpeta)
2. Usar Excel.Workbook para expandir el contenido de cada archivo
3. Filtrar por Kind = "Sheet" para obtener solo las hojas de datos (ignorando nombres definidos y otros objetos)
4. Expandir las columnas de datos de cada hoja
5. Si todas las tablas tienen la misma estructura, la consolidación es directa

Nota clave: si los archivos tienen rangos de celdas nombrados, estos aparecerán como objetos adicionales. Filtrar por Kind = "Sheet" permite quedarse solo con las hojas reales.

Rafa compartió además un webinar completo de 1h 30min sobre técnicas de consolidación de archivos con Power Query, y Nacho aportó un video tutorial paso a paso del proceso básico.

Conceptos clave: Power Query, Excel.Workbook, Excel.CurrentWorkbook, conexión desde carpeta, filtrado por Kind, consolidación de datos.

El problema: una carpeta llena de Excel y cada uno con varias hojas

Consolidar varios ficheros Excel de una carpeta es de los primeros trucos que aprende cualquiera que se acerca a Power Query. Conectas desde carpeta, combinas, y listo.

Hasta que los ficheros tienen más de una hoja. Ahí el asistente automático se atasca: Power Query te pide que elijas una tabla concreta como muestra, y si cada archivo trae varias hojas con nombres distintos, no sabes qué contestar. Un miembro de la comunidad llegó al grupo justo en ese punto, con la ventana del conector abierta y sin saber por dónde seguir.

Varios miembros coincidieron al instante en el camino: conexión a origen carpeta, pero expandiendo el contenido a mano en lugar de dejar que el asistente decida.

La solución paso a paso

Rafa explicó el proceso completo:

1. Conectar a la carpeta. Obtener datos → Desde carpeta. Aquí no combines todavía: dale a Transformar datos para entrar al editor con la lista de ficheros en crudo. Verás una tabla con una fila por archivo y una columna de contenido binario.

2. Expandir cada libro con Excel.Workbook. En vez de usar el botón de combinar, añade una columna personalizada que aplique Excel.Workbook sobre la columna de contenido. Esa función abre el binario y devuelve una tabla con todo lo que hay dentro del libro: hojas, tablas, rangos con nombre y otros objetos.

3. Filtrar por Kind = "Sheet". Este es el paso que resuelve el problema original. Al expandir, cada objeto trae una columna Kind que dice qué es. Filtrando por hoja te quedas solo con las hojas reales de datos y descartas todo lo demás.

4. Expandir las columnas de datos. Ya con las hojas filtradas, expande la columna de datos de cada una y promociona la primera fila a encabezados si hace falta.

5. Consolidar. Si todas las hojas comparten estructura, en este punto ya tienes la tabla unificada. Si no, tocará normalizar antes de apilar.

El detalle que provoca la mayoría de los fallos

Si tus ficheros tienen rangos de celdas con nombre, aparecerán como objetos adicionales. Es la causa número uno de que una consolidación devuelva el doble de filas de las esperadas: el mismo rango de datos entra dos veces, una como hoja y otra como nombre definido.

Filtrar por Kind = "Sheet" lo corta de raíz. Y merece la pena hacerlo siempre, aunque hoy tus ficheros no tengan rangos con nombre, porque el día que alguien añada uno la consulta seguirá funcionando sin que tengas que investigar de dónde salen las filas duplicadas.

Lo mismo aplica si en algún libro hay tablas formateadas: aparecerán con un Kind distinto al de hoja, y el filtro las deja fuera. Si lo que quieres son precisamente las tablas y no las hojas, cambia el valor del filtro; lo importante es decidirlo tú y no dejarlo al azar.

Por qué el asistente automático no vale aquí

Cuando usas el botón de combinar del conector de carpeta, Power Query toma el primer archivo como plantilla y monta una función de transformación basada en él. Con ficheros de una sola hoja bien estructurados eso funciona de maravilla.

Con varias hojas por fichero, el asistente necesita que le señales una hoja concreta, y esa elección queda grabada en la función. Si en el resto de archivos la hoja se llama distinto, la combinación falla o trae vacíos. Por eso el enfoque manual con Excel.Workbook es más largo pero mucho más sólido: tú controlas qué se expande y qué se descarta, sin depender de que todos los libros sean idénticos.

Conceptos clave

  • Conexión desde carpeta — el punto de partida; conviene entrar a Transformar datos en lugar de combinar directamente.
  • Excel.Workbook — abre un binario de libro y devuelve todo su contenido como tabla: hojas, tablas y nombres definidos.
  • La columna Kind — identifica el tipo de cada objeto; filtrar por ella es lo que separa las hojas de datos del resto.
  • Excel.CurrentWorkbook — la variante para cuando los rangos o tablas están dentro del mismo libro; los reconoce todos de golpe sin crear conexiones una por una. Otro miembro lo aportó como tip complementario y ahorra muchísimo trabajo manual.
  • Promoción de encabezados — el paso que convierte la primera fila en nombres de columna una vez expandidos los datos.

Material extra del hilo

Rafa compartió un webinar completo de hora y media sobre técnicas de consolidación de archivos con Power Query, que entra en los casos raros: estructuras que no coinciden, ficheros corruptos en la carpeta, columnas que aparecen y desaparecen entre años.

Nacho aportó además un vídeo tutorial paso a paso del proceso básico, útil si es la primera vez que montas una consolidación desde carpeta y quieres ver el flujo completo antes de meterte con las hojas múltiples.

Conclusión

La consolidación desde carpeta es una de esas cosas que parecen resueltas con dos clics hasta que la realidad mete varias hojas por fichero. La respuesta no es abandonar Power Query, es bajar un nivel: expandir el contenido con Excel.Workbook y filtrar explícitamente lo que quieres.

Son tres pasos más, pero te dan una consulta que aguanta que los ficheros cambien, que alguien añada un rango con nombre o que las hojas se llamen distinto cada mes. En la comunidad de InflueXcel este caso sale casi todas las semanas, y siempre acaba en el mismo sitio: el filtro por tipo de objeto.

Más casos con estas funciones

Más contenido de Excel en InflueXcel