Objetivo del módulo:
Aprender a conectar, transformar y modelar datos desde múltiples fuentes en Excel utilizando Power Query y Power Pivot, crear relaciones entre tablas y aplicar modelos predictivos básicos, integrando la automatización con Power Automate.
1. Importar y transformar datos de múltiples fuentes
Power Query es una herramienta integrada en Excel que permite extraer, transformar y cargar (ETL) datos de distintas fuentes: archivos Excel, CSV, bases de datos SQL, SharePoint, Web, o Power BI.
1.1. Acceso a Power Query
Menú: Datos → Obtener y transformar datos → Obtener datos de…
Fuentes comunes:
• De archivo: Excel, CSV, XML, JSON.
• De base de datos: SQL Server, Access, Oracle, MySQL.
• De servicios online: SharePoint, Web, OData, API REST.
1.2. Transformaciones frecuentes
• Cambiar tipo de datos (texto, número, fecha).
• Quitar duplicados o valores nulos.
• Dividir columnas o combinarlas.
• Filtrar por condición.
• Crear columnas personalizadas (con fórmulas M).
• Combinar consultas (MERGE y APPEND).
Ejemplo práctico:
Importar dos archivos CSV de ventas y unirlos:
1. Datos → Obtener datos → Desde archivo → CSV.
2. Cargar ambos archivos a Power Query.
3. Usar “Combinar consultas” → “Agregar”.
4. Limpiar valores vacíos.
5. Cargar el resultado al modelo de datos.Power Query guarda cada paso en un historial editable, facilitando actualizaciones automáticas.
2. Creación de modelos de datos relacionales
Power Pivot permite crear modelos de datos que relacionan varias tablas de forma similar a una base de datos relacional, optimizando análisis complejos.
2.1. Activar Power Pivot
1. Pestaña Archivo → Opciones → Complementos → COM.
2. Activar Microsoft Power Pivot for Excel.
2.2. Crear relaciones
1. Importa las tablas desde Power Query.
2. Abre la ventana Administrar modelo de datos.
3. En la vista de diagrama, arrastra los campos clave entre tablas:
o Clientes[ID_Cliente] → Ventas[ID_Cliente]
o Productos[ID_Producto] → Ventas[ID_Producto]
2.3. Medidas DAX (Data Analysis Expressions)
Ejemplo de medidas:
Total Ventas := SUM(Ventas[Monto])
Promedio Ventas := AVERAGE(Ventas[Monto])
Crecimiento % := DIVIDE([Total Ventas]-[Total Ventas Año Anterior];[Total Ventas Año Anterior])
DAX permite realizar cálculos avanzados, comparaciones temporales y KPI.
3. Aplicar regresión lineal y predicción con Power BI y Excel
Tanto Excel como Power BI permiten construir modelos predictivos mediante regresión lineal y análisis de tendencias.
3.1. En Excel
1. Selecciona los datos de Ventas y Mes.
2. Usa el complemento Análisis de datos → Regresión (pestaña Datos).
3. Configura:o Variable dependiente: Ventas.
o Variable independiente: Mes.
4. Excel generará el modelo lineal, coeficientes e intervalo de confianza.
Fórmula predictiva:
Ventas = a + b * Mes
También puedes usar =PREVISIÓN.LINEAL() o =PREVISIÓN.ETS().
3.2. En Power BI
1. 2. 3. 4. Carga el modelo desde Power Query.
Inserta un gráfico de dispersión.
En “Análisis → Línea de tendencia” → activar “Regresión lineal”.
Observa la pendiente y el coeficiente R² (indica la precisión del modelo).
4. Introducción a la automatización con Power Automate
Power Automate conecta Excel con otros servicios (Outlook, SharePoint,
OneDrive, Power BI, etc.), automatizando flujos sin código.
Ejemplo de flujo automatizado
“Cada lunes, actualizar datos de ventas y enviar un informe de predicción.”
Pasos:
1. Crear un nuevo flujo:
Disparador: “Cada lunes a las 9:00.”
2. Acción 1: “Obtener archivo Excel de OneDrive.”
3. Acción 2: “Ejecutar script de Excel” (actualiza Power Query).
4. Acción 3: “Enviar correo con el archivo PDF generado.”
Puedes combinarlo con Power BI Alerts para enviar notificaciones automáticas cuando un indicador supere cierto umbral.