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, crear una función personalizada de Power Query que se pueda reutilizar tantas veces como necesite podría ser beneficiosa. 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.
Puede crear manualmente su propia función personalizada de Power Query mediante el lenguaje de fórmulas Power Query Mo 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.
En primer lugar, trataremos los pasos básicos para crear una función personalizada con código en la interfaz de usuario, luego nos centraremos en usar la interfaz para convertir acciones complejas en una función reutilizable.
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 vínculos a otros artículos de la documentación de Power Query para obtener más información sobre las transformaciones específicas a las que se hace referencia en este artículo.
Creación de una función personalizada a partir del código en la interfaz de usuario
Nota
Los pasos siguientes se pueden seguir en Power BI Desktop o con la experiencia de Power Query que se encuentra en Excel para Windows.
- Use la experiencia del conector para conectarse a los datos donde se hospeda. Cuando hayas seleccionado los datos, selecciona el botón Transformar Datos o el botón Editar. Esto le llevará a la experiencia de Power Query.
- Haga clic con el botón derecho en un lugar en blanco en el panel Consultas de la izquierda.
- Seleccione Consulta en blanco.
- En la nueva ventana de consulta en blanco, seleccione el menú Inicio y, a continuación, Editor avanzado.
- Reemplace la plantilla por la función personalizada. Por ejemplo:
let HelloWorld = () => ("Hello World") in HelloWorld - Seleccione Listo.
Para obtener más información sobre el desarrollo de funciones personalizadas con el lenguaje de fórmulas de Power Query M, consulte este artículo: Descripción de las funciones M de Power Query. En las secciones siguientes, hay tutoriales que describen cómo puede usar la interfaz de usuario de Power Query para desarrollar funciones personalizadas sin escribir código e instrucciones sobre cómo invocar la función personalizada en la consulta.
Creación de una función personalizada a partir de un tutorial de 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 ejemplo descargando los archivos de ejemplo usados en este artículo desde el siguiente vínculo de descarga . Para simplificar, en este artículo se usa el conector Folder. Para más información sobre el conector de carpetas, 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 de carpetas para ir a la carpeta donde se encuentran los archivos y seleccione Transformar datos o Editar. Estos pasos le llevan 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, la selección se realizó para el primer archivo de la lista, que resulta ser el archivo Abril 2019.csv.
Esta opción crea eficazmente una nueva consulta con un paso de navegación directamente a ese archivo como binario y el nombre de esta nueva consulta es la ruta de acceso de archivo del archivo seleccionado. Cambie el nombre de esta consulta para que sea archivo de ejemplo.
Crear un nuevo parámetro con el nombre parámetro de archivo y el tipo de Binario. Use la consulta Archivo de muestra como Valor predeterminado y Valor actual.
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 Parámetros cuadro de diálogoTipo cuando se 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 crea eficazmente una nueva función que se vincula con la consultaTransformar archivo de ejemplo. Los cambios que realice en la consulta Transformar archivo de ejemplo se replican 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, observe que se crea un nuevo grupo automáticamente con el nombre de la función. Este nuevo grupo contiene:
- Todos los parámetros a los que se hace referencia en la consultaTransformar 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 ahora está vinculada al archivo de transformación y a la función, así que los cambios realizados en esta consulta se reflejan en la función. Esta conexión 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 interpreta 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 archivo 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 5 y los datos comienzan de la fila 6 hacia abajo, como se muestra en la siguiente imagen.
El siguiente conjunto de pasos de transformación que se deben aplicar al archivo de ejemplo de transformación de son:
Quitar las cuatro primeras filas: esta acción se deshace 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.
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.
Después de promover los encabezados de columna, Power Query agrega automáticamente un nuevo Tipo modificado paso que detecta automáticamente los tipos de datos de cada columna. El archivo de Transformar archivo de ejemplo consulta es 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.
Cautela
La función de 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 de Transformar archivo, recibirá una advertencia que lee 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 (archivos CSV en este ejemplo). En 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 Nombre de columna nuevo. 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 muestra el parámetro de la función y puede seleccionar qué columna de la tabla se va a 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 crea una nueva columna con el nombre Tabla de salida. Esta columna tiene valores de 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 busca, puede expandir la columna de la tabla de salida seleccionando el icono de expansión. No use ningún prefijo para las columnas expandidas.
Puede verificar que tiene datos de todos los archivos de la carpeta comprobando los valores en 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 solo mes a partir 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 que se realiza manualmente.
Se recomienda leer también el artículo sobre Introducción a combinar 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 actualmente. El nuevo requisito requiere que antes de combinar los archivos, filtre los datos dentro de ellos para obtener solo las filas donde País es igual a Panamá.
Para que se produzca este requisito, cree un nuevo parámetro denominado Market con el tipo de datos de texto. Para el valor actual , introduzca el valor Panamá.
Con este nuevo parámetro, seleccione la consulta Transform Sample file y filtre el campo Country utilizando el valor del parámetro Market.
Nota
Para obtener más información sobre cómo filtrar columnas por valores, vaya a Valores de filtro.
Al aplicar este nuevo paso a la consulta, se actualiza automáticamente la función de archivo de transformación de, que ahora requiere dos parámetros basados en los dos parámetros que usa el archivo de ejemplo de transformación de .
Pero los Archivos CSV consulta tienen un signo de advertencia junto a él. Ahora que la función se actualiza, 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 Invocar función personalizada.
Para corregir los errores, haga doble clic en Invocar función personalizada en la Pasos aplicados para abrir la ventana Invocar función personalizada. En el parámetro Market , introduzca manualmente el valor Panamá.
Ahora puede volver a Tabla de salida expandida en los Pasos aplicados. Compruebe su consulta para validar que solo se muestren las filas donde País es igual a Panamá en el conjunto de resultados final de la consulta de los 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 que prefiera. 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:
| código |
|---|
| 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úa como ejemplo. En este caso, es el valor PTY-CM1090-LAX.
A partir de ese parámetro, se crea una nueva consulta en la que se aplican las transformaciones que necesita. En este caso, quiere dividir el código PTY-CM1090-LAX en varios componentes:
- Origen = PTY
- destino = LAX
- Aerolínea = CM
- FlightID = 1090
En el código M siguiente se muestra el 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 más información sobre el lenguaje de fórmulas M de Power Query, ir a Lenguaje de fórmulas M de Power Query.
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 alcanzó la salida deseada y aplicó la lógica para esta transformación desde una función personalizada.
