Cómo utilizar las funciones personalizadas de Power Query
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.
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:
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.
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.
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.
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.
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ú.
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,
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.
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:
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í:
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
Haga clic en Invocar
Debería devolver el valor correcto.
¡¡¡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
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.
Conclusión
En esta publicación, hemos visto cómo crear funciones personalizadas de Power Query básicas y basadas en transformaciones.
Manipular el código M es complejo y propenso a errores, especialmente cuando aún no hemos desarrollado la experiencia. Por lo tanto, como tantas otras características de Power Query, el mejor consejo es practicar, practicar, practicar.
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta