Introducción a power pivot

Power Pivot es un complemento de Excel que permite almacenar grandes cantidades de datos (más de una hoja que está limitada a un millón) optimizando el espacio que ocupa el fichero Excel. Para acceder a este complemento tienes que clicar “Power Pivot” desde la cinta de opciones. Si no tienes esta pestaña, tendrás que habilitar el complemento: Archivo – Opciones – Complementos – Complementos COM, activar la casilla Microsoft Office Power Pivot y aceptar. Ahora podemos empezar.

Veremos a continuación como importar datos directamente desde Power Pivot (sin pasar por Power Query), como crear columnas y campos calculados, y como explotar los datos.

Empezamos, entonces, desde la cinta de opciones de Power Pivot. Aquí encontramos “Agregar a modelo de datos”, que sirve para importar a Power Pivot datos de un rango o una tabla del mismo archivo Excel. Es igual que lo que vimos para Power Query. Si clicamos “Administrar” accedemos al espacio de trabajo de Power Pivot. Aquí, en la pestaña “Inicio”, tenemos otras opciones de importar datos. Son las conexiones que encontramos también para Power Query, así que no me detendré mucho, pero vamos a ver un caso concreto al importar un archivo en esta Práctica Principiante, mientras que en otro post veremos cómo importar desde una base de datos con o sin consulta SQL.

En la siguiente ventana tendremos que elegir la hoja donde están los datos, y, andes de clicar “Finalizar”, podemos ir a “Vista previo y filtro” donde podemos filtrar los datos en diferentes columnas si queremos reducir las filas. Seleccionamos todas las hojas del fichero Excel. Después de dar a “Finalizar”, empezarán a cargarse los datos, damos a “Cerrar” y se genera una hoja en Power Pivot con nuestros datos importados. Si tenemos que actualizar los dato porque hemos modificado el fichero de origen de datos, seleccionamos desde “Inicio” el botón “Actualizar” (actualiza la conexión de la hoja en la que estamos). Podemos también actualizar todo clicando la flechita debajo de “Actualizar” y seleccionar “Actualizar todo”.

Ahora podemos empezar a modificar el formato, agregar columnas calculadas, agregar campos calculadas, y crear una tabla dinámica a partir de estos datos. Lo que explicaré a continuación será válido también para datos importados desde Power Query y cargados en el modelo de datos (Power Pivot), menos modificar el formato. Si los datos se han importado con Power Query tendrás que modificar el formato en Power Query.

Para cambiar el formato en Power Pivot podemos seleccionar la columna deseada y desde “Inicio” tenemos un apartado “Formato”. Aquí podemos elegir el formato que más nos convenga. El formato es importante principalmente para dos cosas: los cálculos que nos permite hacer, y la como visualizamos los datos. En cuanto a los cálculos, no podemos por ejemplo sumar si el formato no es numérico (o de fechas), o no podemos utilizar fórmulas de fecha si el formato no es una fecha. En cuanto a la manera de visualizar los datos, si cambiamos el formato en Power Pivot esto cambia la manera de visualizar los datos en la tabla dinámica (para crear una tabla dinámica a partir de los datos de Power Pivot tenemos que clicar “Tabla dinámica”).

A la hora de modificar datos en Power Pivot es importante entender la diferencia entre las fórmulas en Dax (lenguaje de fórmulas en Power Pivot), y las fórmulas en una hoja de cálculo de Excel. Mientras en una hoja de Excel las fórmulas hacen referencias a una o más celdas (definidas por filas, columnas y hojas), en DAX las fórmulas hacen referencia a columnas y tablas. En DAX no puedo seleccionar un dato en base al número de fila y el nombre de la columna, porque los datos de una columna no tienen un orden implícito. Lo que puedo hace es buscar un dato de una columna (Pax) filtrando otra columna (Origen), pero esto no tienen nada a que ver con la posición de la fila.

Es un concepto que se usa en las bases de datos y es importante no olvidarlo. Esto quiere decir que, no podemos ir a buscar una “celda” concreta en Power Pivot, sino que tenemos que jugar con tablas, columnas y filtros.

El segundo concepto importante a tener en cuenta es la diferencia entre columnas y campos calculados (medidas). Una columna calculada utiliza fórmulas que hacen cálculos por fila, como por ejemplo sumar el valor de dos columnas, cuyo resultado será una nueva columna donde cada fila suma el valor de la misma fila de las dos columnas elegidas. Una medida calculada utiliza fórmulas para hacer cálculos agregando los datos, como por ejemplo la suma de los datos de una columna. Es como cuando añadimos una variable numérica en “Valores” de una tabla dinámica, donde tenemos que especificar el tipo de agregación (cuenta, suma, promedio, mínimo, máximo etc.). Aunque esta sea la regla general, hay excepciones. Para crear cálculos más complejos existen fórmulas que permiten agregar datos en columnas calculadas, y hacer cálculos por filas en las medidas calculadas, pero lo veremos en otro post. De hecho, el campo calculado, se calculará a la hora de usarlo en una tabla dinámica dependiendo de las filas y columnas.

Como regla general, para decidir si utilizar una columna o campo calculado, podemos pensar en su uso en una tabla dinámica. Si lo vamos a utilizar en filtros, filas o columnas, usa una columna calculada. Si lo vas a utilizar en Valores, usa un campo calculado.

Un matiz final antes de empezar. Las fórmulas DAX tienen parecidos a las fórmulas Excel, pero están en inglés. Entonces no buscaremos “SUMA()”, sino “SUM()”, por ejemplo.

COLUMNAS CALCULADAS

Vamos a ver primero las columnas calculadas. Para crear una nueva columna podemos hacer doble clic a la derecha de nuestra tabla en “Agregar columna”, escribir el nombre que le queramos dar en el teclado a “Intro”. Después nos colocamos con el cursor en la barra de fórmulas y podemos empezar a escribir.

Vamos a crear una columna de año “Year” en la hoja “pax tren” sacando el año de la columna “Date”. Aquí notamos varias cosas:

• El formato de las fórmulas es similar a las de Excel, “=nombre_función(argumento)”.
• La referencia a una columna se hace clicando la columna o escribiendo el nombre entre corchetes [].
• Antes de la referencia a la columna aparece el nombre de la tabla o hoja. No es obligatorio si se hace referencia a la misma tabla donde está la fórmula, pero nota que si el nombre de la tabla tiene espacios tiene que ir entre comillas simples ‘…’.
• Aunque no lo veas aquí porque solo tenemos un argumento, los diferentes argumentos de una formula se separan por punto-y-coma.
• Al escribir la formula sale una ayuda de autocompletado con descripción de la función, y al abrir el paréntesis aparece una ayuda que nos dice los argumentos necesarios. Los argumentos que aparecen entre corchetes son los opcionales.

Veremos por encima algunas de las principales funciones DAX dado que no serviría de mucho explicarlas una a una. Sin embargo, puedes consultar esta página web que recoge todas las funciones DAX: https://docs.microsoft.com/es-es/dax/dax-function-reference.

Funciones de tiempo:

• YEAR(fecha): saca el año de una fecha -> creamos Year desde la columna Date.
• MONTH(fecha): saca el mes de una fecha -> creamos Month desde la columna Date.
• DAY(fecha): saca el día del mes de una fecha -> creamos Day desde la columna Date.
• WEEKDAY(fecha, tipo): saca el día de la semana (1 a 7) y podemos especificar con el segundo argumento con que día de la semana empieza, como se puede ver en la ayuda a la hora de completar la fórmula) -> creamos Weekday desde la columna Date.

Hay muchas más funciones, incluidas de horas. Algunas funciones interesantes llamadas “de inteligencia de tiempo” que por ejemplo calculan el mismo periodo del año anterior, o el último día del mes etc. Puedes revisarlas usando el enlace que di anteriormente.

Funciones de texto:

• [columna1]&[columna2]: este ejemplo concatena el texto de dos columnas -> creamos Origen-Destino desde las columnas ORIGEN y DESTINO. Incluimos un guion usando [columna1]&”-“&[columna2].
• LEFT(columna texto; n. caracteres): extrae un número de caracteres del texto de una columna a partir del inicio del texto -> creamos Origen_abr usando las primeras tres letras de Origen.
• RIGHT(columna texto; n. caracteres): igual que el anterior pero desde la fin del texto -> creamos Year_2 sacando las últimas dos cifras del la columna Year.
• SUBSTITUTE(columna de referencia con texto; texto a sustituir; texto de sustitución): permite sustituir una cadena de texto por otra -> creamos ORIGEN_limpio donde sustituimos “AGDE” con “Agde”. Recuerda que, como en Excel, el texto va siempre entre comillas “…”.

Funciones matemáticas:

En el caso de funciones matemáticas tenemos que usar los operadores “+,-,/,*” para sumar, restar, dividir o multiplicar el valor de cada fila de diferentes columnas. Es importante notar que no podemos utilizar las funciones de agregación como SUM() en las columnas. Vamos a ver qué pasa utilizándola. Vemos que suma en cada fila toda la columna de referencia.

Usamos el operador “*” para modificar los pax, los multiplicamos por 2 (Pax_ajustado).

Funciones de lógica:

• IF(prueba lógica; resultado si verdadero; resultado si falso) -> supongamos que durante el mes de abrir no circuló ningún tren y tenemos que poner a “0” todos los pax de este periodo; creamos la columna Pax_con_supresiones con la condición correspondiente.

Podemos añadir más condiciones en la prueba lógica con las funciones AND() y OR(), o añadiendo diferentes condiciones después de “&&” o “||”, pero lo veremos más en detalle en la Práctica avanzado. Aquí tienes un enlace donde se explican los diferentes operadores de DAX: https://docs.microsoft.com/es-es/dax/dax-operator-reference

CAMPOS CALCULADAS

Los campos calculados son medidas cuyo calculo afecta al conjunto de filas, es decir que no calculan fila por fila como en las columnas calculadas, sino que agregan los datos de todas las filas para hacer cálculos. Por ello, como norma general, hay que especificar qué tipo de agregación queremos cuando llamamos a una columna (suma, promedio, mínimo, máximo etc.). Para crear una medida calculada debemos situarnos debajo de la tabla, clicar en una celda y escribir el nombre de la medida seguido de “:=”. Creamos por ejemplo Suma_de_pax, sumando la columna Pax. Como puedes notar he tenido que especificar “SUM()” para que sume todas las filas de Pax.

En este caso, como ya tenemos una columna Pax, podríamos simplemente añadir la columna en “Valores” de una tabla dinámica y elegir la agregación “sumar” para obtener el resultado. La única diferencia es que si usamos en “Valores” Suma_de_pax se agregará con la suma de los datos, sin posibilidad de modificarla. Por otro lado, la ventaja es que Excel no nos dará por defecto otro tipo de agregación.

Pero vamos a ver algún cálculo más interesante. Supongamos que queremos ver el ratio de Pax_con_supresiones entre el total de Pax. Podemos crear una medida calculada que divida la suma de Pax_con_supresiones entre la suma de Pax. Además, le cambiamos el formato para que aparezca en %. Fíjate que tanto Pax como Pax_con_supresiones tienen que ser sumados antes de calcular el ratio.

En las medidas calculadas podemos también utilizar otras medidas calculadas. Por ejemplo, el Ratio_pax anterior lo puedo calcular usando Suma_de_pax en lugar de sumar la columna Pax. Cuando hago esto no tengo que sumar la Suma_de_pax, sino que la añado tal cual, dado que esta medida ya implica que estoy agregando los datos a través de una suma.

Vamos a ver ahora que pasa en la tabla dinámica. Vamos a inicio, clicamos en “Tabla dinámica”, decidimos si queremos la tabla en la hoja existente o en una nueva y aceptamos. Primero, verás que puedes elegir usar cualquiera de las tablas de Power Pivot. Elegimos “pax tren”, que es donde hemos creado columnas y campos calculados. Cuando creas una tabla dinámica de una tabla de Power Pivot específica puede que al volver desaparezcan las demás. Lo que tienes que hacer para visualizarlas es clicar en “Todas” donde pone “Campos de tabla dinámica”.

Imagina que tengas que calcular el precio medio teniendo una columna de Pax y una de Ingresos. ¿Usarías una columna o una medida calculada?

Verás que las medidas calculadas se diferencian de las columnas por empezar por “fx”. Añadimos Ratio_pax en Valores y vemos que automáticamente presenta el dato en formato porcentual.

Añadimos el campo ORIGEN_limpio y vemos que calcula el ratio para cada Origen. Lo que está haciendo es, para cada Origen, calcular la suma de Pax_con_supresiones y la divide por la suma de Pax.

Si agregamos a la tabla Pax y Suma_de_pax, veremos que para el segundo no nos permite cambiar el tipo de agregación.

En conclusión, utilizaremos los campos calculados en lugar de las columnas calculadas cuando nuestra métrica agrupa de alguna manera los datos. Esto significa que, como regla general, cuando usamos funciones de agregación tenemos que crear un campo calculado:

• SUM()
• AVERAGE()
• COUNT() -> cuenta filas con números
• COUNTA() -> cuenta filas no vacías
• DISTINCTCOUNT() -> cuenta el número de elementos únicos
• MIN()
• MAX()
• P() -> desviación típica (usada en estadística)
• Otras funciones de matemática y estadística

Además, podemos usar una combinación de estas para hacer cálculos como ratios, variaciones etc. como hemos visto en el ejemplo. Hay funciones avanzadas que permiten, además, incluir condiciones y hacer cálculos por filas, pero las veremos en otros posts.