Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
En esta publicación, usamos Power Query para importar todos los archivos en una carpeta. Le damos a Power Query una ruta de carpeta, hacemos clic en algunos botones e importa y combina todos los archivos en una sola tabla. ¡Es como magia!
Para agregar otro archivo a la tabla de salida, solo debemos guardar una copia del archivo en la carpeta y hacer clic en actualizar; el nuevo archivo también se importará. Esto puede ahorrarnos días de más de un mes.
Antes de comenzar con esta técnica, hay un punto que debe tener en cuenta. Los archivos que se importarán deben seguir una estructura y un patrón de columnas similares. Power Query es mágico, pero hay que darle una oportunidad razonable. Existen técnicas más avanzadas que podemos usar para combinar archivos con diferentes estructuras, pero eso no es para los pusilánimes y está fuera del alcance de esta publicación.
Los tipos de archivos más comunes para los usuarios de Excel son CSV y libros de Excel. Estos son los dos tipos de archivos que se tratan en esta publicación. Si tiene otros archivos, como .txt, XML o JSON, etc., aún puede utilizar las técnicas de esta publicación, pero requerirá algunos cambios en el proceso.
Tabla de contenido
- Configurando el ejemplo
- Combina todos los archivos CSV en una carpeta
- Método 1: combinar archivos
- Método 2: utilizar una columna personalizada
- Agregar más archivos a la carpeta
- La uniformidad de archivos es la clave del éxito
- Combinar libros de Excel en una carpeta
- Método 1: combinar archivos
- Método 2: utilizar una columna personalizada
- Consejos de notas
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0107 Power Query Combeind Files.zip
Tener acceso
Los ejemplos de esta publicación utilizan los siguientes archivos:
Archivos CSV:
- enero 2019.csv
- febrero 2019.csv
- marzo 2019.csv
- abril 2019.csv
Libros de Excel:
- enero 2019.xlsx
- febrero 2019.xlsx
- marzo 2019.xlsx
- Abril 2019.xlsx
Para seguir el ejemplo de esta publicación, comience moviendo los archivos de enero y febrero a una carpeta separada (tanto los archivos .csv como .xlsx). Estos son los archivos que se importarán inicialmente.
En el ejemplo, he usado una carpeta llamada Carpeta de importación .
No incluya los archivos de marzo o abril en la carpeta en esta etapa. Agregaremos esos archivos más adelante como parte del ejemplo.
Combina todos los archivos CSV en una carpeta
Los archivos más fáciles de combinar son los archivos CSV o de texto, así que ahí es donde comenzaremos.
¿Qué hace que sea fácil trabajar con archivos CSV?
- Siempre contienen una sola tabla.
- La primera fila se trata como la fila del encabezado.
- No se permiten celdas fusionadas
En Excel, haga clic en Datos Obtener datos del archivo de la carpeta
Navegue hasta la carpeta que contiene los archivos para importar y luego haga clic en Abrir .
Se muestra una vista previa de los atributos de la carpeta y el archivo.
Si los archivos son uniformes y no se requieren modificaciones, podemos hacer clic en Combinar (botón desplegable) Combinar Cerrar Cargar . Sin embargo, notará que tenemos una combinación de archivos .csv y .xlsx en la carpeta; no podemos combinarlos fácilmente. En su lugar, hacemos clic en Transformar datos y observamos más de cerca lo que está sucediendo aquí.
Mi consejo es siempre hacer clic en Transformar datos para asegurarse de que los datos sean correctos antes de enviarlos a Excel.
Se abre el editor de Power Query. Sin embargo, no muestra los datos de los archivos como estamos acostumbrados a ver. En cambio, Power Query muestra datos sobre los archivos. Este es el mismo proceso que se utiliza para enumerar los archivos en una carpeta .
Nuestra primera transformación conserva solo los archivos que deseamos combinar (es decir, los archivos CSV de nuestro ejemplo actual). Es una buena práctica filtrar para incluir sólo los archivos que necesitamos; nunca se sabe cuándo otro usuario podría decidir guardar un tipo de archivo aleatorio dentro de la carpeta.
Primero, cambie la extensión del archivo a minúsculas. Con la columna Extensión seleccionada, haga clic en Transformar formato en minúsculas . Como Power Query distingue entre mayúsculas y minúsculas, este paso garantiza que .CSV y .csv reciban el mismo tratamiento. Si bien no tenemos ningún problema con nuestros archivos de ejemplo, es bueno prepararlos para el futuro cuando podamos.
A continuación, filtre la columna Extensión para incluir solo .csv . Haga clic en Filtrar texto Los filtros contienen… . En el cuadro de diálogo Filtrar filas, ingrese .csv y luego haga clic en Aceptar . Una vez más, este paso prepara el futuro; garantiza que se excluyan todos los archivos que no sean CSV.
A partir de aquí tenemos dos opciones.
- Combina los archivos
- Utilice una columna personalizada para extraer los datos de cada archivo
Técnicamente, existen otras opciones, pero son más avanzadas de lo que estamos viendo en esta publicación. El método que elijas depende de ti. Incluso puedes decidir utilizar diferentes métodos para diferentes circunstancias.
Método 1: combinar archivos
El método 1 utiliza el proceso de combinación mágico integrado de Power Query.
La ventana de Vista previa debería verse así:
¿Notaste el ícono con las dos flechas hacia abajo en la parte superior de la columna Contenido ? Ese es el ícono Combinar archivos . Si hacemos clic en el icono, Power Query crea muchas consultas y pasos para nosotros. Efectivamente, profundiza desde el nivel del archivo hasta el nivel del contenido y combina cada archivo.
Continúe y haga clic en el ícono Combinar archivos … ya sabe que lo desea.
Se abre la ventana Combinar archivo , que muestra una muestra de los datos utilizando el primer archivo de la consulta como ejemplo.
Quizás se pregunte por qué el primer archivo tiene fechas de febrero en lugar de enero. Esto se debe a que febrero viene antes de enero alfabéticamente.
Todo se ve bien, así que haga clic en Aceptar.
Power Query ahora hace su magia. Una vez finalizado, vemos que los archivos están combinados. ¡Eso es todo lo que hizo falta, un botón!
Los datos de vista previa deberían verse así:
Si se desplaza hacia abajo, verá que los datos de enero y febrero se combinan en una sola tabla.
En segundo plano, Power Query crea muchas transformaciones para lograr esto (consulte las secciones resaltadas a continuación)
¿Qué está pasando con todas estas consultas?
Quizás se pregunte qué hacen todos estos archivos. Como resumen rápido:
- Parámetro1 : un parámetro que contiene el nombre del archivo de muestra seleccionado.
- Archivo de muestra : un objeto de archivo del archivo de muestra seleccionado.
- Transformar archivo : una función personalizada que se aplica a cada archivo de la carpeta. Los pasos de la función se basan en la consulta Transformar archivo de muestra .
- Transformar archivo de muestra : una consulta que contiene las transformaciones aplicadas al archivo de muestra. Constituye la base de la función personalizada Transformar archivo .
- Carpeta de importación (o como se llame la suya): esta es la consulta final con todos los archivos apilados.
Hay dos ubicaciones donde podemos realizar transformaciones:
- Transformar archivo de muestra: todas las transformaciones realizadas en esta consulta se aplican a todos los archivos de la carpeta.
- Consulta principal (la carpeta de importación en mi ejemplo): esta es una consulta combinada, por lo que todas las transformaciones se aplican a los datos combinados.
Primero deberíamos intentar realizar cualquier cambio en nuestros datos en el archivo de muestra de transformación.
Con la consulta Transformar archivo de muestra seleccionada:
- Promocione encabezados haciendo clic en Inicio Usar la primera fila como encabezados
- Cambie el tipo de datos para cada columna. Tenga en cuenta que las fechas del archivo CSV están en formato de fecha del Reino Unido, por lo que es posible que deba seleccionar Usando configuración regional... Fecha Inglés Reino Unido para la columna de fecha.
Las transformaciones se aplican a cada archivo de la carpeta y se combinan en la consulta Carpeta de importación.
Desafortunadamente, los tipos de datos no se incluyen en la consulta principal. Entonces, necesitamos volver a aplicar los tipos de datos en la consulta principal.
Finalmente, haga clic en Inicio Cerrar Cargar para cargar los datos en Excel.
Método 2: utilizar una columna personalizada
El segundo método utiliza una columna personalizada que contiene la función Csv.Document Power Query.
Siga los pasos del Método 1 para cargar los datos en Power Query. La ventana de vista previa debería verse así:
A continuación, comencemos a realizar transformaciones.
Seleccione las columnas Contenido y Nombre, luego haga clic en Inicio Quitar columnas Quitar otras columnas . Esto elimina las columnas que no necesitamos.
Haga clic en Agregar columna Columna personalizada .
En la ventana Columna personalizada , ingrese la siguiente información
Nuevo nombre de columna: CSV
Fórmula de columna personalizada:
=Csv.Document([Content])
Haga clic en Aceptar para ejecutar la función.
Se agrega una nueva columna llamada CSV a la ventana de vista previa. El nombre se basa en el texto ingresado en el cuadro de diálogo Columna personalizada.
Notará un nuevo ícono de doble flecha en el encabezado de la columna CSV. Este es el ícono Expandir e indica que la columna contiene más detalles en los que podemos profundizar.
Haga clic en el icono Expandir. Desmarque la opción usar el nombre de columna original como prefijo y luego haga clic en Aceptar .
Ahora tenemos los datos de ambos archivos combinados en una sola vista.
Los archivos se apilan uno encima del otro, de forma similar a una transformación de adición .
Para ponerlo todo en orden, necesitamos emprender alguna transformación más:
- Seleccione las columnas Contenido y Nombre y haga clic en Inicio Quitar columna
- Promocione encabezados haciendo clic en Inicio Usar la primera fila como encabezados
- En la columna Fecha, filtre para eliminar el valor de texto "Fecha"
- Establezca los tipos de datos para cada columna. Recuerde, las fechas están en formato del Reino Unido, por lo que es posible que deba utilizar Usando configuración regional... Fecha Inglés Reino Unido .
Nuestra ventana de vista previa ahora debería verse así:
Eso es todo, todo hecho. Haga clic en Cerrar carga para cargar los datos en Excel. Los archivos de enero y febrero están combinados.
Agregar más archivos a la carpeta
Ahora es el momento de agregar más archivos a la carpeta. Copie el archivo.csv de marzo de 2019 en la carpeta.
Ahora, llega el momento de la verdad... en Excel, haga clic en Actualizar datos todos .
La tabla se actualiza con los nuevos datos de marzo. No me digas que no estás impresionado. ¡¡¡¡Es asombroso!!!!
La uniformidad de archivos es la clave del éxito
Ahora agregue el archivo.csv de abril de 2019 a la carpeta. Luego, haga clic en Actualizar datos todos .
Obtenemos un error:
¿Por qué sucedió esto? ¿Qué salió mal?
Abra April 2019.csv y observe de cerca la primera fila. Todos los demás archivos tenían Cliente en la celda B1, pero April tiene Vendido a .
Esto fue para ilustrar la importancia de tener encabezados de columna consistentes. Podríamos emplear otros métodos avanzados para evitar este error; sin embargo, la mejor opción es garantizar títulos coherentes.
Combinar libros de Excel en una carpeta
El método para importar libros de Excel es similar al de los archivos CSV. Pero como los libros de Excel contienen varias hojas y esas hojas pueden tener casi cualquier nombre, existe una complejidad adicional.
A Power Query le gusta codificar valores en el código M de consulta. Esto se convierte en un problema cuando los libros de Excel tienen hojas de trabajo con nombres diferentes.
Para ponernos a prueba, todos nuestros archivos de ejemplo en nuestro ejemplo tienen diferentes nombres de hojas de trabajo.
Copie los archivos de marzo de 2019.xlsx y abril de 2019.xlsx en la carpeta.
En Excel, siga estos pasos (similares a los ejemplos anteriores)
- Haga clic en Datos Obtener datos del archivo de la carpeta
- Navegue hasta la carpeta, luego haga clic en Abrir
- Cuando aparezca la lista de archivos, haga clic en el botón Transformar datos
- Se abre la ventana del Editor de Power Query
- Con la columna Extensión seleccionada:
- Transformar para que sea solo minúscula
- Filtrar para incluir solo archivos .xlsx
Veamos los mismos dos métodos que usamos anteriormente.
Método 1: combinar archivos
El método 1 utiliza el proceso de combinación integrado de Power Query. Después de los pasos anteriores, la ventana de vista previa debería verse así:
A continuación, haga clic en el icono Combinar como antes.
Como April.xlsx ocupa el primer lugar en la lista de archivos alfabéticamente, Power Query lo usará como archivo de muestra. En la ventana Combinar archivos, seleccione la hoja de abril y haga clic en Aceptar .
Como es de esperar, solo los datos de abril se cargan correctamente en la ventana Vista previa de nuestro escenario. Solo hay un libro con una hoja de trabajo llamada Abril , por lo que solo una hoja de trabajo se carga correctamente. Debemos encontrar la ubicación en el código M donde se encuentra el nombre de la hoja. ha sido codificado y ajústelo.
Abra la consulta Transformar archivo de muestra y haga clic en el paso de navegación en el panel de pasos aplicados.
El código M para este paso se ve así:
= Fuente{[ Elemento="Abril", Tipo="Hoja"]}[Datos]
Esto nos indica que se aplica un filtro para Elementos llamado "Abril", donde el tipo de objeto es una Hoja. Elimine el nombre del elemento. El código M ahora debería ser este:
= Fuente{[Tipo="Hoja"]}[Datos]
Vuelva a la consulta principal y mire nuevamente la ventana Vista previa. Todas las hojas de trabajo ahora están combinadas: ¡ eso es mágico!
Terminemos las transformaciones. En el archivo de muestra de transformación :
- Elimine las 3 filas superiores haciendo clic en Inicio Eliminar filas Eliminar filas superiores
- Promocione la primera fila a encabezado haciendo clic en Transformar Usar primera fila como encabezados
- Aplique los tipos de datos correctos a cada columna.
Regrese a la consulta principal y realice cambios para que la consulta funcione correctamente (es probable que deba eliminar y volver a aplicar el paso Tipo modificado).
Método 2: utilizar una columna personalizada
El método 2 utiliza una fórmula de columna personalizada, similar al ejemplo CSV anterior. Sin embargo, esta vez la fórmula aplicada es Excel.Workbook .
Suponiendo que no hayamos realizado el Método 1. Siga los mismos pasos hasta que la ventana de vista previa se vea así:
Seleccione las columnas Contenido y Nombre , luego haga clic en Inicio Quitar otras columnas .
Ahora agreguemos la nueva columna; haga clic en Agregar columna Columna personalizada .
En la ventana Columna personalizada, ingrese la siguiente información:
Nuevo nombre de columna: ExcelFile
Fórmula de columna personalizada:
=Excel.Libro de trabajo([Contenido])
Haga clic en Aceptar .
Se agrega una nueva columna de ExcelFile. Haga clic en el ícono Expandir de doble flecha, desmarque la opción para usar el nombre de la columna original como prefijo y luego haga clic en Aceptar para incluir todas las columnas.
Se enumeran todas las hojas de trabajo y tablas del libro de trabajo.
El archivo de abril contiene hojas de trabajo y tablas. Filtre la columna Tipo para incluir solo hojas.
Seleccione la columna Datos y luego haga clic en Inicio Quitar otras columnas .
Haga clic en el icono Expandir en la columna Datos para profundizar en los datos.
Ahora tenemos todas las hojas combinadas en una sola consulta.
Hagamos algunas transformaciones para poner las cosas en orden.
- Elimine las 3 filas superiores haciendo clic en Inicio Eliminar filas Eliminar filas superiores
- Promocione la primera fila como encabezado haciendo clic en Transformar Usar primera fila como encabezados
- Aplique los tipos de datos correctos a cada columna.
- Filtre la columna Vendido por para eliminar los valores nulos y Vendido por
Finalmente, cierre y cargue la consulta en Excel.
¡Ta-dah! Todo está bien en el mundo una vez más.
Consejos de notas
A continuación se ofrecen algunos consejos adicionales:
- Al buscar archivos, Power Query también buscará en subcarpetas. Esto es excelente cuando los archivos están organizados dentro de una estructura definida. Pero si las carpetas están desordenadas, es posible que sea necesario realizar algunas tareas de limpieza para que este método funcione. O puede editar el código M para cambiar Carpeta.Archivos a Carpeta.Contenido.
- Planifique lo que podría salir mal. Piense en lo que podría suceder si alguien más guardara un tipo de archivo diferente en la carpeta. Cuando cree la consulta, incluya pasos de filtrado adicionales para asegurarse de que solo se importen los archivos que desea.
- Los datos que provienen de un sistema informático suelen funcionar bien con esta técnica. Desafortunadamente, encuentro que cualquier dato generado por la entrada del usuario tiende a causar problemas. Como a la gente le gusta cambiar las cosas, añaden columnas; cambian el nombre de las hojas y escriben sobre valores estáticos. Por lo tanto, si sus archivos provienen de la entrada del usuario, prepárese para utilizar algunas transformaciones avanzadas. En este escenario, la transformación de adición será útil.
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta