Saludos a todos
El día de hoy vamos a seguir descubriendo la versatilidad que tiene Power Query para Importar, Transformar y Cargar datos, y a partir de ello construir soluciones que sean fáciles de actualizar, pero con mucho potencial para analizar escenarios, todo a través del comando Anexar en Power Query, así que empecemos con el ejemplo.
– Ejemplo: Ingresos Múltiples Tiendas
Nuestro objetivo, es crear un reporte con diferentes perspectivas sobre los ingresos obtenidos en diferentes tiendas de una misma cadena de supermercados, a continuación puedes ver la información que nos ha suministrado cada una de las tiendas con los ingresos obtenidos día a día hasta el mes de marzo.
Como podrás intuir, debemos “unificar” todas las tablas, en una «tabla maestra» por así decirlo, la manera tradicional consiste en copiar y pegar las tablas una debajo de la otra directamente en una hoja de Excel y de esta manera preparar los datos de origen para el reporte, sin embargo, este método puede resultar muy tedioso y adicionalmente estaremos más propensos a cometer errores y arruinar la integridad de los datos, aunque por fortuna, contamos con Power Query.
Solución
1. Importar (Extract) a Power Query
En la pestaña Power Query (Grupo Obtener y transformar en Excel 2016), nos dirigimos al grupo Obtener Datos Externos, damos clic en Desde un Archivo, y luego seleccionamos la opción Desde Texto.
Posterior a ello, identificamos la ubicación del archivo en el computador y presionamos Aceptar.
Automáticamente aparece el Editor de Consultas, “el lugar donde la magia ocurre”, con nuestra tabla cargada y con algunas modificaciones aplicadas directamente por Power Query.
Nota: En el panel configuración de consulta podemos ver las modificaciones aplicadas a la tabla, en Pasos Aplicados, si seleccionamos algún paso, nos muestra como estaba la tabla sin hacer dicha modificación.
2. Transformación en el Editor de Power Query (Transform)
Puesto que la columna fecha tiene el formato apropiado, solo basta con modificar el tipo de dato de la columna Ingresos cambiandolo a Moneda: Clic derecho sobre la etiqueta de la columna Ingreso -> Cambiar Tipo -> Moneda.
Por ultimo eliminamos la columna adicional, que NO está cumpliendo con ninguna función dentro del análisis: Clic derecho sobre la columna -> Quitar.
Nota: Recordemos que el nombre de la consulta, por defecto, será el mismo que el del archivo de los datos de origen, en este caso el archivo de texto.
3. Cargar (Load)
Para finalizar, damos clic en Cerrar y Cargar , e instantáneamente aparecerá en una nueva hoja del libro, la tabla limpia, junto con el panel de consultas del libro.
Adicionalmente, tenemos que repetir el proceso ETL (Extracción, Transformación y Carga) para los dos archivos restantes (Tienda B y Tienda C).
– Anexar
Con las consultas y las tablas ya creadas, vamos a agregar una nueva consulta pero esta vez con un procedimiento diferente: Pestaña Power Query –> Grupo Combinar -> elegimos el Comando Anexar.
En el cuadro de dialogo Anexar elegimos como tabla principal (Tabla a la que vamos a anexar las demás tablas) la Tienda A, y la tabla que vamos a Anexar, es la de la Tienda B.
Para este ejemplo, vamos a dejar habilitada la Opción Dos Tablas, y presionamos el botón Aceptar.
– Consideraciones
- Este método solo permite anexar o combinar Consultas, NO Tablas Estructuradas.
- La primera tabla que elegimos es a la que se le anexaran las demás tablas o datos.
Automáticamente se abrirá el editor de consultas con una nueva consulta que tiene por defecto el nombre de Append1, el cual vamos a sustituir por Tabla Maestra.
Damos clic en Cerrar y Cargar…
En este momento, hemos creado una nueva consulta con los datos de la Tienda A y Tienda B, no obstante, aun falta anexar la información de la Tienda C …
En lugar de utilizar el comando Anexar en la pestaña Power Query, para crear una nueva consulta y así, terminar de añadir los datos de la Tienda C, vamos a editar la consulta que recién hemos creado dando doble clic izquierdo sobre su nombre en el panel de consultas.
En el editor de consultas, vamos a modificar la consulta Tabla Maestra anexando los datos de la Tienda C : En el editor de consultas -> Pestaña Inicio -> Comando Combinar -> Elegimos la opción Anexar Consultas.
En el cuadro de dialogo Anexar, seleccionamos la consulta que hace falta por añadir, Tienda C, y presionamos el botón Aceptar.
Damos clic en Cerrar y Cargar en el editor de consultas.
Ahora, en el panel de consultas del libro, podemos ver como el número de filas de la consulta Tabla Maestra ha sido incrementado después de anexar los datos de la tienda c.
– Creación de Reporte
A partir de la Tabla Estructurada que crea Power Query con la información unificada, vamos crear una Tabla Dinámica: Celda Activa sobre la Tabla Maestra -> Pestaña Insertar -> Grupo Tablas -> Comando Tabla Dinámica -> Nueva Hoja de Cálculo -> Aceptar.
Posterior a ello, vamos a arrastrar el campo Sede al Área de Filas, el campo Fecha al área de columnas, y en el área de valores el campo Ingresos.
Agrupamos por Mes el Campo Fecha Y obtenemos la siguiente tabla dinámica:
Ahora bien, vamos a agregar un Slicer utilizando el campo Categoria de Producto: Con la celda activa sobre la tabla dinámica -> Pestaña Analizar -> grupo Filtrar -> comando Insertar Segmentación de Datos -> habilitamos el campo Categoria de Producto -> Aceptar.
Para finalizar, agregamos un gráfico Dinámico: Con la celda activa sobre la tabla dinámica -> Pestaña Analizar -> grupo Herramientas -> Gráfico Dinámico.
Reajustamos las dimensiones del slicer y del gráfico:
Y eso es todo lo que debemos hacer para crear nuestro reporte.
– Actualización
Imagina que has terminado de construir tu reporte e inesperadamente debes agregar información adicional de una cuarta tienda, la Tienda D. 😯
Para ello, debemos repetir el Proceso de Extracción, Transformación y Carga de la consulta para la Tienda D, posteriormente, debemos editar nuevamente la consulta maestra para Anexar los datos de la Tienda D.
Al cerrar y cargar la consulta Tabla Maestra, con los datos de la Tienda D ya agregados, vamos a dirigirnos a la hoja donde se encuentra nuestro reporte de tabla dinámica y en la pestaña Datos, vamos a dar clic en el botón Actualizar Todo.
Y Bang ¡¡¡ todo el reporte se ha actualizado con un solo clic, una solución muy sofisticada para un problema muy común como es la actualización de soluciones creadas en Excel.
Con este procedimiento podrás crear reportes que sean fáciles de actualizar a través del tiempo aumentando tu productividad enormemente, lo cual te brinda la posibilidad de crear reportes bastante robustos e igualmente flexibles.
Bueno por el día de hoy es todo, hasta la próxima.
Un Abrazo !!!