Parámetros y Funciones en Power BI / Power Query – Funciones Personalizadas

Power Query tiene más de 600 funciones nativas y el equipo de Power Query sigue agregando más y más.

No recomendaría memorizarlas, pero necesitas comprender el concepto de parámetros y argumentos para comprender qué son las funciones.

En esta entrada del blog cubriré el tema de Funciones Personalizadas y cómo puedes crearlas. Recuerda que esta es una entrada más en la serie, por lo que te recomiendo ver la primera parte aquí.

Funciones M

Puedes obtener una lista de todas las variables de ambiente en tu Power Query / Power BI al usar un código llamado #shared (HASHTAG shared) en tu barra de fórmulas como se muestra a continuación:

Sin embargo, aún si la herramienta tiene más de 600 funciones, puede que tengamos que crear nuestra propia función personalizada para abordar tareas repetitivas.

En lugar de escribir el mismo código una y otra vez, enfrascarlo en una función personalizada, o definida por el usuario, para mayores beneficios y ahorrar tiempo puede ser una gran ventaja.

Verás, el concepto detrás de una función personalizada o definida por el usuario es que estás “empaquetando” una solución. Es una tarea repetitiva que tiene que ser aplicada a una o varias consultas o argumentos.

Es el mismo principio que obtienes en DAX cuando creas medidas portátiles para no tener que definir un SUM(Sales[Sales]) cada vez que usas un CALCULATE. Simplemente haces una referencia a esa medida que podrías llamar [Sales Amount].

El mismo principio que tienes en Excel al crear un UDF con VBA – una tarea repetitiva que puede ser simplificada con una simple función.

La diferencia es que puedes obtener mucho más que un único valor de una función dentro de Power Query. Puedes obtener una tabla, una lista, un registro, valores o una combinación de cualquiera o todos los anteriormente mencionados.

El escenario: Archivos con el mismo formato, pero con valores diferentes

Imagina que cada mes exportamos un archivo de nuestro sistema que tiene los datos del mes pasado.

Todos los archivos tendrán la misma estructura y formato, pero los valores dentro de los archivos son diferentes porque cada mes difiere del anterior.

Descargué los archivos de enero, febrero y marzo:

y lo que me gustaría hacer es crear una función que pueda aplicar luego a cualquiera de estos archivos en cualquier momento. Quizás en cualquier momento pueda hacer que mi sistema exporte el año completo en lugar de únicamente los datos mensuales.

Con esto, sabemos que necesitamos crear una función con un único parámetro. Ese parámetro debe ser el archivo mismo que queremos transformar.

Puedes seguir el procedimiento mientras lees esta entrada descargando los archivos de muestra en el siguiente botón:

Funciones personalizadas en Power Query / Power BI

Hay 2 formas de crear una función M:

• La forma manual –  donde debes entrar manualmente al editor avanzado para transformar la consulta en una función
• La forma asistida –  donde Power Query te provee con la estructura para crear funciones en lugar de tener que lidiar con el código M.

En este artículo abordaremos ambas formas y luego veremos cuál provee una mejor experiencia en general.

Antes de sumergirnos en este caso, definamos el alcance de lo que nuestra función hará y el valor que agregaremos a nuestra solución.

Uno de los archivos que descargaste es de Excel y dentro del mismo tendrás una consulta que se conecta al archivo 01-January.csv.

Importante: puedes importar esa consulta al Power BI Desktop si vas a Importar desde el menú Archivo en Power BI Desktop.

Lo primero que debes hacer es editar esa consulta y cambiar la ruta del archivo a la de tu propia computadora. Simplemente haz clic en el ícono de engranaje a la par del paso Fuente y establece la ruta correcta de tu archivo local.

Al terminar con eso, tu consulta debería cargar apropiadamente:

En resumen, estos son los pasos que hemos hecho para esta consulta:

• Source (Origen) – La forma en la que conectamos al archivo csv
• Promoted Headers (Encabezados promovidos) – promovimos la primera fila para que sea el encabezado de las columnas
• Replaced Value (Valor reemplazado) – reemplazamos los valores en blanco con valores null en la columna Month
• Filled Down (Rellenar hacia abajo) – usamos una operación de vaciado en la columna Month
• Unpivoted Other Columns (Anulación de dinamización de otras columnas) –quitamos la dinamización a todas las columnas excepto a Month y Product Name
• Renamed Columns (cambiar nombre) –a la columna Attribute se le cambió el nombre a Market, y a la columna Value le pusimos Amount.
• Changed Type (Cambiar tipo de datos) – configuramos los tipos de datos de cada columna

Ahora que tenemos la consulta que necesitamos, debemos transformar esa consulta en una función y asignar un parámetro a la misma.

Crear la función de forma manual

Para mantenerlo todo bien organizado, dupliquemos la consulta y la renombramos ManualFunction. Haz clic derecho en la consulta y te dará la opción para duplicarla:

Al terminar, debemos abrir el Editor avanzado en esa nueva consulta:

Primero vamos al menú View o Vista (1), hacemos clic en el botón Editor avanzado o Advanced Editor (2) y luego veremos la ventana del Editor avanzado (3).

He resaltado la parte del código que necesitamos convertir en un parámetro, que es básicamente el binario del archivo:

Y luego tenemos que cambiar el código manualmente de esta forma:

Luego de hacer clic en Hecho, se desplegará con un ícono de función en el panel de consultas ya que ahora es una función personalizada con 1 parámetro (un binario) que da como resultado una tabla (como una tabla).

Nota la diferencia entre las 2 imágenes. Al principio a la izquierda agregué componentes de función:

(FileBinary as binary) as table =>

Y todo lo demás permaneció igual en esa parte que resalté en la primera imagen, que ahora es un parámetro de función.

Probar la función manual

Tenemos nuestra función y ahora es tiempo de ponerla a prueba. La forma en la que hacemos esto es conectándola a la carpeta que descargamos – la que tiene todos los archivos de muestra.

Puedes hacer eso dentro del Power Query Editor:

Y luego haces clic en Editar dentro de la ventana que aparecerá para colocarla como una nueva consulta en Power Query Editor.

El siguiente paso es invocar tu función en todos los archivos dentro de la carpeta:

Ve al menú Agregar columna o Add Column (1), haz clic en el botón Invocar función personalizada o Invoke Custom Function (2) y luego llena la ventana como se muestra en la imagen (3).

Vas a notar que algunas celdas marcan errores y la razón es porque tenemos algunos archivos que no están en csv en nuestra carpeta.

Entonces podemos filtrarlos y dejarlos fuera antes de invocar la función personalizada, filtrando la extensión para que únicamente tenga archivos .csv o agregamos otro filtro donde nos deshacemos de esos errores.

Recomiendo usar la primera opción de filtrado para dejarlos fuera antes de invocar la función.

Después de deshacerme de algunas columnas innecesarias, esto es lo que obtengo como resultado:

Y el siguiente paso es hacer clic en el ícono que tiene las flechas dobles opuestas (también llamado Expandir) para obtener toda la información en una única tabla, esencialmente adjuntándolas:

Y luego para verificar que todo está en nuestra tabla, puedes hacer lo siguiente:

La próxima semana te mostraré cómo hacer una función con el nuevo y optimizado método que provee Power Query.