Saludos amig@ de EFB
Hoy continuaremos hablando de otra aplicación de Importar archivos desde una carpeta utilizando Power Query, para crear un reporte de utilidad, que se actualice fácilmente, así que manos a la obra :
– Ejemplo – Supermercado
Imagina que trabajas en un Supermercado, y día a día llega un archivo de texto con los datos de las ventas obtenidas en ese día, Para entender el rendimiento de la tienda, debes crear una solución que además de ser fácilmente actualizable, permita analizar la utilidad obtenida rápidamente, a continuación, puedes ver el archivo del primer y segundo día del mes, con la información relacionada.
En la creación del reporte, utilizaremos algunas herramientas de nuestra suite en Inteligencia de Negocios de Microsoft, iniciando inevitablemente con Power Query
– Solución
1. Extraer (Extract)
Primero, vamos a generar una conexión entre nuestro libro y el origen de datos, que para este caso es una carpeta, utilizando Obtener y Transformar en Excel 2016.
Desde un libro nuevo, nos dirigimos a la Pestaña Datos -> grupo Obtener y Transformar -> clic en Nueva Consulta -> elegimos la opción Desde un archivo -> y damos clic en Desde una Carpeta
En el cuadro de diálogo Carpeta, elegimos la ruta de acceso a la carpeta que contiene los archivos dando clic en el Botón Examinar y eligiendo la ruta de la misma..
Para finalizar damos clic en el botón Aceptar.
Automáticamente, aparece un panel con la vista previa de la tabla, y es evidente que debemos editarla…
Al dar clic sobre el botón Editar, aparece el Editor de Consultas de Power Query
Con una tabla que muestra información asociada a cada uno de los archivos, mas NO el contenido o las tablas de cada uno, por lo que vamos a editar el resultado obtenido a través de la consulta, agregando más Pasos.
2. Transformar (Transform)
Eliminar Columnas
Primero debemos eliminar las columnas Innecesarias
Seleccionamos la columna que NO queremos eliminar, para este caso Content -> Clic derecho sobre la etiqueta de la columna seleccionada -> en la lista de opciones que aparece, elegimos Quitar otras Columnas
Por lo que obtenemos una tabla, con una columna.
Botón Combinar
Para que la consulta unifique las tablas de los dos archivos de texto que están en la carpeta, vamos a dar clic sobre el botón Combinar que tiene como símbolo, dos flechas apuntando a una línea horizontal, y aparece en la etiqueta de la columna “Content» al dar clic sobre el botón Combinar obtenemos el contenido de ambas tablas …
Primera fila como encabezado
Pestaña Transformar -> grupo Tabla –> Usar primera fila como encabezado
Filtrar Tabla, para quitar Filas
Para remover la fila de etiquetas del segundo archivo, vamos a utilizar las opciones de filtro
Clic sobre el botón de filtro, que se encuentra al lado derecho de la etiqueta de la columna -> y vamos a quitar la selección del Ítem Producto, porque esta es la etiqueta de la columna del segundo archivo, para finalizar damos clic en Aceptar
Tipo de Dato de Columna
Debemos asegurarnos que cada una de las columnas tenga el tipo de dato apropiado, para cambiar el tipo de dato de una columna:
Clic derecho sobre la columna Costo Unidad -> Cambiar Tipo –> y según el tipo de dato de la columna, elegimos la opción que para este caso es Número Decimal.
Nota: Debes repetir el procedimiento para cada columna, teniendo en cuenta el tipo de dato.
3. Cargar (Load)
Vamos a configurar la consulta de tal manera que cargue los datos en el Modelo de Datos de Excel.
Clic en Cerrar y cargar -> luego, damos clic en Cerrar y cargar en
En el cuadro de dialogo Cargar en, vamos a habilitar las opciones Crear Solo conexión y Agregar estos Datos al Modelo de datos, por último damos clic en Cargar
Al finalizar, podemos ver en el Panel de Consultas como se han cargado los datos en el modelo de datos de Excel.
Modelación
Para calcular la utilidad, vamos a utilizar tres medidas diferentes,
La primera medida se encarga de calcular los ingresos por cada producto, sin necesidad de crear columnas auxiliares.
Para crear una medida desde la interfaz de Excel, vamos a la pestaña Power Pivot -> grupo Cálculos -> Nueva Medida ->En el cuadro de dialogo Medida, asignamos un Nombre a la medida y definimos la tabla en la cual vamos a crear la medida.
y luego escribimos la expresión DAX
Ingresos (x) =
SUMX (
‘Ejemplo – Supermercado’;
‘Ejemplo – Supermercado'[Unidades Vendidas]
* ‘Ejemplo – Supermercado'[Precio Unidad]
)
Nota: Al agregar una medida al modelo de datos, aparece un mensaje alertándonos de dicha situación, aunque no representa ninguna preocupación.
Repetimos el procedimiento para calcular los costos y la utilidad
Utilizaremos la misma función de iteración, para calcular los costos totales.
Costos (x) =
SUMX (
‘Ejemplo – Supermercado’;
‘Ejemplo – Supermercado'[Unidades Vendidas]
* ‘Ejemplo – Supermercado'[Costo Unidad]
)
Ahora bien, sabemos que la utilidad es igual a los ingresos totales, menos los costos totales, algo del siguiente estilo:
Utilidad = Ingresos – Costos
Algo que se puede traducir fácilmente al lenguaje DAX, para calcular la Utilidad
Utilidad (x) = ‘Ejemplo – Supermercado'[Ingresos (x)] – ‘Ejemplo – Supermercado'[Costos (x)]
Puedes ver como a partir de dos expresiones DAX, podemos calcular una tercera Medida.
Visualización
Como estamos trabajando con el Modelo de Datos de Excel, tenemos varias opciones a nuestra disposición para crear el reporte, como por ejemplo:
En este caso, utilizaremos nuestras poderosas tablas dinámicas
Para ello vamos a crear una tabla dinámica con el campo Fecha en el área de Filas, y la medida Utilidad (x), en el área de valores
A partir de dicha tabla dinámica, vamos a insertar y personalizar dos gráficos dinámicos, uno de líneas y otro de Columnas
Ahora, vamos a agregar dos Slicers, uno por el campo producto y otro por el campo Categoría
Nota: No olvides agregar un nombre descriptivo a tu reporte, así como renombrar la hoja donde se encuentra
Y Así hemos concluido con la creación de nuestro reporte
Actualización
Si empezamos a agregar más archivos a la carpeta, con los datos de los ingresos obtenidos en otros días del mes …
Y damos clic en el botón Actualizar Todo o simplemente actualizamos la consulta
Vemos como nuestro reporte se actualiza automáticamente
Seleccionemos una categoría.
Como vemos, nuestro reporte se actualiza fácilmente y día a día podremos obtener la utilidad sin mayor esfuerzo.
Bueno amig@s de EFB, por el día de hoy es todo, espero que este artículo sea de gran ayuda y te de una visión más amplia de como crear soluciones con Power Query y las demás herramientas de Microsoft BI
Hasta la próxima.