Introducción a Columnas Calculadas
Las Columnas Calculadas, nuestro segundo tipo de Cálculo DAX disponible en Power Pivot para Excel. Recordemos que los tipos de Cálculos DAX o Cálculos Personalizados en Power Pivot son:
Medidas o Measures en Inglés (Campos Calculados en Excel 2013)
Columnas Calculadas o Calculated Columnas en Inglés
Tablas Calculadas o Calculated Tables en Ingles (NO disponible en Power Pivot a la fecha)
.
Como su nombre lo indica, las columnas calculadas permiten crear columnas adicionales en nuestras tablas tomando columnas ya existentes de las diversas tablas en el modelo de datos mediante le lenguaje DAX
– Columnas Calculadas Clásicas Vs Columnas Calculadas en Power Pivot
Por la descripción de Columnas Calculadas previa podríamos decir que hace exactamente lo mismo que las columnas calculas clásicas de tablas dinámicas.
Pues bien: NO, no hacen los mismo.
Y aunque el objetivo puede ir orientado en la misma dirección, lo cierto es que: Las columnas Calculadas clásicas de tablas dinámicas tienen muchas restricciones.
Sé que, tal vez se pueda argumentar que las columnas calculadas se pueden crear desde el origen de datos en la hoja de cálculo, con lo cual tenemos a nuestra disposición el motor principal de fórmulas en Excel e incluso las demás funcionalidades …
Y sí, ¡Es cierto! …
A pesar de lo anterior, las Columnas Calculadas de Power Pivot proporcionar un nuevo conjunto de posibilidades lo cual expanden nuestras opciones para el análisis de datos.
Por Mencionar Algunos:
La posibilidad de crear columnas que utilicen diversas del entretejido de manera fácil.
Poder crear columnas calculadas en tablas con más de un millón de registros.
Poder crear columnas calculadas complejas, que de otra manera requerirán programación en Excel, por ejemplo: cálculos iterativos.
– Vistazo al Modelo y Carga de Tablas a Power Pivot
Démosle un vistazo a nuetras tablas del día de hoy: Trabjaremos con un «viejo conocido», la tabla: telefonía datos. En cualquier caso te dejo el link de descargar del archivo, ya que es un una muestra de la población total que contiene dicha base de datos:
Ahora sí, démosle un vistazo a nuestra tabla clásica telefonía datos para refrescar nuestra memoria:
Perfecto, sin embargo, para los fines que deseamos también necesitaremos otra «tablita» que no está disponible en el archivo; para este caso una tabla que nos indique el número de habitantes que hay en las ciudades principales en el país de Colombia, por lo tanto debemos explorar en internet e importar datos desde la web.
Si buscamos en la Web, la primera fuente que nos encontraremos es Wikipedia:
URL: https://es.wikipedia.org/wiki/Anexo:Municipios_de_Colombia_por_poblaci%C3%B3n
No luce nada mal, ¿Cierto? – Importémosla a Power Pivot, para ello vamos a utilizar Power Query:
Vamos a la Pestaña DATOS, grupo OBTENER Y TRANFORMAR, desplegamos las opciones de NUEVA CONSULTA, nos dirigimos a DESDE OTRAS FUENTES, para finalmente pulsar clic encima de DESDE UNA WEB.
Con la acción precedente se despliega un cuadro de diálogo que nos pide la URL.
Presionamos clic en el botón Aceptar y aparece lo siguiente:
Como se puede apreciar aparecen 4 tablas, estas están en Wikipedia, seleccionamos la primera de ellas y en la parte inferior el botón cargar desplegamos las opciones y pulsamos clic en CARGAR EN …
Con lo anterior cargamos la tabla directamente en la venta de Power Pivot:
¡Cuidado Con El Perro!
Fijate que si dejamos la celda activa en alguna de las columnas que tienen valores numéricos y observamos el formato, nos encontraremos con que es de tipo texto, podemos discernirlo fácilmente porque: Los valores tienen espacios en vez de separador de miles, es decir: 2 358 253 y no 2,358,253 o en su defecto 2385253.
Este formato al momento de querer operar nos traerá problemas, entonces debemos cambiarlo en Power Query.
Para ello vamos a la pestaña DATOS, grupo OBTENER Y TRANFORMAR y pulsamos clic en el comando MOSTRAR CONSULTAS; esta acción hará que aparezca el panel de campos: Cosultas del libro (por si estaba oculto). Allí podremos apreciar que la consulta: Población de los municipios ….
Pulsamos clic derecho encima y en el menú que se muestra pulsamos clic en EDITAR.
A continuación nos encontramos en el editor o venta de Power Pivot.
Power Query & un Caso Espcial: ASCII 160
Resulta que los espacios que vemos en las columnas: Población cabecera, Población resto y Población total son bastante especiales, a lo que me refiero, es que no es el espacio típico que surge cuando pulsamos la barra espaciadora de nuestro teclado sino un diferente, uno que abunda mucho en la web, es el CARACTER 160 o ASCII 160.
Para que te hagas una idea básica, TAB también es una especie de espacio, pero con más amplitud.
El carácter ASCII 160 es un espacio especial que es compatible de mejor manera con las páginas de internet.
Qué cómo lo sé …
Bien, al tratr de remover de la forma tradicional en Power Query, es decir, clic derecho encima de la columna TRANSFORMAR y LIMPIAR no sucede nada, los espacios siguen alli.
Por eso lo sé, ahora tu también sabemos como =)
Qué cómo sé que es el carácter 160 o ASCII 160 …
Bueno mi amigo@ eso es tema para otro artículo, recordemos que aquí queremos hablar de Columnas Calculadas. No obstante, veamos como solventar este problema en Power Query para así trabajar de manera adecuada en Power Pivot.
– Limpiar ASCII 160 con Power Query
Lo pimertio, primerito; es copiar el caracter; para ello digita la fórmula CARACTER en cualquier celda de Excel y escribe el número 160 en su único argumento.
Cuando ejecutamos la fórmula no vemos nada, al fin y al cabo es un espacio, espcial si, pero un espacio al final de cuentas.
Ahora lo que debemos hacer es copiarlo y pegarlo por valor.
Yo le pego en la celda inmediatamente debajo, solo para que se vea mejor en la imagen, sin embargo puedes pegarlo como valor encima si lo deseas.
Ahora podrás notar que cuando podemos en modo de edición la celda, el punto de inserción esta corrido un pco a la derecha debido al espacio, lo selccionamos y los copiamos con Ctrl + C.
Porcedemos ir al la venta de Power Query como hicimos previamente. PANEL DE CONSULTAS, clic derecho en la consulta y EDITAR. Seleccionamos las tres columnas numéricas: Población cabecera, Población, resto y población pulsando clic en la etiqueta de columna mientras mantenems persionado la tecla Ctrl.
Una vez seleccionado vamos a la pestaña TRANSFORMAR, grupo CUALQUIER COLUMNA y clic en el comando REEMPLZAR VALORES
Enseguida se despliega un cuadro de diálogo el cual nos pide el carácter que deseamos reemplazar. Allí pegamos el carácter que copiamos con Ctrl + C previamente en la cata de texto Valor que buscar y en la caja de texto Reemplazar con ponemos coma (,)
Finalmente cambios el tipo de formato a Número Entero
Cerramos y mantenemos los cambios, actualizamos en el panel de consultas y vamos a Power Pivot.
– Relacionar Tablas en Power Pivot
Ahora debemos relacionarlas las tablas en Power Pivot, lo podemos hacer gracias a que el campo Municipio de la tabla que trajimos de Wikipedia solamente lista una sola vez las ciudades, así:
Bien, no cae nada mal un breve repaso de vez en cuando 😉
Ahora si a lo que vinimos: Columnas Calculadas.
Crear Columna Calculada
En la tabla de Wikipedia (Que por cierto tiene un nombre larguísimo cuando lo importamos, lo podemos cambiar en Power Query) nos posicionamos en la última columna: Agregar Columna; ¡No hay pierde! ¿No es así?
Bien, cuando dejamos la celda activa allí, pulsamos el signo igual (=) con lo cual se habilita la barra de fórmulas DAX y podemos empezar a escribir nuestra expresión.
Supongamos que la compañía de telefónos abarca un 70% de la Población Cabecera mas un 65% de la Población Resto para el país de Colombia
– Referencia a Columna con el Mouse
Podemos hacer referencia a la columna Población Cabecera, con nuestro Mouse, tal y como hacemos en Excel, múltiplicamos por 0.70
Notaras que hace referencia a la columna completa: [Población cabecera] y no a una celda individual, esto es una propiedad de Power Pivot.
Power Pivot trabaja con columnas completas y con tablas, no con celdas individuales.
– Referencia con el IntelliSense
Ok, ahora demos sumarle el 65% de la Población resto, aquí aprovecho para señalar que también podemos hacer referencia a una columna con el teclado, con lo cual abrimos paréntesis cuadrados y digitamos el nombre de la columna o algunas letras, pues un menú contextual aparecerá (su nombre técnico es IntelliSense) y nos irá filtrando de acuerdo a lo que vamos escribiendo, cuando ubiquemos el campo deseados podemos pulsar la tecla TAB para seleccionar dicho campo.
Multiplicamos por 0.65 y ponemos paréntesis.
De la fórmula podemos notar que los operadores matemáticos son los mismo que utilizamos en Excel.
Ya tenemos la columna calculada creada, ahora la renombramos pulsando clic derecho encima de ella y selccionamos Cambiar el nombre de la columna
La llamamos Población Efectiva.
Aprecia como la expresión es igual para toda la columna, sin embargo, los valores son diferentes para fila, esto es así porque Power Pivot ejecuta la fórmula fila a fila. Puedes asemejarla a las tablas estructuradas de Excel.
– Función DAX en Columna Calculada
En las Columnas Calculadas no estamos limitados solamente a los operadores.
También podemos utilizar todo el abanico de funciones del lenguaje DAX, por ejemplo, para nuestra columna población efectiva necesitamos redondear al número de abajo
(Es población al fin y al acabo)
Para ello podemos utilizar la función ROUNDDOWN, así:
Como se puede ver ROUNDDOWN tiene dos argumentos:
ROUNDDOWN (<Número>; <Decimales>)
En el primer aumento dejamos la expresión que habíamos creado previamente y en el segundo argumento ponemos un cero, el resultado luce así:
Las Columnas Calculdas en Power Pivot aparecen como Campos normales en el panel de campos de las tablas dinámicas, esto quiero decir que las podemos utilizar de igual manera en las distintas áreas de colocación.
Crear Columna Calculada Una «Pizca» Más Compleja
Supongamos que necesitamos dividir el número de llamadas de cada ciudad (El número de registros de cada ciudad, cda registro correponden a los datos de un llamda determinada) y dividirlo entre la Población Efectiva.
De cierto modo, podríamos decir que es el número de llamadas de cada habitante de la Población Efectivamente, evidentemente dará un número decimal muy pequeño porque el número de registros totales son 100 mil, sin embargo, recordemos que la base de datos completa tiene más de 500 mil registros, si deseas hacerlo con la original, este es link. (Aquí lo haremos con nuestra pequeña muestra de datos de manera ilustrativa)
¿Cómo lo hacemos?
Una Columna Calculada «salteada» con un par de funciones del lenguaje DAX.
– Función RELATEDTABLE
La función RELATEDTABLE() cual devuelve un subconjunto en la Tabla Base (Tabla Telefonía Datos «La más grande») de acuerdo a la relación que existe entre los campos que las vincula, dicho de otro manera; a la MSDN, evalúa una expresión en un contexto modificado por un filtro dado.
La definición RELATEDTABLE() devuelve un subconjunto en la Tabla Base (Tabla Telefonía Datos «La más grande»)puede ser confusa, vamos a «desmenuzarla» paso a paso para comprender un poco mejor a que va la función RELATEDTABLE.
Pero antes, su sintaxis:
RELATEDTABLE(<Nombre_de_la_Tabla)
Como podemos apreciar solamente cuenta con un argumento, y este es una tabla que esté relacionada con la tabla base; como en nuestro caso solo tenemos una tabla base y una tabla de búsqeuda, entonces, nustra expresión sería:
=RELATEDTABLE(OrigenDatosTabla)
Ahora sí, tratemos de comprender un poco mejor la función:
«La Función RELATEDTABLE() devuelve un subconjunto en la Tabla Base (Tabla Telefonía Datos «La más grande»)»
Es decir del tabla OrigenDatosTabla solamente retorna un número de registros o filas más pequeño (El número de columnas si se mantiene)
«De acuerdo a la relación que existe entre los campos vinculadas»
¿Cuáles son los campos que vinculas estas dos tablas?
Municipio y Ciudad
Como la fórmula se va a crear en la tabla de búsqueda, es decir, la tabla de Wikipedia, entonces, para la primera fila, mira en el campo Municipio (porque es el campo que tiene una relación con la otra tabla) y toma el valor de dicho campo.
Luego va a la otra tabla y hace el filtro de «Bogotá» de acuerdo al campo que las vinculas, para este caso: Ciudad
Y esto lo hace para cada una de las filas, una por una de manera itertiva internamente.
– Función COUNTROWS
Como la función RELATEDTABLE retornar una tabla para una fila determinada, entonces debemos resumir la tabla con una función de agregación para asi obtener un valor escalar que se pueda mostrar en la celda de la Columna Calculada.
Existen un montón de funciones de agregación así como funciones que retornan tablas, pero aquí vamos a utilizar la función COUNTROWS, la cual cuenta el número de filas o registros en una tabla (Es la que hace lo que queremos)
Sintaxis:
COUNTROWS(<Tabla>)
Comprendiendo las función RELATEDTABLE y COUNTROWS ya podemos crear la Columna Calculada desada:
=
COUNTROWS ( RELATEDTABLE ( OrigenDatosTabla ) ) / [Población Efectiva]
En Power Pivot:
Podemos crear incluso Columnas Calculadas más potentes, pero vamos a dejar el ejemplo para un vídeo.
Columnas Calculadas Vs Medidas
Las Columnas Calculadas suelen ser sobrevalorados por los estudiantes del lenguaje DAX en sus inicios, pues se tiende a utilizar demasiados cuando realidad prácticamente todo lo podemos hacer con medidas quien a su vez proporcionan modelo de datos más eficientes, algunas razones:
Las Columnas Calculadas se almacenan en memoria (consumen recursos)
Las Columnas Calculadas se recalculan cada vez que se actualiza el origen de datos
Se pueden crear referencias circulares
El algoritmo de almacenamiento de las columnas calculadas no es tan bueno como para las columnas importadas
En conclusión utiliza Medidas en lugar de Columnas Calculadas, se puede decir que el 90% de los escenarios se puede resolver con Medidas.
– Cuándo Utilizar Columnas Calculadas
Cuando se necesita un resultado para filtro en el modelo de datos
Cuando se crean categorías para las áreas de filas, columnas y filtros
Cuando el resultado de una columna calculada tiene cardinalidad baja (Más de esto futuros artículos)
Ok, suficiente de columnas calculadas por ahora =)
Espero haya sido de agrado el artículo, en próximas días más contenido (..y en vídeo también, they will be back)
¡Estamos en Sintonía Hasta la Próxima Oportunidad!