Crear funciones personalizadas en Power Query

En la entrada Transformar datos con Power Query vimos que la mayoría de las transformaciones que llevamos a cabo en el editor de consulta las podemos realizar de forma asistida, usando la interfaz gráfica, pero en ocasiones necesitamos crear funciones personalizadas en Power Query para realizar algunas transformaciones que de otra forma no serían posibles.

Para crear funciones personalizadas en Power Query debemos conocer el lenguaje que está por debajo del editor de consultas, informalmente conocido como M.

El lenguaje M

M es un lenguaje funcional, sensitivo a mayúsculas y minúsculas por lo que hay que ser cuidadoso con la sintaxis. Incluye una gran cantidad de funciones integradas que podemos utilizar cuando realizamos transformaciones. Las funciones se agrupan en categorías y se les puede pasar parámetros.

Toda la documentación de M, incluyendo las especificaciones del lenguaje, los tipos de datos soportados y las funciones las puedes encontrar en Power Query M Reference.

Si abrimos el editor avanzado de cualquiera de las consultas veremos un conjunto de fórmulas separadas por coma unas de otras y encerradas entre las instrucciones let e in. Cada fórmula constituye un paso de transformación y están formadas por dos partes separadas por el signo “=”. A la izquierda del signo se declara una variable a la que se le asigna el resultado de evaluar la expresión de la derecha. La expresión puede ser el llamado a una función, ya sea de la biblioteca estándar o nuestra. La mayoría de las veces, cada paso se basa en el resultado del paso anterior refiriéndose a él por el nombre de variable.

A continuación de la instrucción in se escribe el resultado a devolver que por lo general es el nombre de la variable del último paso.

Ejemplo de sintaxis del lenguaje M

 let 
     x = 2, 
     y = 3, 
     z = x + y 
 in 
     z 

En el ejemplo se han declarado tres variables: x, y, z. A las dos primeras variables se les ha asignado un valor numérico y al tercera se le ha asignado una expresión que es la suma de los valores contenidos en las otras dos variables. Como resultado se ha devuelto el valor de la última variable.

Funciones

Una función es una asignación de un conjunto de valores de entrada a un solo valor de salida. El valor de salida puede ser un número o un texto, y se puede incluir en un paso como cualquier otra expresión.

Llamando a una función

El llamado o ejecución de una función se puede realizar desde cualquiera de los pasos de la consulta, en el miembro derecho de la fórmula.

Las funciones se ejecutan usando la siguiente sintaxis:

<nombre_categoría>.<nombre_funcion>(<lista de parámetros>)

donde:

<nombre_categoría> es la categoría a la que pertenece la función.

<nombre_funcion> es la función que queremos llamar.

<lista de parámetros> son los valores que requiere la función para poder ejecutarse, pueden ser opcionales. Generalmente, el primer parámetro de la función corresponde al valor devuelto por la fórmula del paso anterior.

Ejemplo de llamado de función

let 
     x = 2, 
     y = 3, 
     z = Number.Power(x,y) 
 in 
     z

En este ejemplo se ha usado la función Power en el tercer paso de la consulta. Power pertenece a la categoría Number y devuelve como resultado un número elevado a una potencia. Admite dos parámetros: el valor base y la potencia.

Declarando una función

En un paso de fórmula podemos usar, además de las funciones de la biblioteca estándar de Power Query, una función personalizada. Podemos crear funciones personalizadas en Power Query dentro de una consulta o como una consulta separada. Si la definimos dentro de una consulta solo podremos usarla en esa consulta. Si por el contrario la escribimos en una consulta separada podremos utilizarla en múltiples consultas.

La declaración de la función consta de una lista de parámetros separados por coma y encerrados entre paréntesis y a continuación el símbolo (=>) que indica que lo que sigue es el cuerpo de la función.

Si la función solo contiene un paso este se escribe a continuación. Si la función requiriera de más de un paso, estos irían encerrados entre let e in y separados por coma.

Finalmente se escribe la instrucción in indicando el resultado a devolver, el nombre de la función.

Ejemplo de sintaxis para crear una función:

let
    MyFunction1 = (parameter1, parameter2) => (parameter1 + parameter2) / 2 
in 
    MyFunction1 

La variable Myfunction1 es el nombre de la función. El cuerpo de la función solo tiene una expresión y se escribe a continuación del símbolo =>

Ejemplo de sintaxis de una función con más de un paso:

let
    MyFunction2 = (parameter1, parameter2) => 
    let 
        value = parameter1 + parameter2, 
        result = value / 2 
    in 
       result 
in
   MyFunction2

En este caso la función MyFunction2 contiene dos pasos y van encerrados entre las instrucciones let e in.

La información del tipo de dato se puede incluir de manera explicita en la declaración tanto de los parámetros como del valor de retorno de la función. Si no la incluimos el tipo de datos será implícito. Los valores implícitos son de tipo any, es similar al tipo objeto en otros lenguajes. Todos los tipos en M derivan del tipo any.

Ejemplos de sintaxis para crear una función con declaración de tipo:

En el caso de la siguiente función, MyFunction3, tanto los parámetros como el valor de retorno son de tipo number.

let
    MyFunction3 = (parameter1 as number, parameter2 as number) as number => 
    let 
        value = parameter1 + parameter2, 
        result = value / 2 
    in 
       result 
in
   MyFunction3

Los parámetros de una función pueden ser opcionales. En estos casos es conveniente chequear si se ha pasado o no valor al parámetro porque podría generarse un error.

Ejemplos de sintaxis para crear una función con valores opcionales:

En este ejemplo el parámetro z es opcional. Si no se pasa valor a z le asignamos la cadena en blanco, si no hiciéramos esta comprobación el resultado mostraría un error.

let
    MyFunction4 = (y as number, optional z as text) as any => 
    let 
        result = Text.From(y) & (if z = null then "" else z)
    in 
       result 
in
   MyFunction4

Un ejemplo de crear funciones personalizadas en Power Query

Problema

Tenemos una consulta que contiene la lista de bebidas y sus precios

y otra con los rangos por precios de las bebidas.

Queremos conocer a qué rango de precios corresponde cada bebida, es decir, para cada fila de la primera consulta debemos buscar el valor correspondiente en la segunda consulta, pero no tenemos forma de combinar ambas consultas porque no tienen ningún elemento en común.

Para resolver este problema crearemos una función en el editor avanzado, que tendrá dos parámetros de entrada, uno con el nombre de la tabla con los rangos de precio y otro con el precio del cual se quiere conocer el rango al que pertenece.

Solución

1. En el menú Inicio, dentro del grupo Datos externos, desplegamos el menú Obtener datos y escogemos Consulta en blanco.
2. Se crea una nueva consulta en el Editor de Power Query. La consulta contiene con un solo paso, Origen
3. Abrimos el Editor avanzado y sustituimos el código existente por el siguiente:

let
    //le asigna a la variable Origen el resultado de evaluar una función que tiene dos parámetros tabla y col
    Origen = (tabla as table, col as number) as number =>
 
    //el cuerpo de la función
    let
        //le asigna a la variable values una lista con las filas de la consulta tabla  
        values = Table.ToRows(tabla),  
 
        //Devuelve la primera columna de la consulta tabla si se cumple que el valor de col está entre los valores de las columnas 1 y 2      
        Result = List.Last(List.Select(values, each _{1} <= col and _{2} > col)){0}
 
    //el valor que devuelve la función   
    in Result
   
//devuelve el valor de Origen
in
    Origen

4. Oprimimos el botón Listo. En el navegador aparece la función creada.
5. Seleccionamos la consulta Listado Gin.
6. En la cinta de opciones, elegimos la pestaña Agregar columna y dentro del grupo General, Invocar función personalizada.
7. Selecciona la función fx_rango, la consulta RangosPrecios y la columna Precio de la consulta ListadoGin.

8. Se crea una nueva columna y solo queda cambiar el tipo de dato a número entero.
9. Cargamos las dos tablas en el modelo y las relacionamos.

Podemos usar la columna Rango en las visualizaciones para filtrar

o agrupar los precios de las bebidas.

Conclusiones

El lenguaje M, además de la posibilidad de usar todas las funciones de su biblioteca estándar, nos permite crear funciones personalizadas en Power Query. Estas funciones se pueden definir dentro de una consulta o como una consulta separada y son una forma muy útil de compartir la lógica de negocio entre diferentes pasos en una consulta o entre múltiples consultas.

El archivo PIBX de este ejemplo está disponible en GitHub.