Cómo estructurar el Excel y prepararlo en Power Query

Introducción

Antes de construir cualquier visual en Power BI, hay que resolver una pregunta fundamental: ¿cómo estructuro mis datos para que el modelo sea flexible, eficiente y fácil de mantener?

En esta serie trabajaremos con un programa de migración cloud: cientos de servicios que deben moverse desde un datacenter on-premise hacia instancias EC2 en AWS y máquinas virtuales en Azure, coordinados con dos proveedores externos y organizados en oleadas (waves). Es exactamente el tipo de programa donde Power BI brilla: muchas variables, fechas críticas, estados cambiantes y múltiples stakeholders que necesitan vistas distintas del mismo dato.

Este primer artículo cubre los cimientos: el modelo de datos en Excel y las transformaciones en Power Query necesarias para que los dashboards funcionen correctamente.

1. El principio de la tabla plana

Power BI trabaja mejor con una tabla plana: una fila por unidad mínima de seguimiento (en nuestro caso, un servicio o servidor), y una columna por cada atributo de esa unidad. Sin celdas combinadas, sin subtotales embebidos, sin filas de resumen en medio de los datos.

💡 Si tu Excel tiene cabeceras multinivel, grupos colapsables o totales intercalados, Power Query puede limpiarlos — pero es mucho más fácil evitarlos desde el origen.

Nuestra tabla principal «All Services AWS» sigue este principio: cada fila es un servicio, y las columnas recogen desde el tipo de infraestructura hasta las fechas de migración planificada y real. Eso es lo que permite a Power BI agregar, filtrar y calcular con DAX sin restricciones.

2. Referencia de columnas del modelo

La siguiente tabla documenta las columnas de la hoja «All Services AWS», su tipo de dato correcto y su función en los dashboards:

💡 Las columnas de fecha son las más críticas: Power BI solo puede usar funciones de Time Intelligence (DATEADD, TOTALYTD, acumulados…) si están en formato Date. Si llegan como texto, los gráficos de Burn Up y Burndown no funcionarán.

3. Conectar el Excel a Power BI Desktop

El proceso de conexión es sencillo pero hay que seguir el orden correcto para evitar problemas de permisos y rutas relativas:

1. Abre Power BI Desktop → Home → Get Data → Excel Workbook.
2. Navega hasta el archivo All_Services_AWS_MOCK.xlsx y haz clic en Open.
3. En el Navigator, marca la hoja «All Services AWS» y haz clic en Transform Data (no en Load directamente — necesitamos pasar por Power Query).

⚠️  Si haces clic en «Load» sin pasar por Power Query, los tipos de dato se inferirán automáticamente y casi seguro las fechas llegarán como texto. Siempre usa «Transform Data» en el primer acceso.

4. Transformaciones en Power Query

Power Query es el ETL integrado de Power BI. Aquí preparamos los datos antes de que lleguen al modelo: limpiamos, normalizamos y añadimos columnas calculadas que sería imposible o ineficiente hacer en DAX.

Estos son los 10 pasos necesarios para nuestro modelo, en orden:

4.1 Normalizar el campo Environment

El campo Environment tiene valores duplicados en mayúsculas y minúsculas (PRO, pro, PRE, pre…). En Power Query, ve a la columna Environment → Transform → Replace Values y aplica cada sustitución:

"pro"  → "PRO"
"pre"  → "PRE"
"int"  → "INT"
"dev"  → "DEV"
"tst"  → "TST"

💡 Alternativamente puedes usar Transform → Format → UPPERCASE sobre toda la columna, que hace lo mismo en un solo paso.

4.2 Columna personalizada: EsMigrado

Necesitamos una columna booleana que identifique si un servicio está completamente migrado (estado «4) DONE»). Será la base del contador de migraciones reales en los KPIs y en la línea real del Burn Up.

En Power Query: Add Column → Custom Column → nombre: EsMigrado → fórmula:

EsMigrado = if [#"PROVEEDOR Implementation Status"] = "4) DONE"

  then 1

  else 0

💡 Usamos 1/0 en lugar de true/false para poder sumar directamente con SUMX en DAX sin necesidad de conversión.

4.3 Columna personalizada: Status_Color

Para los KPI cards y semáforos visuales necesitamos una versión simplificada del estado. Añade una columna «Status_Color» con esta lógica:

Status_Color = if [#"PROVEEDOR Implementation Status"] = "4) DONE"      then "Verde"
  else if [#"PROVEEDOR Implementation Status"] = "3) IN PROGRESS" then "Azul"
  else if [#"PROVEEDOR Implementation Status"] = "2) REQUESTED"   then "Amarillo"
  else if [#"PROVEEDOR Implementation Status"] = "5) DISCARDED"   then "Rojo"
  else "Gris"

Esta columna te permitirá usar reglas de color condicional en las tarjetas y tablas sin necesidad de lógica DAX adicional.

La correspondencia entre estado, color y comportamiento en DAX es la siguiente:

⚠️  Los servicios con estado «5) DISCARDED» deben excluirse de los ratios de progreso. En DAX usaremos CALCULATE con un filtro NOT DISCARDED para todos los KPIs principales.

4.4 Tipos de dato para columnas de fecha

Selecciona las siguientes columnas manteniendo Ctrl pulsado y asígnales Data Type: Date (no DateTime):

• fecha solicitud
• fecha de entrega
• Fecha de migración/monitorización
• Fecha esperada de migración
• Fecha de decomisionado
• Migration window

💡 Usar Date en lugar de DateTime simplifica los cálculos de acumulados y evita problemas de granularidad horaria en los gráficos de línea.

5. Tabla de fechas (Calendar Table)

Para que las funciones de Time Intelligence de DAX funcionen correctamente, Power BI necesita una tabla de fechas independiente, marcada como «Date Table». Esta tabla actúa como eje temporal al que se vinculan todas las columnas de fecha de la tabla principal.

Crea una nueva tabla en Power BI Desktop (Home → Enter Data, o directamente en DAX con New Table):

Calendario =
  ADDCOLUMNS(
      CALENDAR(DATE(2025,1,1), DATE(2026,12,31)),
      "Año",         YEAR([Date]),
      "Trimestre",   "Q" & QUARTER([Date]),
      "Mes Num",     MONTH([Date]),
      "Mes Nombre",  FORMAT([Date], "MMMM"),
      "Semana",      WEEKNUM([Date]),
      "Dia Semana",  FORMAT([Date], "dddd"),
      "AñoMes",      FORMAT([Date], "YYYY-MM")
  )

Una vez creada, ve a la vista de Modelo → clic derecho en la tabla Calendario → Mark as Date Table → selecciona la columna Date.

💡 Esta tabla es obligatoria para poder usar DATEADD, TOTALYTD, SAMEPERIODLASTYEAR y todas las funciones de inteligencia temporal que usaremos en los artículos de Burn Up y Burndown.

6. Relaciones en el modelo

Con la tabla de fechas creada, establece las relaciones en la vista de Modelo (el icono de diagrama en la barra lateral izquierda):

• Calendario[Date] → AllServices[Fecha esperada de migración]  (relación activa — para la línea planificada)
• Calendario[Date] → AllServices[Fecha de migración/monitorización]  (relación inactiva — para la línea real, activaremos con USERELATIONSHIP en DAX)
• Calendario[Date] → AllServices[Fecha de decomisionado]  (relación inactiva — para el Burndown)

⚠️  Power BI solo admite una relación activa entre dos tablas. Las demás deben ser inactivas y se activan dentro de cada medida DAX con USERELATIONSHIP(). Esto es el patrón estándar para modelos con múltiples roles de fecha.

7. Verificación antes de cerrar Power Query

Antes de hacer clic en Close & Apply, revisa esta checklist:

• La tabla se llama «AllServices» (sin espacios ni caracteres especiales)
• Las 6 columnas de fecha tienen tipo Date (icono de calendario en la cabecera)
• CPU y RAM (GB) tienen tipo Whole Number
• WAVE tiene tipo Text
• Las columnas EsMigrado y Status_Color están presentes
• El campo Environment no tiene valores en minúsculas
• No hay columnas «Column1», «Column2» ni columnas sin nombre

💡 Cada vez que actualices el Excel origen, Power BI refrescará automáticamente todos estos pasos. Por eso es importante que la estructura del fichero (nombres de columnas, hoja «All Services AWS») se mantenga estable entre actualizaciones.

Próximo artículo

Con el modelo de datos limpio y las relaciones establecidas, en el Artículo 2 construiremos las medidas DAX fundamentales:

• # Total Elementos (excluyendo Discarded)
• # Elementos Migrados (DONE acumulado real)
• # Elementos Planificados (acumulado por fecha esperada)
• % Completado
• Fecha Estimada de Fin (proyección lineal)

Estas medidas son la base del Burn Up chart que vimos en las capturas de pantalla: la visualización más potente para comunicar el estado real vs. planificado de un programa de migración ante dirección.