Aprendamos Power Query para Excel – 5 – Consolidar información de hojas
En los capítulos anteriores vimos cómo extraer información desde archivo .CSV y .TXT, pero qué pasa si la información que deseamos transformar está en el mismo archivo de Excel, en diferentes hojas. Power Query tiene una fórmula que nos permitirá mostrar los diferentes modos en que podemos obtener información de un archivo.
Nos vamos a la siguiente ruta:
- Pestaña Datos > Nueva consulta > Desde otras fuentes > Consulta en blanco.
Una vez dentro del Editor de consultas escribimos la siguiente función:
=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:
- Tablas de Excel.
- Nombres de rangos.
- Áreas de impresión.
- Conexiones.
Ver video Power Query – Consolidar información de hojas
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Consolidar Tablas de Excel
En el archivo que manejaremos en este ejemplo (mismo que puedes descargar al pie del artículo), tiene 3 hojas y en cada hoja hay información por mes, tenemos la hoja de enero, febrero, marzo y abril.
Figura 1. Consolidaremos 4 Tablas.
Seguimos el mismo paso que describí con anterioridad:
- Pestaña Datos > Nueva consulta > Desde otras fuentes > Consulta en blanco.
En la barra de fórmulas del Editor de consultas ingresamos:
=Excel.CurrentWorkbook()
Ahora vemos la lista de las 4 tablas que tenemos en nuestro archivo. Recordemos que hay una tabla por hoja.
Figura 2. Lista de Tablas en el archivo.
- Ahora damos clic en el botón de la columna Content para desplegar la información consolidada de las tablas.
- Demarcamos la opción Usar el nombre de la columna original como prefijo.
Figura 3. Clic en el botón de la columna Content.
Ahora hagamos las siguientes transformaciones:
- Clic derecho en FECHA_COMPRA > Cambiar tipo > Fecha.
- Elegimos CANTIDAD, luego con la tecla Control presionada elegimos FECHA_COMPRA, PRODUCTO, SUCURSAL, VENDEDOR.
- Presionamos clic derecho y elegimos Quitar otras columnas.
- Le cambiamos el nombre a la consulta, y le ponemos PrimerCuatrimestre.
Figura 4. Contenido de las 4 Tablas.
Cargar datos
Y para finalizar, cargaremos los datos a una hoja de cálculo:
- Nos dirigimos a la pestaña Archivo y elegimos Cerrar y cargar.
En la siguiente figura vemos algo que nos llama la atención, los datos combinados son 11,360 , pero la consulta muestra 23,260 registros, es decir, el doble.
Figura 5. Se muestra el doble de regitros. Qué pasó?
Veamos que sucedió
Cuando usamos la función =Excel.CurrentWorkbook() se enlistaron las 4 Tablas de nuestro archivo, pero al final, cuando combinamos el contenido de estas 4 Tablas, se creó una Tabla adicional con el nombre PrimerCuatrimestre, entonces Excel intentó mostrar el contenido de las 4 Tablas más el de la Tabla nueva.
Regresemos al Editor de consultas, elijamos el paso Origen y ahora vemos que se muestran 5 Tablas.
Figura 6. Se añadió una Tabla adicional con la combinación de las 4 Tablas iniciales.
Cómo lo solucionamos
Para cargar solamente los valores de las 4 Tablas, hagamos lo siguiente:
- Elegimos el paso Origen.
- Elegimos la columna Name.
- Clic sobre el filtro > Filtros de Texto > No contiene.
- Confirmamos que sí deseamos insertar un paso.
- Escribimos el texto PrimerCuatrimestre.
- Clic en Aceptar.
- Pestaña Archivo > Cerrar y cargar.
Con los pasos anteriores nos aseguramos que los datos cargamos excluyan el contenido de la Tabla PrimerCuatrimentre, que fue la que se generó y donde se mostrarán los datos combinados de las 4 Tablas de cada uno de los meses.
Ahora vemos la cantidad correcta, 11360.
Figura 7. Se cargaron los registros a una hoja de cálculo.
Descarga el archivo de ejemplo
ejemplo03 – hojas por meses 1er cuatrimestre.rar
