POWERQUERY: FUNCIONES PERSONALIZADAS (Caso Práctico)

POWERQUERY: FUNCIONES PERSONALIZADAS (Caso Práctico)

Conocer cómo crear tus propias funciones te permite conseguir transformaciones imposibles utilizando la UI de PowerQuery. Adéntrate en una de las partes más interesantes del código M!

Crear funciones personalizadas en Power Query suele intimidar porque no es una opción precargada en la interfaz: hay que escribirla a mano en el editor avanzado. En esta sesión partimos de lo más sencillo (una función que calcula el trimestre) y vamos subiendo la dificultad hasta resolver un caso real que surgió en el grupo: consolidar varios ficheros cuya información viene partida en dobles cabeceras dentro de cada registro. La idea es trabajar paso a paso, como quien levanta una pared ladrillo a ladrillo, para no perderse en el camino.

La sintaxis de una función personalizada

Toda función en Power Query empieza con una estructura let ... in, muy parecida a la función LET de Excel. En let vamos generando las variables que necesitamos y en in elegimos qué devuelve la función.

Para convertir una consulta en función, se empieza declarando los parámetros entre paréntesis, seguidos de =>:

  1. Abrir un nuevo origen: Otros orígenes → Consulta en blanco.
  2. Entrar al editor avanzado.
  3. Escribir los parámetros, por ejemplo (mes as number) =>.
  4. Dentro de let, calcular el resultado en una variable (por convención, output).
  5. En in, devolver esa variable.

Indicar el tipo de dato del parámetro (as number, as table) no es obligatorio, pero ayuda a prevenir errores.

Primeras funciones: trimestre e IVA

La función trimestre toma el número de mes y lo convierte en su trimestre. La fórmula es redondear hacia arriba el mes dividido entre tres con Number.RoundUp(mes / 3). Así, los meses 1, 2 y 3 devuelven el primer trimestre, el 4 ya devuelve el segundo, etc. Una vez creada, se puede invocar desde una columna personalizada con función trimestre pasándole el campo periodo, en lugar de repetir la fórmula completa cada vez.

La función tax calcula el impuesto sobre un importe: Number.Round(importe * 0.21, 2), multiplicando por el 21 % y redondeando a dos decimales. Igual que antes, se invoca desde una columna personalizada pasándole los euros.

La ventaja es clara: tener una serie de cálculos ya hechos de forma fácil y reutilizable, igual que haríamos con una función LAMBDA en Excel.

Funciones que imitan a las dinámicas de Excel

Las funciones de Power Query no solo manejan datos sueltos: también trabajan con tablas, columnas y listas. Aprovechando esto, recreamos varias funciones dinámicas de Excel:

  • tomar: Excel tiene TOMAR, que con un número positivo coge las primeras filas y con uno negativo las últimas. Power Query no tiene una única función equivalente, sino dos separadas (Table.FirstN y Table.LastN). La solución es una función con dos parámetros (tabla y filas) y un condicional: if filas > 0 then Table.FirstN(tabla, filas) else Table.LastN(tabla, filas * -1). El signo negativo se invierte para pasar siempre un número positivo a Table.LastN.
  • listar: convierte una tabla en una lista de listas, donde cada columna pasa a ser una lista independiente. Usa la función nativa Table.ToColumns(tabla).
  • apilarV: simula la función APILARV de Excel, poniendo una lista debajo de otra. Usa List.Combine({lista1, lista2}); las listas en Power Query se escriben entre llaves {}.
  • controlT: a partir de una lista de columnas genera una tabla, el equivalente al Ctrl+T de Excel. Usa Table.FromColumns(columnas).

Acotar funciones nativas que tienen muchos parámetros a nuestro uso habitual (uno o dos argumentos) nos permite trabajar de forma más ágil y construir nuestra propia librería.

El caso práctico: consolidar ficheros con doble cabecera

El reto real eran varios ficheros con una estructura incómoda: cada registro venía partido en dos pares de filas (nombre de campo / valor). El objetivo: consolidarlos en una tabla plana. Combinando las funciones anteriores, el tratamiento de cada fichero queda así:

  1. tomar(datos, 2) para quedarse con las dos primeras filas y tomar(datos, -2) para las dos siguientes, generando dos bloques.
  2. listar sobre cada bloque para convertirlos en listas de columnas.
  3. apilarV para unir ambas listas en una sola.
  4. controlT (Table.FromColumns) para reconstruir una tabla con todos los pares alineados.
  5. Table.PromoteHeaders para usar la primera fila como encabezado.
  6. Anular dinamización de otras columnas (unpivot), dejando fija la referencia (el número de albarán) para que se repita en cada fila.

Un truco para depurar: el registro de pasos

En lugar de encadenar variables secuenciales, se puede envolver todo el bloque let en un registro usando corchetes [ ... ], separando cada paso con comas. Al devolver ese registro completo, Power Query muestra todos los pasos a la vez y se puede inspeccionar el resultado de cada uno por separado. Es muy útil mientras se desarrolla y para localizar dónde está fallando algo. Cuando la función ya está lista, basta con devolver solo el último paso (output[tabla_formateada]).

De consulta a función reutilizable

Todo ese tratamiento se encapsula en una función formateo(archivo as binary) que recibe el binario de cada fichero, lo interpreta como un libro de Excel (Excel.Workbook) y aplica todos los pasos. Después, al obtener datos de una carpeta, en lugar de consolidar directamente se añade una columna personalizada que invoca formateo sobre la columna de binarios. Cada fichero queda transformado y ya se puede expandir y consolidar en una única tabla. Si llega un fichero nuevo a la carpeta, basta con actualizar para que se incorpore automáticamente.

Conclusión

Crear funciones personalizadas en Power Query es más sencillo de lo que parece: paréntesis, parámetros, => y a trabajar los datos. Desde cálculos básicos como el trimestre o el IVA hasta recrear funciones dinámicas de Excel y combinarlas para resolver una consolidación compleja, la clave está en avanzar paso a paso. Construir tu propia librería de funciones reduce el coste de mantenimiento, te da confianza en cada paso y te permite resolver casos que las opciones estándar de Power Query no cubren de forma directa.

Más contenido de Excel en InflueXcel