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 hasta un único valor de salida, y se crea a partir de M funciones y operadores nativos.
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 facilitar las cosas, este artículo utiliza el conector de carpetas. 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. Estos pasos le llevarán 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 April 2019.csv.
Esta opción crea efectivamente 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 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 y el tipo Binario. Use la consulta Archivo de ejemplo 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 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 crea eficazmente una nueva función que se vincula con la consulta Transformar 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, observará que se creará 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 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. 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 interprete 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 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 a Transformar archivo de ejemplo son:
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.
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 de forma predeterminada un nuevo paso Tipo modificado que detecta automáticamente los tipos de datos de cada columna. La consulta Transformar archivo de ejemplo tendrá un aspecto similar a la siguiente imagen.
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 (Archivos CSV en este ejemplo). 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 muestra el parámetro de la función y puede 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 crea 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 transforman en la forma que está buscando, puede expandir la columna Tabla de salida seleccionando el icono Expandir. No use 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.
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 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 actualiza automáticamente la función Transformar archivo, que ahora requiere 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 está actualizada, 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 volver a la Tabla de salida expandida en los pasos aplicados. Compruebe 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
Se empieza por tener un parámetro que tenga un valor que sirva de ejemplo. En este caso, es 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
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"
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 aplicado la lógica para dicha transformación desde una función personalizada.
¿Quiere usar fórmulas de una forma más avanzada? Con esta ruta de aprendizaje, aprenderá a usar fórmulas avanzadas y a realizar actualizaciones personalizadas.
Como consultor operativo, lleva a cabo la detección, los requisitos de captura, involucra a expertos y partes interesadas en la materia, y configura soluciones empresariales mediante herramientas y componentes de Power Platform.