Combinar varias hojas de varios libros de trabajo

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Escribí una serie de posts en los que expliqué diferentes métodos para combinar datos de varios archivos en un solo libro / tabla usando Power Query. En el primer post, expliqué cómo crear una función personalizada cuando desea remodelar los datos en los archivos de origen. En el segundo post, cubrí la combinación de datos de varios archivos CSV en una carpeta. En el tercer post de la serie, he mostrado cómo combinar datos de varios libros de Excel usando un enfoque distinto.

Con el tiempo, Microsoft ha implementado muchas mejoras en el uso de Power Query, incluida la capacidad de combinar automáticamente el contenido de varios libros de trabajo. Con la función de combinación puedes

  • combinar datos en varias hojas de trabajo en una sola tabla
  • combinar una sola hoja de varios libros de trabajo en una sola tabla

Hoy quiero mostrarle una forma en que puede hacer ambas cosas al mismo tiempo, dicho de otra forma, cómo combinar datos que están en varias hojas de trabajo y además en varios libros de Excel en una sola tabla usando Power Query. Todos los libros de trabajo deben estar en la misma carpeta y los datos de todas las hojas de trabajo y los libros de trabajo que deben combinarse deben estar en el mismo formato. A pesar de esto, es factible que los libros de trabajo tengan

  • distinto número de hojas de trabajo
  • hojas de trabajo que no son imprescindibles para el procesamiento que se pueden excluir dentro de la consulta

El método

El método que muestro tiene dos partes.

  • Crea 2 funciones
      • una función para extraer las hojas de trabajo válidas de un workbook
      • una función para remodelar los datos en una sola hoja de trabajo según sea necesario
  • Posteriormente extraigo los datos usando estas 2 funciones de todos los libros de Excel en la carpeta

Video de Youtube

Puede ver cómo se ejecuta cada uno de estos pasos usando la interfaz de usuario de Power Query con el ajuste ocasional del código de idioma ‘M’ que se produce en el siguiente video. Además he proporcionado un resumen de alto nivel de los pasos a continuación.

Función para extraer todas las hojas de trabajo válidas de un workbook

Los pasos a seguir son

  • Obtenga datos de un archivo de Excel (este será el archivo de muestra para la función).
    • Utilice la opción «Transformar datos» para cargar una lista de hojas en el Editor de Power Query.
    • Filtre la tabla para retener solo las hojas válidas que desea transformar.
  • A continuación, cree dos parámetros: la ruta del archivo y el nombre del archivo. Los utilizará para crear una consulta parametrizada.
    • Edite el paso de origen para que utilice los parámetros de ruta y nombre de archivo.
  • Convierta la consulta en una función.
  • Llamar a la función fnAllSheetNames.

Esta función extrae todas las hojas válidas de un workbook dada la ruta del archivo y el nombre del archivo.

Función para remodelar los datos en una sola hoja de trabajo según sea necesario

Los pasos a seguir son

  • Utilice el mismo archivo de muestra que el anterior. Por lo tanto crea un duplicado de la misma consulta.
  • Extraiga la primera hoja de trabajo del workbook.
  • Cree un nuevo parámetro y llámelo SheetName.
  • Edite la consulta y conviértala en genérica para utilizar el parámetro.
  • Ahora la consulta funciona con 3 parámetros: ruta de archivo, nombre de archivo y nombre de hoja.
  • Convierta la consulta en una función.
  • Llamar a la función fnProcessSheets.

Extraiga datos usando las 2 funciones de todos los libros de Excel en la carpeta

Los pasos a seguir son

  • Obtener datos de la carpeta.
  • Seleccione Transformar datos y cargue la lista de todos los libros de trabajo en Power Query.
  • Mantenga 2 columnas: una con nombres de archivo y la otra con nombres de ruta de archivo (elimine las otras).
  • Agregue una nueva columna usando Invocar función personalizada.
  • Utiliza la función fnAllSheetNames y pase los nombres de las 2 columnas como parámetros.
  • Extraiga los nombres de las hojas expandiendo el «Elemento».

Esto proporciona todas las hojas válidas de todos los archivos de la carpeta. A continuación, necesitamos extraer datos de todas estas hojas de trabajo.

  • Agregue una columna usando Invocar función personalizada.
  • Utiliza la función fnProcessSheets y pasar los 3 parámetros.

Tiene todos los datos de todas las hojas de trabajo válidas de todos los libros de trabajo en la carpeta.

¿Quiere aprender Power Query?

Si desea una lección completa acerca de cómo utilizar Power Query, consulte mi curso de capacitación online para obtener detalles y videos de vista previa.

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.