Power Pivot y el modelo de datos de Excel explicados

Power Pivot es el complemento de Excel que te permite cargar varias tablas en un mismo libro, conectarlas con relaciones y escribir medidas en DAX encima—todo dentro de lo que Excel llama el modelo de datos. En pocas palabras, convierte a Excel de una calculadora de una sola hoja en una pequeña base de datos analítica, y usa el mismo motor y el mismo lenguaje de fórmulas que Power BI.

Qué es realmente el modelo de datos de Excel

Cuando creas una tabla dinámica normal, lee de un solo rango o una sola tabla. El modelo de datos es distinto: es una base de datos en memoria guardada dentro de tu archivo .xlsx. Puedes cargar varias tablas—ventas, productos, clientes, un calendario—y Excel las comprime con el mismo motor columnar (VertiPaq) que usan Power BI y Analysis Services.

Esto tiene dos consecuencias prácticas:

• Tamaño: los datos del modelo no viven en la cuadrícula de una hoja, así que no están limitados a las 1,048,576 filas de una hoja. El límite real pasa a ser la memoria de tu computadora y el tamaño del archivo, y Excel de 64 bits maneja modelos grandes mucho mejor que el de 32 bits.
• Relaciones: en vez de usar BUSCARX (XLOOKUP) para copiar la categoría del producto en cada fila de ventas, relacionas una sola vez la tabla Ventas con la tabla Productos y analizas por cualquier campo de cualquiera de las dos.

Power Pivot es la ventana a ese modelo. Te deja ver las tablas, dibujar relaciones en la Vista de diagrama, agregar columnas calculadas y crear medidas.

¿Lo tienes disponible?

Power Pivot viene incluido en Excel para Windows de Microsoft 365 y en la mayoría de las versiones de escritorio recientes, aunque su disponibilidad ha cambiado según la edición a lo largo de los años. Excel para Mac y Excel para la web no ofrecen la ventana de Power Pivot. Revisa la página de soporte de Power Pivot de Microsoft para tu versión exacta. Si lo tienes, verás una pestaña Power Pivot en la cinta; si no, ve a Archivo > Opciones > Complementos, elige Complementos COM y activa "Microsoft Power Pivot for Excel".

Power Pivot vs. tablas dinámicas normales

Si ya dominas las tablas dinámicas estándar (si no, empieza con nuestra guía de tablas dinámicas), el modelo de datos es el siguiente paso natural.

Cómo crear tu primer modelo de datos paso a paso

Usemos un escenario típico de pequeño negocio: una tabla Ventas (Fecha, IDProducto, IDCliente, Cantidad, Ingreso), una tabla Productos (IDProducto, Producto, Categoría, Costo) y una tabla Clientes (IDCliente, Cliente, Ciudad).

1. Convierte cada rango en tabla. Haz clic dentro de cada rango, presiona Ctrl+T y cámbiale el nombre en la pestaña Diseño de tabla (Ventas, Productos, Clientes).
2. Agrega cada tabla al modelo de datos. Con una tabla seleccionada, ve a Power Pivot > Agregar a modelo de datos. Otra opción es cargar los datos con Power Query y marcar "Agregar estos datos al modelo de datos" en el cuadro Cargar en—la mejor alternativa cuando los datos vienen de archivos o bases de datos.
3. Crea las relaciones. En la ventana de Power Pivot, abre la Vista de diagrama y arrastra Ventas[IDProducto] hasta Productos[IDProducto], y luego Ventas[IDCliente] hasta Clientes[IDCliente]. Cada relación debe ser de uno a varios: un producto, muchas filas de ventas.
4. Agrega una tabla de calendario. Crea una tabla Fecha con una fila por día que cubra todo tu rango de fechas, relaciónala con Ventas[Fecha] y márcala como tabla de fechas (Diseño > Marcar como tabla de fechas). Eso es lo que hace funcionar la inteligencia de tiempo.
5. Escribe medidas. En el área de cálculo debajo de una tabla, o desde Power Pivot > Medidas > Nueva medida en Excel, define tus cálculos en DAX.
6. Crea una tabla dinámica desde el modelo. Insertar > Tabla dinámica > Desde el modelo de datos. La lista de campos ahora muestra todas tus tablas, y puedes poner Categoría en filas y Clientes[Ciudad] en columnas aunque esos campos estén en tablas distintas.

Este diseño—una tabla de hechos con las transacciones rodeada de tablas de búsqueda—se llama esquema estrella. Nuestro artículo sobre el esquema estrella explica por qué funciona tan bien.

Tus primeras medidas en DAX

Las medidas son fórmulas que se calculan al momento de la consulta, según los filtros que aplique la tabla dinámica. Estas son algunas que usarás en casi cualquier modelo. Recuerda que DAX se escribe igual en todos los idiomas, con funciones en inglés:

Ingreso Total := SUM ( Ventas[Ingreso] )

Unidades := SUM ( Ventas[Cantidad] )

Costo Total :=
SUMX ( Ventas, Ventas[Cantidad] * RELATED ( Productos[Costo] ) )

Margen Bruto := [Ingreso Total] - [Costo Total]

Margen Bruto % := DIVIDE ( [Margen Bruto], [Ingreso Total] )

Clientes Únicos := DISTINCTCOUNT ( Ventas[IDCliente] )

Ingreso YTD := TOTALYTD ( [Ingreso Total], 'Fecha'[Fecha] )

Algunos detalles importantes. := es la sintaxis para definir medidas en el área de cálculo de Power Pivot; en el cuadro Nueva medida escribes el nombre en un campo y la fórmula (empezando con =) en otro. Ojo: según tu configuración regional, DAX en Excel puede pedir punto y coma (;) como separador en lugar de coma. RELATED trae un valor del lado "uno" de la relación, y así Costo Total lee el costo de cada producto sin columna de búsqueda. DIVIDE evita errores por división entre cero. Y TOTALYTD solo funciona bien con una tabla de fechas completa y continua.

Medidas vs. columnas calculadas

Una columna calculada se calcula una vez por fila al actualizar los datos y se guarda en el modelo, así que ocupa memoria. Una medida se calcula al vuelo para cada celda de la tabla dinámica. Regla práctica: si quieres filtrar o segmentar por ese valor (como una etiqueta "Rango de precio"), usa una columna calculada; si quieres agregarlo (sumas, proporciones, promedios), usa una medida. Nuestra guía de DAX básico profundiza en esto y en el contexto de filtro.

Errores comunes que debes evitar

• Relacionar columnas con duplicados en ambos lados. La clave de la tabla de búsqueda debe ser única. Si Productos tiene el mismo IDProducto dos veces, Power Pivot no aceptará la relación de uno a varios.
• Segmentar por la fecha de Ventas en vez de una tabla de fechas. Funciona hasta que necesitas nombres de mes, años fiscales o acumulados del año—y ahí se rompe. Crea el calendario desde el principio.
• Convertir todo en columnas calculadas. Infla el archivo sin necesidad.
• Importar columnas que no necesitas. Cada columna extra cuesta memoria. Quita IDs, comentarios y marcas de tiempo que nunca vas a analizar.
• Mezclar tipos de datos en las claves. Si IDProducto es texto en una tabla y número en la otra, la relación no va a coincidir. Corrige los tipos en Power Query.

De Power Pivot a Power BI

Todo lo que aprendes en Power Pivot te sirve después: tablas, relaciones, esquema estrella, medidas DAX y tablas de fechas son los mismos conceptos en Power BI Desktop. De hecho, Power BI Desktop puede importar el modelo de datos, las consultas y las medidas de un libro de Excel (Archivo > Importar), lo que convierte a Excel en un buen lugar para practicar. Power BI conviene cuando necesitas actualización programada, compartir en la web o seguridad a nivel de fila. Nuestra comparación de Excel vs. Power BI explica esa decisión en detalle.

Próximos pasos

Abre un libro que ya uses, convierte sus rangos en tablas y agrégalas al modelo de datos. Crea una relación, una tabla de calendario y tres medidas—Ingreso Total, Margen Bruto % e Ingreso YTD—y luego arma una tabla dinámica desde el modelo. Cuando eso te salga natural, estarás listo para estudiar el contexto de filtro y CALCULATE, el corazón de DAX tanto en Excel como en Power BI.

Mira: lecciones en video

▶

▶

Preguntas frecuentes