Cree tablas con diferentes encabezados de varios libros

Formulación del problema

Tenemos varios archivos (en nuestro ejemplo, 4 piezas, en el caso general, tantos como desee) en una carpeta Informes:

En el interior, estos archivos se ven así:

Donde:

• La hoja de datos que necesitamos siempre se llama Fotos, pero puede estar en cualquier parte del libro.
  
• Más allá de la hoja Fotos Cada libro puede tener otras hojas.
• Las tablas con datos tienen un número diferente de filas y pueden comenzar con una fila diferente en la hoja de cálculo.
• Los nombres de las mismas columnas en diferentes tablas pueden diferir (por ejemplo, Cantidad = Cantidad = Cantidad).
• Las columnas de las tablas se pueden organizar en un orden diferente.

Tarea: recopilar datos de ventas de todos los archivos de la hoja Fotos en una tabla común para luego construir un resumen o cualquier otro análisis sobre ella.

Paso 1. Preparar un directorio de nombres de columna

Lo primero que debe hacer es preparar un libro de referencia con todas las opciones posibles para los nombres de las columnas y su interpretación correcta:

Convertimos esta lista en una tabla dinámica "inteligente" usando el botón Formatear como tabla en la pestaña Inicio (Inicio — Formatear como tabla) o atajo de teclado Ctrl+T y cárguelo en Power Query con el comando Datos: de la tabla/rango (Datos: de la tabla/rango). En versiones recientes de Excel, se le ha cambiado el nombre a con hojas (De la hoja).

En la ventana del editor de consultas de Power Query, tradicionalmente eliminamos el paso Tipo cambiado y agregue un nuevo paso en su lugar haciendo clic en el botón f_xen la barra de fórmulas (si no está visible, puede habilitarlo en la pestaña Revisar) e ingrese la fórmula allí en el lenguaje M integrado de Power Query:

=Tabla.ToRows(Fuente)

Este comando convertirá el cargado en el paso anterior Fuente tabla de referencia en una lista que consta de listas anidadas (Lista), cada una de las cuales, a su vez, es un par de valores fue-se convirtió de una línea:

Necesitaremos este tipo de datos un poco más adelante, cuando renombramos en masa los encabezados de todas las tablas cargadas.

Después de completar la conversión, seleccione los comandos Inicio — Cerrar y cargar — Cerrar y cargar en… y tipo de importación Solo crea una conexión (Inicio — Cerrar y cargar — Cerrar y cargar a… — Solo crear conexión) y vuelve a Excel.

Paso 2. Cargamos todo desde todos los archivos tal cual

Ahora carguemos el contenido de todos nuestros archivos desde la carpeta, por ahora, tal como está. Eligiendo equipos Datos – Obtener datos – Desde archivo – Desde carpeta (Datos — Obtener datos — Desde archivo — Desde carpeta) y luego la carpeta donde están nuestros libros fuente.

En la ventana de vista previa, haga clic en Convertir (Transformar) or Cambios (Edit):

Y luego expanda el contenido de todos los archivos descargados (Binario) botón con flechas dobles en el encabezado de la columna Contenido:

Power Query en el ejemplo del primer archivo (Vostok.xlsx) nos preguntará el nombre de la hoja que queremos tomar de cada libro de trabajo – elija Fotos y presiona Aceptar:

Después de eso (de hecho), ocurrirán varios eventos que no son obvios para el usuario, cuyas consecuencias son claramente visibles en el panel izquierdo:

1. Power Query tomará el primer archivo de la carpeta (lo tendremos Vostok.xlsx — ver Ejemplo de archivo) como ejemplo e importa su contenido creando una consulta Convertir archivo de muestra. Esta consulta tendrá algunos pasos simples como Fuente (acceso a archivos) Navegación (selección de láminas) y posiblemente subiendo los títulos. Esta solicitud solo puede cargar datos de un archivo específico Vostok.xlsx.
   
2. En base a esta solicitud, se creará la función asociada a ella. Convertir archivo (indicado por un icono característico f_x), donde el archivo fuente ya no será una constante, sino un valor variable, un parámetro. Así, esta función puede extraer datos de cualquier libro que introduzcamos como argumento.
   
3. La función se aplicará a su vez a cada archivo (Binario) de la columna Contenido – el paso es responsable de esto Llamar a la función personalizada en nuestra consulta que agrega una columna a la lista de archivos Convertir archivo con resultados de importación de cada libro de trabajo:

   

4. Se eliminan las columnas adicionales.
5. El contenido de las tablas anidadas se expande (paso Columna de tabla extendida) – y vemos los resultados finales de la recopilación de datos de todos los libros:

   
   

Paso 3. Lijado

La captura de pantalla anterior muestra claramente que el ensamblaje directo "tal cual" resultó ser de mala calidad:

• Las columnas están invertidas.
• Muchas líneas adicionales (vacías y no solo).
• Los encabezados de tabla no se perciben como encabezados y se mezclan con datos.
  

Puede solucionar todos estos problemas muy fácilmente: simplemente modifique la consulta Convertir archivo de muestra. Todos los ajustes que le hagamos caerán automáticamente en la función Convertir archivo asociada, lo que significa que se utilizarán más adelante al importar datos de cada archivo.

Al abrir una solicitud Convertir archivo de muestra, agregue pasos para filtrar filas innecesarias (por ejemplo, por columna Column2) y subiendo los encabezados con el botón Usar la primera línea como encabezados (Utilice la primera fila como encabezados). La mesa se verá mucho mejor.

Para que las columnas de diferentes archivos encajen automáticamente entre sí más tarde, deben tener el mismo nombre. Puede realizar dicho cambio de nombre masivo de acuerdo con un directorio creado previamente con una línea de código M. Presionamos el botón de nuevo f_x en la barra de fórmulas y agregue una función para cambiar:

= Table.RenameColumns(#”Encabezados elevados”, Encabezados, MissingField.Ignorar)

Esta función toma la tabla del paso anterior Encabezados elevados y cambia el nombre de todas las columnas de acuerdo con la lista de búsqueda anidada Titulares. tercer argumento MissingField.Ignorar es necesario para que en aquellas cabeceras que están en el directorio, pero no están en la tabla, no se produzca un error.

En realidad, eso es todo.

Volviendo a la solicitud Informes veremos una imagen completamente diferente, mucho mejor que la anterior:

• Qué es Power Query, Power Pivot, Power BI y por qué un usuario de Excel los necesita
  
• Recopilación de datos de todos los archivos en una carpeta dada
• Recopilación de datos de todas las hojas del libro en una tabla