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.
Listo, después de mil intentos con mi data me salió, no estaba prestando atención al detalle. Gracias mil!!!
intento combinar 31 libros de excel en un libro, pero no me carga todos los archivos solo 20 libros ?¿como puedo hacer para que se carguen todos?
Hola Ruby!
Lo mejor que te puedo sugerir es revisar paso por paso para ver en qué paso de tu consulta se pierden los archivos. Una vez encuentres el paso en el cual se pierden estos archivos, tendrías que revisar tu código de dicho paso para poder entender el porqué. Podría ser que tu consulta está apuntando a un nombre de hoja o tabla que simplemente no existe en todos los archivos y por eso solo está combinando los datos de 20 libros.
Te recomiendo publicar tu consulta con lujo de detalles en el foro oficial de Power BI acá:
https://community.powerbi.com/t5/Translated-Spanish-Desktop/bd-p/pbi_spanish_desktop
Allí también podrás publicar archivos de ejemplo o códigos de ejemplo si así lo deseas para poder obtener ayuda de la comunidad y poder revisar en más detalle tu caso.
Saludos y suerte!
hola miguel,
me ha encantado el articulo, esta muy bien explicado, he seguido tus pasos y me ha salido a la primera¡¡¡¡
Ahora quiero hacer algo similar a tu ejemplo pero no se muy bien como hacerlo, ¿me echas una mano?
Recibo uno o varios libros diariamente, con una o varias tablas(una por libro), en una unica carpeta. Las tablas de dias anteiores anteriores se pierden y solo tengo la tabla del dia actual La idea es que al actualizar el «append» me actualice la tabla con los nuevos datos pero conservando los datos anteriores en la tabla(los datos se van apilando). ¿Como podria conservar el contenido anterior de la tabla al actualizar los daros de la consulta?
Espero que me puedas orientar sobre el tema, saludos 🙂
Hola!
Realmente el Power Query no mantiene un histórico de los datos que se han procesado. Lo que hace el Power Query es simplemente procesar el código que has creado y luego guardar ese resultado en algún lugar – usualmente sobreescribe el lugar donde se haya guardado la última ejecución.
Para mantener ese histórico necesitarías emplear otras técnicas que realmente no detallo en este artículo y que son específicas para Power Query en otras herramientas como PowerApps, SSIS y Power BI Dataflows.
Acá te comparto un artículo donde hablo de la opción de Dataflows en PowerApps y cómo podrían ayudarte para mantener ese histórico:
https://www.thepoweruser.com/es/2019/09/29/usando-flujos-de-datos-de-powerapps-y-el-add-in-para-excel-de-powerapps/
Excelente , después de varios intentos logre hacerlo, estos pasos no se pueden hacer en power query desde Excel , no permite seleccionar todos los archivos solo uno y luego unir todas sus hojas