Uso de funciones personalizadas

Si se encuentra en una situación en la que necesita aplicar el mismo conjunto de transformaciones a consultas o valores diferentes, podría ser beneficioso crear una función personalizada de Power Query que se pueda reutilizar tantas veces como necesite. Una función personalizada de Power Query es una asignación de un conjunto de valores de entrada a un único valor de salida y se crea a partir de funciones y operadores nativos de M.

Aunque puede crear manualmente su propia función personalizada de Power Query mediante código como se muestra en Descripción de las funciones de Power Query M, la interfaz de usuario de Power Query le ofrece características para acelerar, simplificar y mejorar el proceso de creación y administración de una función personalizada.

Este artículo se centra en esta experiencia, que solo se proporciona a través de la interfaz de usuario de Power Query, y en cómo sacar el máximo partido de ella.

Importante

En este artículo se describe cómo crear una función personalizada con Power Query mediante transformaciones comunes accesibles en la interfaz de usuario de Power Query. Se centra en los conceptos básicos para crear funciones personalizadas y enlaza con artículos adicionales de la documentación de Power Query para obtener más información sobre transformaciones específicas a las que se hace referencia en el artículo.

Creación de una función personalizada a partir de una referencia de tabla

Nota:

El ejemplo siguiente se creó con la experiencia de escritorio que se encuentra en Power BI Desktop y también se puede seguir mediante la experiencia de Power Query que se encuentra en Excel para Windows.

Puede seguir este modelo y descargar los archivos de ejemplo que se han usado en el artículo desde el siguiente vínculo de descarga. Para simplificar, este artículo usará el conector Carpeta. Para obtener más información sobre el conector Carpeta, vaya a Carpeta. El objetivo de este ejemplo es crear una función personalizada que se pueda aplicar a todos los archivos de esa carpeta antes de combinar todos los datos de todos los archivos en una sola tabla.

Empiece por usar la experiencia del conector Carpeta para ir a la carpeta donde se encuentran los archivos y seleccione Transformar datos o Editar. Esto le llevará a la experiencia de Power Query. Haga clic con el botón derecho en el valor Binario de su elección en el campo Contenido y seleccione la opción Agregar como nueva consulta. En este ejemplo, verá que la selección se realizó para el primer archivo de la lista, que resulta ser el archivo April 2019.csv.

Esta opción creará efectivamente una nueva consulta con un paso de navegación directamente a ese archivo como Binario, y el nombre de esta nueva consulta será la ruta de acceso del archivo seleccionado. Cambie el nombre de esta consulta por Archivo de ejemplo.

Cree un nuevo parámetro con el nombre Parámetro de archivo. Use la consulta Archivo de ejemplo como el Valor actual, como se muestra en la siguiente imagen.

Nota:

Se recomienda leer el artículo sobre Parámetros para comprender mejor cómo crear y administrar parámetros en Power Query.

Las funciones personalizadas se pueden crear mediante cualquier tipo de parámetros. No es necesario que ninguna función personalizada tenga un binario como parámetro.

El tipo de parámetro binario solo se muestra dentro del menú desplegable Tipo del cuadro de diálogo Parámetros cuando tiene una consulta que se evalúa como un binario.

Es posible crear una función personalizada sin un parámetro. Esto se suele ver en escenarios en los que se puede deducir una entrada desde el entorno en el que se invoca la función. Por ejemplo, una función que toma la fecha y hora actuales del entorno y crea una cadena de texto específica a partir de esos valores.

Haga clic con el botón derecho en Parámetro de archivo en el panel Consultas. Seleccione la opción Referencia.

Cambie el nombre de la consulta recién creada de Parámetro de archivo (2) a Transformar archivo de ejemplo.

Haga clic con el botón derecho en esta nueva consulta Transformar archivo de ejemplo y seleccione la opción Crear función.

Esta operación creará eficazmente una nueva función que se vinculará con la consulta transformar archivo de ejemplo. Los cambios que realice en la consulta Transformar archivo de ejemplo se replicarán automáticamente en la función personalizada. Durante la creación de esta nueva función, use Transformar archivo como Nombre de la función.

Después de crear la función, observará que se creará un nuevo grupo automáticamente con el nombre de la función. Este nuevo grupo contendrá:

• Todos los parámetros a los que se hace referencia en la consulta Transformar archivo de ejemplo.
• La consulta Transformar archivo de ejemplo, conocida normalmente como consulta de ejemplo.
• La función recién creada, en este caso Transformar archivo.

Aplicación de transformaciones a una consulta de ejemplo

Con la nueva función creada, seleccione la consulta con el nombre Transformar archivo de ejemplo. Esta consulta está vinculada ahora a la función Transformar archivo, por lo que los cambios realizados en esta consulta se reflejarán en la función. Esto es lo que se conoce como el concepto de una consulta de ejemplo vinculada a una función.

La primera transformación que debe producirse en esta consulta es una que interpretará el binario. Puede hacer clic con el botón derecho en el archivo binario en el panel de vista previa y seleccionar la opción CSV para interpretar el binario como un archivo CSV.

El formato de todos los archivos CSV de la carpeta es el mismo. Todos tienen un encabezado que abarca las cuatro primeras filas. Los encabezados de columna se encuentran en la fila cinco y los datos comienzan de la fila seis hacia abajo, como se muestra en la siguiente imagen.

El siguiente conjunto de pasos de transformación que se deben aplicar a Transformar archivo de ejemplo son:

1. Quitar las cuatro primeras filas: esta acción se deshará de las filas que se consideran parte de la sección de encabezado del archivo.

   

   Nota:

   Para obtener más información sobre cómo quitar filas o filtrar una tabla por posición de fila, vaya a Filtrar por posición de fila.

2. Promover encabezados: los encabezados de la tabla final se encuentran ahora en la primera fila de la tabla. Puede promocionarlos como se muestra en la siguiente imagen.

   

De forma predeterminada, Power Query agregará automáticamente un nuevo paso Tipo modificado después de promover los encabezados de columna que detectarán automáticamente los tipos de datos de cada columna. La consulta Transformar archivo de ejemplo tendrá un aspecto similar a la siguiente imagen.

Nota:

Para obtener más información sobre cómo promover y degradar encabezados, vaya a Promover o degradar encabezados de columna.

Precaución

La función Transformar archivo se basa en los pasos realizados en la consulta Transformar archivo de ejemplo. Sin embargo, si intenta modificar manualmente el código de la función Transformar archivo, recibirá una advertencia con el texto: The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Invocación de una función personalizada como una nueva columna

Con la función personalizada creada y todos los pasos de transformación incorporados, puede volver a la consulta original donde tiene la lista de archivos de la carpeta. Dentro de la pestaña Agregar columna de la cinta de opciones, seleccione Invocar función personalizada en el grupo General. Dentro de la ventana Invocar función personalizada, escriba Tabla de salida como Nuevo nombre de columna. Seleccione el nombre de la función, Transformar archivo, en la lista desplegable Consulta de función. Después de seleccionar la función en el menú desplegable, se mostrará el parámetro de la función y podrá seleccionar qué columna de la tabla se usará como argumento para esta función. Seleccione la columna Contenido como valor o argumento que se va a pasar para el Parámetro de archivo.

Después de seleccionar Aceptar, se creará una nueva columna con el nombre Tabla de salida. Esta columna tiene valores Tabla en sus celdas, como se muestra en la siguiente imagen. Para simplificar, quite todas las columnas de esta tabla, excepto Nombre y Tabla de salida.

Nota:

Para obtener más información sobre cómo elegir o quitar columnas de una tabla, vaya a Elegir o quitar columnas.

La función se aplicó a cada fila de la tabla utilizando los valores de la columna Contenido como argumento de la función. Ahora que los datos se han transformado en la forma que estaba buscando, puede expandir la columna Tabla de salida, como se muestra en la imagen siguiente, sin usar ningún prefijo para las columnas expandidas.

Puede comprobar que tiene datos de todos los archivos de la carpeta comprobando los valores de la columna Nombre o Fecha. En este caso, puede comprobar los valores de la columna Fecha, ya que cada archivo solo contiene datos de un mes de un año determinado. Si ve más de uno, significa que ha combinado correctamente datos de varios archivos en una sola tabla.

Nota:

Lo que ha leído hasta ahora es fundamentalmente el mismo proceso que sucede durante la experiencia Combinar archivos, pero realizado manualmente.

Se recomienda leer también los artículos Información general de la combinación de archivos y Combinar archivos CSV para comprender mejor cómo funciona la experiencia de combinación de archivos en Power Query y el rol que desempeñan las funciones personalizadas.

Adición de un nuevo parámetro a una función personalizada existente

Imagine que hay un nuevo requisito sobre lo que ha creado. El nuevo requisito requiere que antes de combinar los archivos, filtre los datos dentro de ellos para obtener solo las filas donde el País es igual a Panamá.

Para que se produzca este requisito, cree un nuevo parámetro denominado Mercado con el tipo de datos texto. En Valor actual, escriba el valor Panamá.

Con este nuevo parámetro, seleccione la consulta Transformar archivo de ejemplo y filtre el campo País mediante el valor del parámetro Mercado.

Nota:

Para obtener más información sobre cómo filtrar columnas por valores, vaya a Filtrar valores.

Al aplicar este nuevo paso a la consulta, se actualizará automáticamente la función Transformar archivo, que ahora requerirá dos parámetros basados en los dos parámetros que usa Transformar archivo de ejemplo.

Pero la consulta Archivos CSV tiene un signo de advertencia junto a ella. Ahora que la función se ha actualizado, requiere dos parámetros. Por lo tanto, el paso en el que se invoca la función da como resultado valores de error, ya que solo se pasó uno de los argumentos a la función Transformar archivo durante el paso Función personalizada invocada.

Para corregir los errores, haga doble clic en Función personalizada invocada en los Pasos aplicados para abrir la ventana Invocar función personalizada. En el parámetro Mercado, escriba manualmente el valor Panamá.

Ahora puede comprobar la consulta para validar que solo las filas en las que País es igual a Panamá se muestran en el conjunto de resultados final de la consulta Archivos CSV.

Creación de una función personalizada a partir de una parte reutilizable de la lógica

Si tiene varias consultas o valores que requieren el mismo conjunto de transformaciones, puede crear una función personalizada que actúe como una parte reutilizable de la lógica. Más adelante, esta función personalizada se puede invocar en las consultas o valores de su elección. Esta función personalizada puede ahorrar tiempo y ayudarle a administrar el conjunto de transformaciones en una ubicación central, que puede modificar en cualquier momento.

Por ejemplo, imagine una consulta que tiene varios códigos como una cadena de texto y desea crear una función que descodifique esos valores, como en la tabla de ejemplo siguiente:

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Para empezar, debe tener un parámetro que tenga un valor que actúe como ejemplo. En este caso, será el valor PTY-CM1090-LAX.

A partir de ese parámetro, crea una nueva consulta donde aplica las transformaciones que necesita. Para este caso, debe dividir el código PTY-CM1090-LAX en múltiples componentes:

• Origen = PTY
• Destino = LAX
• Aerolínea = CM
• Identificación de vuelo = 1090

A continuación se muestra el código M para ese conjunto de transformaciones.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Nota:

Para obtener más información sobre el lenguaje de fórmulas de Power Query M, vaya a Lenguaje de fórmulas de Power Query M.

Después, puede transformar esa consulta en una función haciendo clic con el botón derecho en la consulta y seleccionando Crear función. Por último, puede invocar la función personalizada en cualquiera de las consultas o valores, como se muestra en la siguiente imagen.

Después de algunas transformaciones más, puede ver que ha alcanzado el resultado deseado y ha aprovechado la lógica para dicha transformación desde una función personalizada.