Introducción

Cuando se construye un modelo de datos en Power BI, suele ser común la necesidad de ampliar una tabla procedente de un origen de datos con columnas adicionales, cuyo contenido se derive de los datos existentes en dicha tabla. En estos casos en los que los valores de las nuevas columnas van a ser pre-calculados y materializados de forma estática en el modelo y la lógica de negocio a implementar deriva del resto de columnas, es casi siempre preferible utilizar Power Query para definirlas, principalmente porque obtendremos un mayor nivel de compresión que usando columnas calculadas en DAX, y una columna con mejor compresión ocupará menos en memoria y normalmente nos proporcionará mejores niveles de rendimiento.

Un caso común, corresponde a la creación de columnas que nos ayuden a clasificar valores numéricos de manera que podamos discretizar esa variable y entender de forma clara la distribución de estos valores en nuestro modelo. Esta situación surge en multitud de escenarios, por ejemplo, cuando necesitamos clasificar facturas en función de la fecha de vencimiento, productos por precio de venta, empresas por cifra de negocios o empleados por grupos de edad. En este artículo vamos a ver distintas opciones para crear estas columnas con Power Query.

Utilizando una columna condicional

Probablemente, la primero que nos venga a la mente sea el utilizar la funcionalidad de columna condicional en el editor de consultas. Imaginemos que queremos crear una columna para clasificar las horas del día en un restaurante para poder agruparlas en periodos distintos y utilizar estos valores para segmentar nuestras medidas. Disponemos de una dimensión horaria, siendo estas algunas de sus columnas:

Podemos utilizar la columna HoraNumero para, mediante una columna condicional, comprobar para cada fila de la tabla en que rango se encuentra el valor y asignarle la clasificación correspondiente:

Así, podemos agrupar los valores numéricos como nos interese en cada caso y obtendríamos el resultado deseado, poder segmentar nuestras medidas en función de los valores de nuestra columna de clasificación:

En este caso, el número de valores de clasificación es relativamente pequeño, pero la tarea se volvería tediosa y muy poco eficiente en cuanto el número de estos valores creciera un poco. A continuación, vamos a ver cómo podemos crear una función personalizada en Power Query que permita realizar la clasificación de una manera más simple y que a la vez nos sirva de plantilla que podamos reutilizar, modificándola para adaptarla a cualquier necesidad de agrupación.

Función personalizada con Power Query

Para el escenario anterior, podríamos crear la siguiente función:

(Input) => 
     let 
       ClasificacionPeriodo = 
         { 
             {(x)=>x<3, "Madrugada"},
             {(x)=>x<10, "Cerrado"}, 
             {(x)=>x<12, "Mañana"}, 
             {(x)=>x<16, "Mediodia"}, 
             {(x)=>x<20, "Tarde"},  
             {(x)=>true, "Noche"} 
         }, 
       Resultado = List.First(List.Select(ClasificacionPeriodo, each _{0}(Input))){1} 
     in 
       Resultado

Donde la variable ClasificacionPeriodo contiene una lista de listas, porque cada elemento de la lista principal contiene una función que nos va a devolver un valor de true o false, un booleano, y un valor de texto que va a ser al final el resultado que vamos a tener en la columna que vamos a crear para agrupar los valores numéricos.

Finalmente, el paso Resultado, que será el output de nuestra consulta, comprueba la variable Input, que será nuestra columna HoraNumero, con la lista y devuelve la coincidencia correcta o el valor de la cláusula «else» si no se encuentra ninguna coincidencia («Noche» en el caso con el que estamos trabajando).

Para invocar la función nos iríamos a nuestra dimensión horaria y, desde la pestaña agregar columna seleccionaríamos «Invocar función personalizada» y posteriormenete, introduciríamos los parámetros necesarios:

Y ya tendríamos nuestra columna creada.

Utilizar una tabla con los rangos de clasificación

Por último, vamos a ver cómo podemos pasar a la función los valores de los rangos de clasificación en una tabla, que nos será muy útil si estos valores son dinámicos en el origen, o si queremos darle al usuario la capacidad de modificarlos sin la necesidad de que alguien manipule el código M.

Para seguir con el mismo ejemplo del artículo, imaginemos que tenemos la siguiente tabla con los valores a usar para crear los rangos y las distintas clasificaciones:

Veamos el código de la función y luego lo comentamos:

let
     Clasificar = (tabla as table, numero as number) => 
         let 
             Listas =  Table.ToRows(tabla),
             Seleccionados = List.Select(Listas, each {0} <= numero),             SeleccionadosOrdenados = List.Sort(Seleccionados,{each {0},Order.Ascending}),
             Resultado = List.Last(SeleccionadosOrdenados){1} 
         in 
             Resultado
 in
     Clasificar

En primer lugar, guardamos la declaración de la función en la variable Clasificar. Esta función tendrá dos parámetros, la tabla con las clasificaciones a utilizar y el número que clasificaremos, que será nuestra columna HoraNumero de la dimensión horaria del modelo.

A continuación, en el bloque de instrucciones de la función utilizaremos Table.ToRows para convertir la tabla en una lista anidada de filas, de manera que cada fila se convierta en un elemento de una lista y a su vez los campos de cada fila se conviertan en una lista. Posteriormente utilizamos la función List.Select para quedarnos solamente con los valores de la tabla de rangos que son menores o iguales que el valor del campo que queremos clasificar.

Más tarde, como queremos que el algoritmo funcione aunque los valores de la columna con los limites no estén ordenados de menor a mayor, vamos a utilizar la función List.Sort para ordenar los valores dentro de nuestra lista resultante de forma ascendente, para, posteriormente, utilizar la función List.Last que devuelve el último elemento de la lista, en este caso, el correspondiente al elemento que contiene los datos del registro de la tabla de clasificación que corresponde al valor numérico a clasificar.

Ya solo nos quedaría invocar esta nueva función personalizada desde nuestra dimensión horaria de la siguiente forma:

Y obtendríamos el resultado deseado, que en este caso coincide con lo obtenido usando los otros 2 métodos que hemos visto anteriormente en este artículo.

Conclusión

En este artículo hemos visto distintas formas de crear columnas de agrupación de valores numéricos, siendo la mejor de ellas, desde el punto de vista de la eficiencia y la reutilización del código, la opción de crear una función personalizada en Power Query, optando por pasar a dicha función una tabla externa que contenga los valores para cada grupo en el caso de que estos puedan ser modificados en el origen en el futuro.

El disponer de una función de este tipo a la que poder recurrir, nos será útil en multitud de escenarios distintos donde necesitemos discretizar valores numéricos. En cuanto a la visualización, podemos servirnos de estas nuevas columnas para crear, por ejemplo, histogramas como el siguiente sin necesidad de utilizar visualizaciones personalizadas: