Combinar o Anexar Datos: Combinar Archivos de Excel

En la publicación anterior, aprendimos a combinar múltiples archivos desde una Carpeta.

En esa entrada utilizamos archivos planos pero, ¿cómo sería ese proceso para archivos de Excel?

En este artículo veremos la diferencia entre archivos planos sencillos y archivos más complejos (por ejemplo, un libro de Excel) cuando se trata de usar la experiencia Combinar Archivos dentro de Power BI / Power Query.

¿Cuál es la diferencia entre combinar archivos planos y algo como los archivos de Excel?

¡Esa es una buena pregunta! Lo primero que debemos entender es que un archivo plano tiene una estructura muy básica, mientras que un libro de Excel no es tan simple y tienes que definir el objeto exacto al que te quieres conectar.

Con Power BI / Power Query, tienes la opción de conectarte a 3 tipos de objetos desde un libro de Excel:

• Hojas
• Tablas
• Rangos definidos

En contraste, cuando quieres conectarte a un archivo plano vas directo a los datos ya que la estructura del archivo es muy sencilla.

¿Cómo la diferencia entre los archivos planos y los archivos de Excel impacta la experiencia de Combinar Archivos?

¡Otra buenapregunta! Y la respuesta es sencilla: debes considerar la estructura del archivo para crear la “receta” (función) que trabajará sobre todos los archivos que quieras combinar.

Normalmente el problema con el que la mayoría se encuentra al usar un Libro de Excel como su fuente para Combinar Archivos desde una carpeta es que definen su receta de una forma demasiado específica, y esto puede servir para algunos archivos, pero no para todos.

Por ejemplo, algunas personas especifican que quieren obtener los datos desde una tabla llamada “Table1” de su “Sample Transform File”, pero esa tabla únicamente existe en su Archivo de Muestra y no en el resto de los archivos.

Hagamos un ejercicio sobre cómo combinar múltiples Libros de Excel desde una carpeta

Paso 1: Conectarse a los Archivos

Esta vez la situación será un poco más fácil en términos de la cantidad de archivos que manejaremos.

Solo tenemos 2 archivos en nuestra carpeta, pero dentro de cada archivo hay 12 hojas (1 por cada mes de datos) y dentro de esas hojas hay una tabla en donde está guardada la información mensual.

Nuestra meta es conectarnos a los archivos, obtener todas las Tablas que tienen los datos que queremos y luego combinar / consolidar / anexar todo en una gran tabla.

Si quieres seguir el ejemplo paso a paso, puedes descargar los archivos en el siguiente botón:

Similar a lo que hicimos antes, vamos a usar la experiencia Desde Carpeta:

Y cuando selecciones la carpeta donde están tus archivos, puede que termines con algo así:

Recuerda, únicamente tenemos 2 archivos en esa carpeta, pero en la vista previa dice que hay 3. ¿Por qué sucede esto?

Si prestas atención, te darás cuenta de que uno de esos archivos tiene el prefijo ~, el cual te sirve para identificar los archivos temporales. Esto significa que el archivo 2017.xlsx está siendo utilizado. Entonces hagamos clic en el botón Editar para poder filtrar esos archivos temporales.

Esto es bastante sencillo, simplemente haz un filtro No Comienza por en la columna Nombre así:

y luego terminarás con 2 archivos, justo los que necesitamos:

Paso 2: Usar la Experiencia Combinar Archivos

Ahora hagamos clic en el botón Combinar Archivos (en la Columna de Contenido) y veamos a donde nos lleva esto:

Como puede que ya te hayas dado cuenta, hay mucha información en ese archivo y también puedes hacer algunas diferencias entre los objetos que se encuentran en ese archivo por medio de los íconos. El que tiene una barra azul arriba es para tablas, y el resto son hojas.

Y aquí está el motivo del problema más común entre los usuarios: seleccionan únicamente 1 tabla o no saben cómo seleccionar múltiples tablas a la vez.

En lugar de tratar de seleccionar múltiples tablas, quiero que hagas clic en “Parámetro de archivo de muestra” que se ubica en la parte superior, el que tiene un ícono de carpeta a la izquierda. Selecciónalo y haz clic en Aceptar.

Ve a la consulta a la izquierda que comienza con Transformar Archivo de Muestra Desde (Transform Sample File From) y échale un vistazo:

Allí dentro, vemos una tabla con TODOS los objetos a los que podemos acceder desde nuestro Archivo de Muestra.

En nuestro caso específico, queremos acceder únicamente a los archivos que tienen al Tipo igual a la Tabla, así que apliquemos ese ese filtro:

He resaltado algunas filas que no necesitamos ya que no contienen los datos que queremos. Las tablas que buscamos tienen nombres que inician con un prefijo de Sales_, entonces apliquemos otro filtro en la columna Item para que obtengamos únicamente las filas que contienen Sales_:

Finalmente terminamos con el proceso del Filtro y lo único que debemos hacer ahora es ir a la columna Data y hacer clic en el ícono de la dereche que iniciará la operación Table.ExpandTableColumn:

y el resultado de esa operación se verá así:

Te animo a deshacerte de las columnas que no necesitas. En mi caso, obtuve únicamente las columnas que necesitaba de las tablas:

Y con eso finalizamos la receta para transformar cada uno de los archivos en la carpeta.

Paso 3: Validando los Resultados

Si quieres ir a tu consulta original (la mía era Sales en los Archivos de Excel), te darás cuenta de que te aparece un error. Puedes leer esta entrada del blog para saber más sobre este tipo de errores en Power BI / Power Query, pero en pocas palabras, este es un error de nivel de paso que hace referencia a una Columna que ya no existe::

Lo que normalmente recomiendo es que elimines el último paso con nombre “Tipo Cambiado”, porque el anterior a ese funciona de maravilla.

Cuando tratas de validar si todos tus datos están ahí, no deberías sorprenderte al ver que de verdad todos tus datos están ahí:

El último paso es definir los tipos de datos de cada una de las columnas y si quieres, puedes cambiar el nombre a tu gusto. Así es como mi consulta se ve después de haberle cambiado el nombre a las columnas y agregado los tipos de datos correctos:

Y para cerrar con broche de oro, aquí te dejo una captura de pantalla que muestra cómo funcionan todas las consultas juntas:

Como te habrás dado cuenta, se ve igual que la que vimos en la entrada del blog anterior sobre los archivos planos. La realidad es que ese proceso es exactamente el mismo sin importar los archivos que quieras combinar. Lo único que cambia es el código / pasos que suceden dentro de tu Archivo de Muestra (Transform Sample File).

Últimas Palabras

Si bien no es sencillo distinguir que el ícono de la Carpeta es una opción válida cuando te estás conectando a un archivo de Excel, es recomendable que lo uses si los archivos que quieres combinar no tienen exactamente los mismos nombres en las hojas, las tablas o los rangos nombrados que quieres combinar.

De forma predeterminada, cuando ves esa ventana de “Combinar Archivos” donde puedes seleccionar los objetos dentro del Libro de Excel, únicamente te permite seleccionar una opción. Por eso no puedes seleccionar múltiples tablas, hojas o rangos nombrados desde allí. Si quisieras hacer eso, tendrías que hacer el proceso que describí en esta entrada de este blog.

¿Alguna vez has tenido una mala experiencia o un problema al usar Combinar Archivos con archivos de Excel? Comparte tu experiencia conmigo en la sección de comentarios a continuación.