Combinar o Anexar datos: Combinar archivos planos

En la publicación anterior, cubrí los conceptos básicos detrás de la operación de anexar datos que se encuentra dentro de Power Query para Excel y Power BI.

En esa publicación, solo usamos 2 archivos y fue bastante sencillo pues simplemente tuvimos que hacer clic en el botón Anexar consultas para combinar ambas consultas de esta manera:

Un escenario más complejo

Pero…¿qué pasa cuando tienes varios archivos? Digamos 12 archivos. Uno por cada mes del año.

Vayamos un poco más lejos, ¿qué sucedería si tienes una carpeta para cada año y dentro de cada carpeta un archivo plano para cada mes de ese año?

Crear una consulta para cada archivo consume demasiado tiempo y no es una solución dinámica, ya que cada año/mes terminarás volviendo a esta consulta y tendrás que modificarla.

¿Cuál es la mejor opción? Conectarse a una carpeta y usar la experiencia Combinar archivos.

Si deseas seguir el procedimiento que realizaré aquí, asegúrate de descargar los archivos por medio del siguiente botón:

Paso 1: Conectarse a los archivos

Nuestros archivos se almacenan en una carpeta que se llama Sales y luego, dentro de esa carpeta encontramos algunas subcarpetas para cada año.

En lugar de conectarnos a cada archivo de forma individual o a cada carpeta anual, simplemente nos conectaremos a la carpeta Sales para que podamos acceder a todo lo que se encuentra dentro de esa carpeta Sales.

¡Conectémonos a la carpeta Sales! Con Power Query esto es pan comido.

Simplemente selecciona la opción «Carpeta» y navega hasta la carpeta a la que deseas conectarte.

Notarás que te mostrará todos los archivos de esa carpeta y las subcarpetas que contiene:

En esta etapa, te recomiendo que hagas clic en el botón ‘Editar’ y nunca presiones el botón ‘Combinar’.

El botón Editar to llevará a la ventana del Editor de Power Query, donde tendrás más control sobre lo que sucederá.

Por lo general, en esta etapa debes asegurarte de cargar únicamente los archivos que deseas consolidar, por lo que se recomienda que quites los archivos que no se deben agregar.

En nuestro caso, todos los archivos que queremos combinar están en esas carpetas y no hay nada más en ellas.

¡Vayamos al siguiente paso!

Paso 2: Usar la experiencia de combinar archivos

Cuando veas tu consulta, notarás que hay una primera columna con el nombre de Content y justo al lado del nombre de esa columna, verás un botón que se ve como 2 flechas que van hacia abajo en contra de una línea de base.

Ese mismo botón aparece en la pestaña Inicio debajo de Combinar grupo como se muestra en la siguiente imagen:

Ese botón se llama Combinar archivos y solo está disponible cuando tienes una columna con valores binarios.

Nota: Si tienes una versión muy antigua de Power Query (anterior a junio de 2017), este botón podría llamarse Combinar Binarios y realiza una acción completamente diferente a la que describo en esta publicación.

Antes de presionar ese botón, debemos asegurarnos de algunas cosas:

• ¿Todos los archivos en esta consulta tienen la misma extensión de archivo? Sí, todos son archivos CSV
• ¿Los archivos en cuestión tienen una estructura bastante similar? Sí, la tienen.

Probablemente te estés preguntando qué sucede si tienes una situación en la que te haces esas preguntas y la respuesta a cualquiera de ellas es un no. Lo veremos más adelante en esta publicación, pero por ahora nos centraremos en nuestro escenario.

Una vez hagamos clic en el botón Combinar archivos, se nos muestra un asistente básico que nos guía a través de un proceso que creará una «receta» o un «proceso de transformación» que se aplicará a cada archivo antes de consolidar los datos.

Toda esta receta o proceso de transformación se denomina función personalizada y los «ingredientes» de esa «receta» se denominan parámetros. Puedes aprender más sobre las funciones y los parámetros en esta serie de artículos que escribí hace un par de semanas.

¿Qué es exactamente esa ventana de «asistente» que vemos cuando hacemos clic en el botón de combinar archivos? De manera sencilla, es exactamente la misma ventana que vemos cuando nos conectamos directamente a un archivo CSV.

¿Veríamos exactamente la misma ventana si intentáramos combinar archivos de Excel? No. Esta ventana es contextual al archivo que seleccionas en el menú desplegable «Archivo de ejemplo». Puedes dejarlo con la opción predeterminada de Primer archivo o seleccionar un archivo específico que desees utilizar como «ingrediente» de ejemplo para tu «receta». Sí, tu archivo es tu ingrediente principal para la receta.

Además, como puedes ver en la esquina inferior izquierda de esa ventana, hay una opción para Omitir archivos con errores (Skip files with errors), lo que significa que si nuestra receta termina teniendo errores (no nos da el resultado deseado), podemos deshacernos de ese resultado final.

Ya que estamos de acuerdo con la forma en que se lee nuestro archivo, simplemente hacemos clic en Aceptar y continuamos con el siguiente paso.

Paso 3: Validar los Resultados

Inmediatamente notarás que hay MUCHAS cosas en ese panel de Consultas (a tu izquierda). Hay un montón de consultas y grupos que no estaban ahí antes.

La consulta principal que nos interesa es la que tiene nuestra «receta», que es la que comienza con las palabras «Transformar archivo de muestra desde».

Esta consulta es la que define tu receta. De esta manera es como se procesarán exactamente cada uno de los archivos de tu carpeta y, como puedes ver a la derecha, tiene los siguientes pasos:

• Source – cómo necesita interpretar el binario. En nuestro caso interpreta el archivo como un csv.
• Promoted Headers – dado que los archivos planos no tienen una estructura definida sobre dónde están los encabezados de columna, interpretamos que los encabezados de columna están en la primera fila del archivo, por lo que promovemos esa fila como los encabezados de columna de nuestra tabla.

Puedes agregar más pasos a esta consulta y será como agregar más pasos a tu «receta». Esto es completamente opcional y solo es necesario si necesitas realizar algún tipo de transformación de tus datos, pero en nuestro caso no lo necesitamos.

Si deseas comprender más profundamente lo que está detrás de bambalinas en toda la experiencia de Combinar archivos, te recomiendo que consultes este artículo en mi blog en donde lo explico.

Todo lo que nos queda por hacer es volver a nuestra consulta de Sales para ver cómo se ven nuestros datos y notarás que también tiene el nombre del archivo de donde provienen los datos, lo cual es útil cuando tratas de auditarlos para encontrar cualquier error.

También puedes intentar filtrar esa columna para ver cuáles son los valores disponibles y verás que todos los archivos que queríamos combinar se mostrarán en esa lista:

Por último, pero no menos importante, aquí hay un pantallazo sobre cómo trabajan juntas todas las consultas:

Algunas palabras finales

Es posible que te encuentres con situaciones más complejas en las que necesites hacer mucho más que una simple consolidación de los datos. Tal vez debas realizar un proceso de limpieza o transformación antes de anexar los datos, y esa es la razón por la que existe la consulta «Transformar archivo de muestra desde» y la función «Transformar archivo desde».

Todo este proceso se aplica a cada uno de los binarios que desees combinar, por lo que el proceso se denomina «Combinar archivos» en lugar de algo como «combinar archivos csv» o «combinar archivos de excel». Podemos combinar cualquier tipo de archivo que queramos, como csv, txt, prn, xls, xlsx, xlsm, pdf, json, xml y muchos más.

¿Recuerda esas 2 preguntas que nos hicimos sobre la extensión y estructura de los archivos? Es mucho más fácil si solo tenemos una consulta en la que los archivos compartan la misma extensión y estructura, pero si te sientes seguro acerca de tus habilidades del lenguaje M, podrías hacer que las cosas funcionen a tu manera. Todavía te animo a que solo realices la experiencia de Combinar archivos con archivos que comparten la misma extensión de archivo y tienen una estructura muy similar. Si no tienen la misma extensión o estructura similar, entonces puedes crear otra consulta y básicamente segmentas tu carpeta original para que puedas dirigirte al conjunto de archivos que comparten la misma extensión y estructura de una manera más eficiente.

La próxima semana mostraré otra situación de la vida real en la que combinaremos datos de archivos de Excel, pero que deben ser transformados antes de hacer cualquier cosa como anexarlos / consolidarlos.