Cómo utilizar las funciones personalizadas de Power Query

Índice

¿Qué son las funciones personalizadas?
Ejemplo de función personalizada de Easy Power Query
Comprender la sintaxis
1. Múltiples argumentos y tipos.
Funciones personalizadas de Power Query que contienen transformaciones
Conclusión

En esta publicación, exploramos el tema de las funciones personalizadas de Power Query; ¿Qué son? ¿Cuándo debemos usarlos? ¿Y cómo crearlas? Al momento de escribir este artículo, más de 700 funciones M estándar ya están disponibles en Power Query. Entonces, ¿por qué necesitamos funciones personalizadas?

Las funciones personalizadas utilizan la biblioteca de funciones M existente y las combinan para lograr un resultado específico. No crean ninguna transformación nueva, sino que son una combinación de funciones existentes. Los usamos cuando necesitamos aplicar las mismas transformaciones en muchas consultas o en varias filas.

Pensé durante bastante tiempo en incluir funciones personalizadas en esta serie de introducción a Power Query , ya que pueden ser bastante complicadas, pero decidí que eran demasiado valiosas para excluirlas.

Esta publicación implica un poco de codificación y cambio del código M generado automáticamente, así que síguela con atención.

Tabla de contenido

¿Qué son las funciones personalizadas?
Ejemplo de función personalizada de Easy Power Query

Crear una consulta en blanco
Escribe el código M
Invocar la función
Utilice la función personalizada como una nueva columna

Comprender la sintaxis

Múltiples argumentos y tipos.

Funciones personalizadas de Power Query que contienen transformaciones

Ejemplo
Duplicar la consulta
Cambiar el nombre de la consulta
Transformaciones
Cambiar la consulta a una función.
Probando la función
Utilice la función personalizada

Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0117 Funciones personalizadas de Power Query.xlsx

Tener acceso

¿Qué son las funciones personalizadas?

Las funciones son procesos o procedimientos que realizan consistentemente una tarea. Por ejemplo, la función SUMA de Excel sumará valores constantemente, o BUSCARV devolverá constantemente un valor coincidente equivalente de una lista.

Sin embargo, el hecho de que las funciones en Excel normalmente calculen resultados numéricos y de texto no significa que tenga que ser así. En realidad, esta es una creencia bastante limitante. Desde la perspectiva de Power Query, es mejor pensar en las funciones como cualquier tarea rutinaria que se puede aplicar una y otra vez.

Vamos a abordar esta área trabajando con dos ejemplos:

Una función personalizada sencilla para demostrar la sintaxis y el formato.
Una función personalizada basada en transformación para demostrar un caso de uso de la vida real

Ejemplo de función personalizada de Easy Power Query

El archivo de ejemplo contiene solo una tabla. Para cargar los datos en Power Query, seleccione cualquier celda de esa tabla y haga clic en Datos de tabla/rango en la cinta.

Datos de tabla o rango

Asigne un nombre útil a la consulta: la he llamado Datos y así es como nos referiremos a esta consulta más adelante en esta publicación.

En este primer ejemplo, creamos una función personalizada simple. Su propósito es ilustrar el proceso para que no sea una curva de aprendizaje abrumadora cuando entremos en el siguiente ejemplo.

Crear una consulta en blanco

Cree una consulta en blanco haciendo clic en Inicio Nueva fuente Otras fuentes Consulta en blanco

Asigne un nombre útil a la consulta; con fines ilustrativos, lo he llamado fxMyFunction (no use ese nombre en el mundo real, seleccione algo más descriptivo, pero funcionará para nuestro ejemplo).

Escribe el código M

Abra el Editor avanzado para la consulta en blanco haciendo clic en Ver editor avanzado (o Editor avanzado de inicio ).

La ventana del Editor avanzado se ve así:

Aunque es una consulta en blanco, todavía se nos proporciona un código inicial.

Las declaraciones let e in son elementos clave en el código M; le indica a Power Query dónde comienzan y terminan las transformaciones.

Nuestro primer ejemplo tiene como objetivo crear una función personalizada que multiplique un valor por 10. Para ello, necesitamos una única entrada, a la que llamaremos valorinicial .

Ingrese el siguiente texto en el Editor avanzado. RECUERDE: El código M distingue entre mayúsculas y minúsculas, así que escriba con cuidado

(initialValue) =let    nameOfStep = initialValue * 10in    nameOfStep

Deconstruiremos este código en breve, pero por ahora, haga clic en Listo para cerrar el cuadro de diálogo Editor avanzado.

Invocar la función

En la ventana de Power Query, deberíamos ver lo siguiente:

Invocar una función personalizada

Aquí es donde podemos probar la función. Ingrese un número en el cuadro Valor inicial (yo he elegido 10) y haga clic en Invocar .

Si hemos hecho todo correctamente, debería calcular el valor y mostrárnoslo.

Resultado de la función invocada

Al invocar la función de esta manera, Power Query crea una consulta llamada Invocar función . No necesitamos esto; se puede eliminar.

Utilice la función personalizada como una nueva columna

En el panel de consultas, tenemos la consulta que creamos al principio (a la que llamamos Datos ) y la función que creamos (llamada fxMyFunction ).

Usemos la función personalizada dentro de nuestra consulta de datos. Abra la consulta de datos y cree una columna personalizada haciendo clic en Agregar columna Columna personalizada en la cinta.

El código a utilizar en la ventana Columna personalizada es:

=fxMyFunction([Value])

Nota: La palabra [Valor] en la función es el nombre de la columna que se utilizará como argumento.

Columna personalizada con la función personalizada

Usar una función personalizada es como usar una función estándar de Power Query .

Haga clic en Aceptar para confirmar la función. Se agrega una nueva columna a la consulta con el resultado de la función personalizada; cada resultado es la columna Valor multiplicada por 10.

Resultado de función personalizada en una columna personalizada

Comprender la sintaxis

Deconstruyamos el código que creamos anteriormente para comprender qué hace.

La primera línea declara los argumentos (o entradas) que se utilizarán en la función. En nuestro ejemplo, solo tiene una entrada llamada valorinicial .

(initialValue) =

La segunda línea identifica el inicio de los pasos de transformación; no es necesario cambiar nada en esta línea.

let

A continuación, creamos un paso llamado nameOfStep . Existen reglas para nombrar los pasos, especialmente con respecto al uso de espacios. Sin embargo, en lugar de entrar en eso ahora, es más fácil excluir siempre los espacios al crear pasos manualmente.

nameOfStep toma el valor inicial (el argumento declarado en la primera línea) y lo multiplica por 10.

nameOfStep = initialValue * 10

Finalmente, devolvemos el resultado de la función. in denota el final de las transformaciones. Después de la declaración in , proporcionamos el nombre del paso que devolverá la función. En nuestro caso, solo tenemos un paso, por lo que devolveremos nameOfStep .

in    nameOfStep

Múltiples argumentos y tipos.

Al utilizar funciones estándar de Power Query, notará que pueden tener múltiples argumentos y requerir tipos de datos específicos. Podemos hacer lo mismo en nuestras propias funciones personalizadas. Esto se maneja en la declaración del argumento al comienzo de la consulta.

Declarar el tipo de entrada

Para declarar un tipo de datos de texto, cambiamos el código de la siguiente manera:

(myText as text) =

En el código anterior, myText solo acepta entrada de texto. Para permitir solo una tabla como argumento, usaríamos lo siguiente:

(myTable as table) =

SUGERENCIA: Como sabe, existen muchos tipos de datos. Si no está seguro del código para un tipo de datos, utilice otra consulta, cambie el tipo de datos a través de la interfaz de usuario estándar y luego revise el código creado para identificar el código de el tipo de datos.

Declarar el tipo de salida

Power Query también tiene un tipo de datos para la salida de la función; esto se indica fuera de los corchetes.

(myText as text) as table =

El ejemplo anterior muestra que la función toma un valor de texto como argumento y el resultado devuelto es un tipo de datos de tabla.

Declarar múltiples argumentos

Para declarar múltiples argumentos para la función, los enumeramos entre paréntesis.

(myText as text, myTable as table) as number =

La declaración anterior tiene:

Dos argumentos llamados:
- myText – con un tipo de datos de texto
- myTable – con un tipo de datos de tabla
La salida se ha declarado como un tipo de datos numérico

Funciones personalizadas de Power Query que contienen transformaciones

Lo creas o no, ahora tenemos todo lo que necesitamos. Todo lo que necesitas es una pequeña cantidad de sintaxis y los pasos de transformación correctos.

Recuerde, el objetivo de una función personalizada es repetir los mismos pasos en múltiples escenarios. La forma más sencilla de crear funciones personalizadas es:

Aplicar los pasos de transformación para calcular un único resultado.
Edite las transformaciones para insertar parámetros donde el código será diferente cada vez que se ejecute.

Cuando sea más avanzado con Power Query, podrá escribir funciones personalizadas desde cero sin registrar transformaciones.

Ejemplo

Para este ejemplo, calcularemos las compras totales de un cliente.

La siguiente captura de pantalla contiene el resultado final. ABC tiene dos transacciones, la primera de $46 y la segunda de $76, pero en ambas se muestran $122 en la columna Total del cliente.

Columna Total del cliente

Duplicar la consulta

Duplica la consulta de datos que importamos al principio. Haga clic derecho en la consulta en la lista de consultas y haga clic en Duplicar en el menú.

Haga clic derecho - Consulta duplicada

Cambiar el nombre de la consulta

Como vimos arriba, el nombre que le damos a la consulta de función es el nombre de la función. Le he dado a la consulta el nombre fxFilterTotal. Colocar fx al principio es una práctica común para funciones personalizadas; ayuda a identificarlo visualmente como una función (otro prefijo común es fn ).

Transformaciones

Comencemos a hacer las transformaciones para calcular el resultado para un cliente.

Comience cambiando el nombre de las columnas con las que trabajaremos.

Cambie el nombre de las columnas de la siguiente manera:

El cliente cambia a filterColumn
Cambio de valor para returnColumn

Este es un paso útil ya que nos da control de los nombres de las columnas sin importar el escenario.

A continuación, filtre en filterColumn para seleccionar un solo cliente,

Filtrar a valor único

Para la transformación final, seleccione la columna de retorno y haga clic en Transformar suma de estadísticas en la cinta.

Esto calculará el valor total para el cliente seleccionado.

Resultado final después de las transformaciones.

Hay otras formas de realizar este cálculo, pero hagámoslo simple.

Cambiar la consulta a una función.

Abra el editor avanzado; el código será el siguiente:

Editor avanzado con el código grabado inicial

Aquí viene la parte difícil... hacer los cambios correctos en el código M. ¡¡¡Eeek!!!

Primero, debemos declarar los argumentos en la primera línea del código. Necesitamos cuatro argumentos:

La mesa a utilizar
El nombre de la columna a filtrar.
El valor por el que filtrar
El nombre de la columna de la que devolver el resultado.

Por lo tanto, la declaración en la parte superior de la consulta es:

(tableName as table, filterColumn as text, filterText as text, returnColumn as text) =

Ahora insertemos los argumentos en nuestro código grabado:

Cambia esto:

Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],

A esto:

Source = tableName,

En la siguiente línea, cambiamos el nombre de las columnas Cliente y Valor por los nombres de los parámetros.

Este:

#"Renamed Columns" = Table.RenameColumns(Source,{{"Customer", "filterColumn"}, {"Value", "returnColumn"}}),

Se convierte en:

#"Renamed Columns" = Table.RenameColumns(Source,{{filterColumn, "filterColumn"}, {returnColumn, "returnColumn"}}),

El último parámetro a aplicar es el valor por el que filtrar.

Este:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = "ABC Discounts")),

se convierte

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = filterText)),

Esos son todos los cambios. Mira, no estuvo tan mal, ¿verdad?

Notará que la función ya no contiene ninguna columna de la consulta de datos con la que estábamos trabajando. Esto significa que la función podría usarse en cualquier consulta.

SUGERENCIA: Podemos copiar y pegar consultas fácilmente entre libros si queremos reutilizarlas.

El código M completo debería ser así:

Código de funciones personalizadas de Final Power Query

Haga clic en Listo para cerrar la ventana del Editor avanzado.

Si tiene algún error tipográfico, aquí está el texto final que puede copiar/pegar.

(tableName as table, filterColumn as text, filterText as text, returnColumn as text) =let    Source = tableName,    #"Renamed Columns" = Table.RenameColumns(Source,{{filterColumn, "filterColumn"}, {returnColumn, "returnColumn"}}),    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = filterText)),    #"Calculated Sum" = List.Sum(#"Filtered Rows"[returnColumn])in    #"Calculated Sum"

Probando la función

Invoquemos la función para ver si funciona.

nombre de tabla: Datos
filterColumn: Clientes
filterText: Descuentos ABC
columna de retorno: Valor

Invocar la función personalizada

Haga clic en Invocar

Debería devolver el valor correcto.

El resultado de la función invocada.

¡¡¡Cosas increíbles!!!

Utilice la función personalizada

Todo lo que nos queda ahora es usar la función personalizada. Abra la consulta de datos y agregue una columna personalizada. Ingrese lo siguiente como fórmula:

=fxFilterTotal(Source,"Customer",[Customer],"Value")

Fuente – es el nombre del paso anterior.
“Cliente” : el nombre de la columna a filtrar
[Cliente] : el valor por el que filtrar (es decir, el valor entrante de la columna Cliente)
“Valor” : la columna para calcular

La función personalizada utiliza una función estándar

Haga clic en Aceptar para aceptar la función.

La ventana Vista previa ahora tiene este aspecto y muestra el valor total de ventas de cada cliente. Por ejemplo, observe los descuentos ABC en las filas 1 y 6; ambos muestran 122 en la columna Total del cliente.