Aprendamos Power Query para Excel – 6 – Consolidar información de varios archivos de Excel

Ver curso completo Aprendamos Power Query para Excel.

En el artículo anterior vimos cómo consolidar información de varias hojas de Excel, para posteriormente analizar la información

con nuestra manera favorita: las Tablas dinámicas. Para cumplir con nuestro objetivo recordemos que usamos la función en Power Query:

=Excel.CurrentWorkbook()

Lo que la anterior función hace es mostrar los objetos de donde Excel puede extraer información. Considera que Power Query podrá extraer información de:

1. Tablas de Excel.

2. Nombres de rangos.

3. Áreas de impresión.

4. Conexiones.

Ver video Consolidar información de varios archivos de Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Consolidar información de archivos de Excel

En este artículos haremos uso de otra función, la cual nos ayudará a ver el contenido de los objetos a extraer información, pero de cada uno de los archivos enlistados. Dicha función es:

=Excel.Workbook([Content])

Toma en cuenta que esta función tomará los últimos datos guardados en los archivos, por lo que tenemos que prestar atención a Guardar cambios antes de actualizar la función en Power Query.

Cómo lo hacemos

Tenemos 24 archivos en una carpeta, los cuales son de los meses del año 2016. 12 archivos son .CSV y los otros 12 son archivos de Excel. En este artículo consolidaremos la información de los archivos de Excel, ya que los CSV contienen la misma información.

Figura 1. En la carpeta tenemos archivos de Excel y CSV.

En Excel nos dirigimos a la pestaña Datos. En la sección Obtener y transformar elegimos Nueva consulta > Desde un archivo > Desde una carpeta. Elegimos la ruta donde están nuestros archivos y damos Aceptar.

Figura 2. Elegimos la carpeta donde están los archivos.

Se mostrará una vista con todos los archivos de la carpeta. Damos clic en Editar.

Ahora, en la ventana de Power Query veremos una de los archivos de la carpeta elegida, además de ver las propiedades de dichos archivo. Nota que tenemos la misma columna Content, la cual vimos el capítulo 4 de esta serie.

Figura 3. En la columna Content podemos el contenido de los archivos.

Transformar

Ahora sigue los siguientes pasos para mostrar sólo los archivos de Excel y extraer su información:

• Expandimos el filtro de la columna Extensión y dejamos marcada la opción .xlsx.
• Con la tecla [Ctrl] presionada elegimos las columnas Content y Name.
• Damos clic derecho sobre cualquier de las dos columnas y elegimos Quitar otras columnas.
• En la pestaña Agregar columna elegimos la opción Columna personalizada.
• Añadimos la función =Excel.Workbook([Content]).

Figura 4. Añadimos la función =Excel.Workbook([Content]).

• Se agregará una nueva columna llamada Personalizado donde vemos que el contenido de cada elemento dice Table.
• Eliminamos la columna Content.
• Damos clic en el botón con el ícono con flechas encontradas .
• En el filtro que se hará desmarcamos la opción Usar el nombre de la columna original como prefijo.
• Hacemos un filtro en la columna Kind donde dejamos seleccionado solo Sheet.
• Eliminamos las columnas, Item, Kind y Hidden.
• Damos clic en en encabezado Data, en el ícono con las flechas encontradas .
• En el filtro que se hará desmarcamos la opción Usar el nombre de la columna original como prefijo.
• Nos vamos a la pestaña Inicio y seleccionamos la opción Usar la primera fila como encabezado.
• Elegimos la columna FECHA_ENTREGA y damos clic derecho para elegir Cambiar tipo > Usar configuración regional.
• En Tipo de datos elegimos Fecha y en Configuración regional elegimos Inglés (Estados Unidos).
• Elegimos la columna FECHA_ENTREGA y nos dirigimos a la pestaña Inicio > Quitar filas > Quitar errores.
• Eliminamos la primera columna llamada abr 2016.xlsx.
• Le cambiamos el nombre a la columna abr 2016 y le ponemos MES_AÑO.
• Elegimos las columnas MES y FECHA_COMPRA, damos clic derecho y elegimos Cambiar tipo > Fecha.

Cargar a Excel

En pestaña Inicio elegimos la opción Cerrar y cargar. Al final podemos hacer una Tabla dinámica para analizar la información.

Figura 5. Se cargaron los  registros de todos los archivos.

Descargar los archivos de ejemplo

 ejemplo 06 – archivos de meses.rar

Ver curso completo Aprendamos Power Query para Excel.