Actividad Práctica – Módulo 3: Power Query, Power Pivot y Modelos
Predictivos en Excel 365 con IA
Título:
Predicción Automatizada de Ventas con Power Query, Power Pivot y Power Automate
Objetivo general
Integrar datos de diversas fuentes, construir un modelo de datos relacional en Excel, generar predicciones de ventas mediante funciones de regresión lineal y automatizar el proceso de actualización y distribución del informe con herramientas de Inteligencia Artificial integradas.
Objetivos específicos
• • Aplicar Power Query para la conexión, transformación y limpieza avanzada de datos.
• • Crear un modelo relacional con Power Pivot y definir medidas DAX personalizadas.
• • Utilizar funciones estadísticas y predictivas (como PREVISIÓN.LINEAL o FORECAST.ETS) para estimar tendencias futuras.
• • Generar gráficos interactivos y dashboards que muestren patrones de comportamiento.
• • Automatizar la actualización y envío del informe mediante Power Automate o Office Scripts.
Datos de partida
Debes disponer de los siguientes archivos:
Clientes.xlsx → Contiene ID_Cliente, Nombre, Sector y Zona.
Productos.xlsx → Contiene ID_Producto, Categoría y Precio.
Ventas.csv → Contiene ID_Venta, ID_Cliente, ID_Producto, Fecha y Cantidad.
Instrucciones paso a paso
1. Importación y limpieza de datos (Power Query)
• Abre un libro nuevo en Excel y selecciona Datos > Obtener y transformar datos > Obtener datos > Desde archivo.
• Importa los tres ficheros (Clientes.xlsx, Productos.xlsx, Ventas.csv).
• En el Editor de Power Query:
– Elimina filas vacías o duplicadas.
– Cambia los tipos de datos (por ejemplo, Fecha como fecha y Cantidad como número entero).
– Crea una nueva columna calculada: Importe = [Cantidad] * [Precio] (tras combinar con latabla Productos).
• Cierra y carga los datos en el modelo.
2. Creación del modelo relacional (Power Pivot)
• Abre el panel de Power Pivot > Vista de diagrama.
• Establece relaciones:
– Ventas[ID_Cliente] → Clientes[ID_Cliente]
– Ventas[ID_Producto] → Productos[ID_Producto]
• Define medidas DAX:
– Total Ventas = SUM(Ventas[Importe])
– Promedio Ventas = AVERAGE(Ventas[Importe])
– Ventas por Cliente = [Total Ventas] / DISTINCTCOUNT(Clientes[ID_Cliente])
3. Análisis predictivo (IA y funciones estadísticas)
• En una nueva hoja, crea una tabla con los datos de ventas mensuales agregados.
• Usa la función PREVISIÓN.LINEAL(x, y_conocidos, x_conocidos) para proyectar las ventas de los próximos 3 meses.
• Alternativamente, aplica FORECAST.ETS() para una predicción más avanzada (suavizado exponencial).
• Agrega un gráfico de tendencia con las ventas históricas y las predicciones futuras, diferenciadas por color.
4. Automatización del proceso (Power Automate / Office Scripts)
• Desde Power Automate (flow.microsoft.com):
– Crea un flujo con el conector Excel Online (Business).
– Programa la actualización del archivo de Excel cada semana.
– Añade una acción para enviar el archivo por correo electrónico o Teams con el informe actualizado.
• (Opcional avanzado): Utiliza Office Scripts en Excel 365 para automatizar directamente la actualización y el cálculo del modelo.
5. Presentación de resultados
• Diseña una hoja resumen o dashboard con:
– Total de ventas global y promedio.
– Ranking de clientes y productos principales.
– Gráfico de tendencia con la predicción de los próximos meses.
– Fecha de última actualización automática.