Las funciones personalizadas en Power Query funcionan de forma similar a Excel, pues hablamos de un dato de entrada que nos da un resultado de salida, pero aquí una función personalizada nos permite crear un cálculo por cada fila en una columa o columas, y crear una nueva columna a partir de esos resultados.
Si ya te has familiarizado con las funciones de Excel, entonces ya conoces el principio de que una función por lo regular nos pide un dato de entrada para darnos una salida. El dato de entrada, por ejemplo, puede ser un rango, un número o un pedazo de texto. El dato de salida puede variar: en casos como la función SUMA obtenemos un número, mientras que IZQUIERDA nos dará una secuencia.
Las funciones personalizadas en sí solo son un tipo de query que contiene pasos como cualquier otro query y pueden ser escritas para realizar tareas complicadas, como borrar datos de páginas web o recuperar datos de una base de datos. Puede ser una serie de pasos que queramos repetir para un conjunto dado como datos de entrada.
Para demostrarlo mejor veremos unos cuantos ejemplos simples.
FUNCIÓN PERSONALIZADA PARA CUADRAR UN NÚMERO
Digamos que tenemos una columna de números en una tabla y queremos calcular el cuadrado de esos números.
Daremos click dentro de la tabla y crearemos un nuevo query en la pestaña «Data» y luego «Desde una Tabla/Rango». Con esto se cargará la tabla dentro de Power Query:
Ahora crearemos un nuevo query en blanco. Para ello daremos click derecho en el área de Queries y nos iremos a «Nuevo Query», «Otras fuentes» y «Query en blanco».
Daremos click derecho en el nuevo query y después en el Editor Avanzado.
Para crear nuestra función necesitaremos especificar un parámetro de entrada y escribir los pasos para calcular la salida. Nuestra salida será especificada por la línea (TheNumber as number) =>, lo cual significa que este query tomará un valor de entrada que será referido como «TheNumber» y será el tipo de número.
(TheNumber as number) => let Source = TheNumber * TheNumber in Source
Para calcular el cuadrado de «TheNumber» solo lo multiplicaremos por sí mismo.
Daremos click en «Done» y veremos en pantalla que podemos introducir un parámetro de entrada para probar la función. Introduciremos cualquier número y daremos click en «Invoke».
Dado que no necesitamos este query de función invocada podemos darle click derecho y borrarla. Lo siguiente será dar click derecho a la función llamada Query 1 y la renombraremos «fxSquared».
Hay varias formas para poder hacer uso de una nueva función, ya sea dando click en la sección de «Añadir columna» de la pestaña podemos entonces usar «Columna Personalizada» o «Invocar Función Personalizada».
USANDO UNA FUNCIÓN PERSONALIZADA EN UNA COLUMNA PERSONALIZADA
Con el query de «Números» seleccionada, daremos click en «Añadir Columna» y después en «Añadir Columna Personalizada», lo que nos dará la ventana de diálogo de columna personalizada.
En el área de «Fórmula de Columna Personalizada» escribiremos «fx», lo que causará que Intellisense nos muestre nuestra función personalizada. Presionando TAB o dando click en el nombre de la función la introducirá dentro de la fórmula.
Ahora necesitamos suministrar a la función con un valor de entrada, que serán los números de la columna de números. Introduciremos «(» y entonces podremos dar doble click en «Número» en la lista de columnas disponibles. Si nuestra tabla tuviera más de una columna entonces nos mostraría un listado del que podríamos elegir.
Vamos a cerrar nuestro paréntesis «)» y daremos click a OK para crear nuestra columna personalizada.
Y nuestro resultado será que tendremos en pantalla una nueva columna con el cuadrado de los números.
Cerraremos y cargaremos el query en nuestra hoja de cálculo.
AÑADIR UNA COLUMNA INVOCANDO UNA FUNCIÓN PERSONALIZADA
Podemos lograr el mismo resultado si invocamos una función personalizada. Para ello nos vamos a la pestaña «Añadir Columna» y después «Invocar función personalizada»:
En el menú que nos aparecerá tendremos que darle un nombre a nuestra columna, que en este caso la vamos a llamar «Cuadrados» por la fórmula que estamos probando. Elegiremos el query de la función que como ya sabemos es «fxSquared». Elegiremos la columna de entrada por el parámetro «TheNumber»: Number.
Ya con esto listo vamos a darle click a OK y tendremos una nueva columna llamada «Cuadrados» que contendrá los cuadrados de los números en la columna de Números.
FUNCIÓN PERSONALIZADA PARA CONVERSIÓN DE MONEDA
Ahora pasamos a otro ejemplo. Aquí tengo una tabla con cantidades en dólares americanos que quiero convertir a pesos mexicanos.
Le daremos click a la tabla y crearemos un nuevo query desde la pestaña «Data» y luego «Desde Tabla/Rango»:
Como ya hicimos antes, para crear una función le daremos click derecho al área de Queries y seleccionaremos «Nuevo Query», «Otras Fuentes» y «Query en blanco». Otra vez click derecho en el nuevo query y click en «Editor avanzado».
La función toma un parámetro que se denominará como la «cantidad» y usa un valor llamado «tarifa», el cual tiene un valor de 21.59, lo cual corresponde a la tasa de conversión.
(Monto as number)=> let Rate = 21.59, Source = Monto * Rate in Source
Para añadir una columna invocando una función personalizada, vamos a seguir los mismos pasos que la vez anterior y terminaremos con una columna que contenga los valores en pesos méxicanos por cada cantidad en dólares americanos en cada fila.
Y aunque la función nos es útil, tiene la desventaja de que la tasa de cambio está programada de manera fija, lo cual no nos ayuda a la hora de tener tasas de cambio dinámicas. Si tenemos Office365 entonces podríamos obtener tasas de cambio actualizadas si usamos el tipo de datos STOCKS en Excel y leemos esos valores dentro de nuestro query.
Cambiaríamos la función así:
(Monto as number)=>
let
tarifa = STOCK[VALOR]{0},
Source = Monto * tarifa
in
SourceO también podríamos escribir un query/función que obtenga esas tasas desde un API, o también si usamos VBA o Power Query para que extraiga datos de un sitio web que provea tasas de cambio, pero eso es otro tema en el cual podríamos profundizar más delante.
FUNCIONES PERSONALIZADAS CON DOS PARÁMETROS
¿Pero que pasa si quiero poder escoger la moneda del tipo de cambio?
Digamos que tenemos una nueva tabla llamada «tipos de cambio» con las tasas de cambio a la fecha, que se vería algo así:
Tras darle click a la tabla «tipos de cambio» crearemos otro query desde una tabla/rango. De tratarse de un caso real probablemente tendríamos que modificar la función fxTipoCambio para este punto, pero para poder hacer una demostración vamos a duplicar esa función y la modificaremos. Para ello vamos a darle click derecho a «fxTipoCambio» y la vamos a duplicar. Esta copia la vamos a llamar «fxTiposCambio».
Vamos a dar click derecho en la nueva función y abriremos el editor avanzado.
Todo lo que necesitamos hacer aquí es cambiar el código para que la función tome dos parámetros de entrada, por lo que vamos a añadir «Tarifa as text» como el segundo parámetro. Recuerda separar con coma los dos parámetros.
(Monto as number, Tarifa as text)=>
let
Tarifa = Table.SelectColumns(Table.SelectRows(STOCK, each [MONEDA] = "US Dollar/Euro FX Cross Rate"),{"VALOR"}){0}[VALOR],
Source = Monto * Tarifa
in
SourceComo la tasa ahora está suministrada como un parámetro no hace falta declararlo en la función, por lo que borraremos toda la línea:
"Tasa= XX.XX"
y esto sería todo, por lo que guardaremos los cambios.
Ahora usaremos nuestra función dentro de una columna personalizada para ver como funcióna.
Daremos click en OK y tendremos una nueva columna con las varias cantidades convertidas a la moneda seleccionada con la tasa de conversión correcta.
En resúmen, aquí hemos hecho algunos ejemplos de funciones simples, pero estas pueden ser usadas para cosas más complicadas y útiles, las cuales podremos analizar más adelante en futuras entradas. Ejemplos de esto pueden ser funciones personalizadas que hagan totales acumulados o calculen el porcentaje de partes comparado a un entero, entre otras cosas.
Cualquier duda nos leemos abajo.