Power Query: Cómo combinar archivos basados ​​en una lista

Power Query tiene una característica realmente potente que nos permite combinar todos los archivos de una carpeta . Sin embargo, a veces los archivos no están contenidos en una sola carpeta. A veces se encuentran dispersos en varios lugares. ¿Cómo podemos lidiar con eso? Eso es lo que estamos viendo en esta publicación: cómo combinar archivos según una lista.

Tabla de contenido

• Tabla de datos de origen
• Conéctese y transforme un solo libro de trabajo
• Reutilizar los pasos de transformación
• Ampliar los datos para combinar archivos.
• Realizar transformaciones adicionales más adelante
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0150 Lista de archivos de conexión de Power Query.xlsx

Tener acceso

Tabla de datos de origen

Primero, comencemos por hacer una Tabla de todos los archivos a combinar, incluidas las hojas o tablas de aquellos libros que queremos usar.

En este ejemplo, usaré hojas de trabajo, pero usted podría usar tablas o rangos con nombre.

Notará que las hojas de trabajo de cada libro tienen nombres diferentes; Esta suele ser una situación complicada de Power Query, pero no supone ningún problema para este método.

Cargue la tabla, que por ejemplo se llama SourceFiles, en Power Query.

Conéctese y transforme un solo libro de trabajo

Ahora necesitamos obtener el código para conectarnos a cada libro de trabajo. Supongo que no eres un experto en código M, por lo que solucionaremos esto sin escribir ningún código.

En Power Query, haga clic derecho en el panel Consultas y haga clic en Nuevas consultas desde archivo desde libro de trabajo .

Si su archivo es de un tipo diferente, utilice el conector correspondiente para ese archivo.

Navegue hasta uno de los archivos de la lista y haga clic en Importar .

En la ventana del Navegador, seleccione los datos de origen relevantes y luego haga clic en Aceptar .

A continuación, debemos realizar las transformaciones necesarias para ese único libro.

Por ejemplo:

• Se eliminaron las 4 filas superiores.
• Promocionó los encabezados
• Cambió el tipo de datos
• Filtrado para excluir valores nulos de la columna Código de cuenta

Aquí está la tabla final para un único libro de trabajo de los datos de ejemplo.

Reutilizar los pasos de transformación

En la consulta de un solo libro, haga clic en Ver editor avanzado .

Copie todo el código, luego haga clic en Listo para cerrar la ventana.

Vuelva a la consulta que enumera todos los archivos.

Haga clic en Agregar columna Columna personalizada

En el cuadro de diálogo Columna personalizada , asigne un nombre a la columna (he usado Datos ) y pegue el código que copiamos anteriormente en el cuadro de fórmula Columna personalizada .

Sólo necesitamos hacer algunos pequeños ajustes al código.

• Cuando la ruta del archivo esté codificada, cámbiela por el nombre de la columna que contiene la ruta del archivo.
• Cuando el nombre de la hoja esté codificado, cámbielo por el nombre de la columna que contiene el nombre de la hoja.

Para nuestro ejemplo. El código cambia de esto:

Source = Excel.Workbook(File.Contents("C:ExamplesData2022P12P12 2022 Trial Balance.xlsx"), null, true),Dec_Sheet = Source{[Item="Dec",Kind="Sheet"]}[Data],[insert code]

A esto:

Source = Excel.Workbook(File.Contents([File Path]), null, true),Dec_Sheet = Source{[Item=[Sheet Name],Kind="Sheet"]}[Data],

Haga clic en Aceptar para cerrar el cuadro de diálogo Columna personalizada.

La ventana de vista previa de Power Query ahora tiene este aspecto:

Ampliar los datos para combinar archivos.

Para simplificar, expandiremos las columnas usando el ícono Expandir. Si pudiera tener datos nuevos en algún momento, debe expandir las columnas dinámicamente. Hay una publicación de blog sobre ese método aquí: Cómo expandir columnas dinámicamente en Power Query

Haga clic en el icono de expandir . No queremos el nombre de la columna original como prefijo , así que desmárquelo y luego haga clic en Aceptar .

¡AUGE!

Eso es todo. ¡Ya terminaste!

Ahora podemos eliminar la consulta del libro único, ya que no sirve para nada.

Si agregamos más filas a la tabla SourceFiles, la consulta también incorporará esos archivos.

Realizar transformaciones adicionales más adelante

En algún momento, los datos de origen pueden cambiar y es posible que desee cambiar la consulta. ¿Entonces, qué podemos hacer?

• Cree una nueva consulta en blanco: haga clic con el botón derecho en el panel Consultas y haga clic en Nueva consulta Consulta en blanco de otras fuentes.
• Copie y pegue el código del paso de la columna Personalizado en la nueva consulta.
• Cambie las columnas [Ruta del archivo] y [Nombre de la hoja] nuevamente a valores codificados que hagan referencia a un archivo específico.
• Esto cambia la nueva consulta a una consulta estándar.
• Realice las modificaciones que desee en esa consulta.
• Finalmente, copie y pegue el código nuevamente en la columna personalizada.

Conclusión

Hemos visto que podemos combinar fácilmente todos los archivos en una lista usando Power Query. Se necesitan algunas modificaciones menores en el código generado automáticamente, pero nada que no podamos manejar. Ahora bien, no importa dónde estén los archivos, ni cómo se llamen las hojas; podemos combinarlos fácilmente en una única fuente de datos.

Artículos Relacionados:

• Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
• Cómo utilizar las funciones personalizadas de Power Query
• Cómo introducir datos en Power Query: 5 fuentes de datos comunes

---