Lo Que Calla List.TransformMany en Lenguaje M, el Poder de las Iteraciones Anidadas en Power Query.
Una función bastante poderosa en el Lenguaje M de Power Query es: List.TransformMany, sin embargo, su documentación en Microsoft es demasiado críptica y bastante enrevesada de entender para un no programador, veamos:
— ¿Así o más enredado?
Y para colmo de males la documentación en Microsoft no brinda ejemplos, por ello, hoy te revelaré parte sus secretos para que le saques el máximo provecho posible.
A continuación puedes leer todo el contenido con ilustraciones o ver la serie de vídeos que se dividen en 4 episodios, primero episodio: aquí , segundo aquí (en elaboración ) y tercero aquí (en elaboración) .Este artículo condensa lo tratado en los 3 primeros episodios, el último episodio que compete a un análisis de rendimiento se hará por separado.
— Otra nota:
En esta publicación asumo que tienes por lo menos un nivel intermedio en el Lenguaje M, por lo tanto, conoces: sus funciones, la synax sugar each y sobre funciones de iteración, no obstante, si no tienes conocimiento en el lenguaje puedes hacer un curso de sus fundamentos aquí y aquí.
Entendiendo List.TransformMany
Resulta que describir la función List.TransformMany es complicado, por lo que no la definiremos de entrada, si no que pasaremos directamente a un ejemplo y desglosaremos sus argumentos viendo los detalles para comprenderla, luego de ello desgranaremos la definición y la complementaremos con la de Microsoft.
— Ejemplo:
Lo primero a destacar es que se han proporcionado tres argumentos, estos son:
En el primer parámetro se indica cualquier lista de nuestra necesidad, para el ejemplo el argumento: { 1 .. 4 }.
La función List.TransformMany al ser un iterador recorre cada ítem en esta lista del primer argumento, pasando por 1, luego 2, luego 3 y finalmente 4 (una iteración).
En cada iteración ejecuta el segundo argumento: ( a ) => { a*4, a }, donde el valor que se asigna al argumento de entrada: a, de la expresión lambda, es el ítem en la iteración actual.
Tomando como caso particular la primera iteración externa, tendríamos:
{ 1, 2, 3, 4 } // Primer argumento, primera iteración, valor: 1.
( 1 ) => { 1*4, 1 } // Segundo argumento, recibe 1 en: a y se evalúa el cuerpo de la función.
Por lo que el resultado de este segundo argumento sería: {4, 1}
A continuación, (y recordando que estamos en la primera iteración externa), se ejecuta el tercer argumento: ( a, b ) => b – a, el cual es otra expresión lambda que itera en el resultado: {4, 1}, transformado los valores como si fuera List.Transform, donde a, es el valor en la iteración más externa: 1, mientras que b es el valor actual en la iteración interna, suponiendo que estamos en la primera iteración interna el valor sería: 4.
Emulando el proceso tendríamos:
{ 1, 2, 3, 4 } // Primer argumento, primera iteración externa, valor: 1
( 1 ) => { 1*4, 1 } = {4,1} // Segundo argumento, recibe 1 en: a y se evalúa ->
// Primero transformaría el 4 (Primera iteración interna):
( 1, 4 ) => 4 -1 = 3 // El tercer argumento, recibe 1 en: a y 4 en b dando como resultado: 3.
// Luego transformaría el 1 (Segunda iteración interna):
( 1, 1 ) => 1 -1 = 0 // Tercer argumento, recibe 1 en: a y 1 en b dando como resultado 0.
Por lo anterior, el resultado de la lista de la primera iteración más externa pasa de {4, 1} a {3,0}; este mismo proceso se repite para cada ítem en el primer argumento, por lo que el resultado final sería: {3, 0} & {6, 0} & {9, 0} & {12, 0}, como se puede apreciar el resultado final es la concatenación de estos valores en una única lista.
— El siguiente esquema resume el proceso:
NOTA ANTES DE CONTINUAR CON EL ESQUEMA: Vale la pena enfatizar que este proceso se repite para cada iteración externa, de aquí que el conteo de las iteraciones internas se reinicie, puede entenderse como iteraciones anidadas.
Y así sucesivamente…
— Así luce el resultado en el área de resultados del editor de Power Query:
Definición de la Función List.TransformMany
Una vez entendida la función List.TransformMany, la descripción en la documentación en Microsoft ya no sonará tan extraña y hasta será clara, no obstante, la «decoraré» un poco con el fin de brindar más contexto y complementarlo según lo comentado.
La función List.TransformMany recorre cada ítem de la lista de su primer parámetro (List), y en cada iteración (ítem del primer argumento) genera una nueva lista (y debe ser una lista) según lo señalado en el cuerpo del valor función indicado en su segundo parámetro (CollectionTransform), que tiene la firma ( x as any ) =>…, a esta última colección de valores se aplica la transformación del tercer parámetro (ResultTransform) mediante un valor de función al estilo de List.Transform, que tiene la firma ( x as any , y asany ) =>… .
Por favor cuéntame en los comentarios si ahora es entendible la función…
Concretamente: la función List.TransformMany devuelve una lista cuyos ítems se proyectan a partir de la lista de entrada gracias a dos iteraciones anidadas.
SINTAXIS Y PARÁMETROS:
- List: Lista de entrada donde se va a iterar pasando por cada uno de los ítems.
- CollectionTransform: Función que recibe en su primer parámetro el valor del ítem actual en la iteración, y de allí genera una nueva lista para que itere la función del tercer parámetro. El resultado de esta función debe ser siempre un valor de tipo lista.
- ResultTransform: Transformación que se aplica a cada ítem en la lista generada en el segundo argumento, tomando como valores de entrada para la expresión lambda, el valor de la iteración externa (a) y el valor en la iteración interna (b) de la lista generada en el segundo argumento.
.
Ejemplo con List.TransformMany
Antes de ver una aplicación con la función List.TransformMany primero veamos algunos ejemplos a problemas puntuales para interiorizarla más.
Lista de Meses en Diferentes Idiomas
Supongamos que deseamos producir una lista con los nombres de los meses en diferentes idiomas: español, inglés, alemán, francés, italiano, portugués y japones; con las iteraciones anidadas de List.TransformMany lo conseguimos variando el segundo argumento de la función Date.MonthName, así:
Como adición, es sabido que muchas veces queremos el resultado agrupado por idioma como lista en la lista general (lista de listas), y aunque contamos con la función de la librería estándar List.Split si el resultado lo queremos para implementarla en una función de generación de tabla y crearla la listas internas que no tienen la misma cantidad de ítems tendremos filas de error, y a pesar de que para este escenario puntual no hay problema, siempre es mejor acostumbrarnos a algo más robusto, por esto te recomiendo importar la función List.ChunkUp de POWER QUERY ZONE que es más conveniente, una vez integrada a la librería de funciones nativas y nombrada según lo sugerido como: List.ChunkUp, tenemos:
Así luce el resultado en el área de resultados del editor de Power Query, observando la vista previa del ítem 2:
Creando un Patrón
Crear patrones con esta función es muy fácil, por ejemplo: Generar una lista que repita el patrón 0,1 y 2 cinco veces, se soluciona así:
El resultado:
No es la única forma de generar este patrón, hay muchas otras con el Lenguaje M, pero lo que queremos es practicar un poco con List.TransformMany.
Creación de las Tablas de Multiplicar
Concebir la función List.TransformMany como dos iteraciones anidadas nos genera una lluvia de ideas, a modo de practica gracias a ella generar las tablas de multiplicar es sencillo:
— El resultado se visualiza de la siguiente manera:
La iteración externa va del 1 al 10 como se comprueba en el primer argumento de la función List.TransformMany, luego, en cada parada generamos una nueva lista del 1 al 10 donde se iterará (iteraciones internas) correspondiente al segundo argumento, de esta forma apalancados del tercer argumento lo que hacemos es multiplicar valor de la iteración externa por el valor de la iteración interna, claro, en el ejemplo se convierte a texto para presentar el resultado de la forma: «a x b = ab» que tendría el aspecto en la interfaz «7 x 2 = 14«.
A parte de los detalles de la función List.TransformMany, esta queda abrazada por la función List.ChunkUp de POWER QUERY ZONE, pues ella nos colabora agrupando cada 10 ítems, de esta forma, nos queda fácil transformarlo en una tabla con la función Table.FromColumns, función que en su primer parámetro recibe una lista de listas (por eso la necesidad de List.ChunkUp), donde cada ítem se convertirá en una columna en la tabla que se generará.
En el segundo argumento de Table.FromColumns empleamos { 1 .. 10 } debido a que la forma { «1» .. «10» } no es factible dado que el extremo superior («10») tiene dos caracteres, si fuera { «1» .. «9» } sería válido, pero no en este caso, es por ello que utilizamos List.Transform debido a que generamos los nombres de las columnas en esta parte con { 1..10 }, no obstante, es necesario transformar cada ítem a valor de tipo texto porque son los encabezados de columnas, además, de paso nos sirve para poner el prefijo: «Table del … «.
List.TransformMany Aplicado
La función List.TransformMany abre un mundo nuevo para casos de uso y en esta publicación voy a brindar uno de ellos.
Consolidar Hojas Especificas de Múltiples Archivos en una Carpeta Según Criterio
En una carpeta tenemos múltiples archivos de Excel con múltiples hojas, donde cada archivo está asociado a una librería, mientras que cada hoja tiene el detalle de la empresa intermediaria que hace la distribución del libro, como: DHL, FedEx, etc. Además, la librería puede hacer la distribución de sus productos dependiendo del caso, por eso también tendrá hojas asociadas a la empresa según el distribuidor interno.
Véase la siguiente taba que resumen archivos y hojas para un mayor entendimiento:
Nuestra misión consiste en crear un consolidado de todas las hojas en los distintos archivos, pero sólo de la empresas intermediarias de distribución, por lo que, para el primer archivo (BarnesAndNoble) toca excluir las tablas de las hojas BarnesAndNobel_Local y BarnesAndNoble_Site, los motivos para esto pueden ser muchos, por ejemplo, sólo queremos analizar los proveedores.
Puedes descargar los archivos aquí.
Con sólo la interfaz logramos conseguir la solución, pero se crean un montón de pasos innecesarios y hasta la consulta de muestra es creada automáticamente, y aunque soy el admirador número uno de la Consulta de Muestra y sus bondades, también es cierto que ella produce un cúmulo de consultas auxiliares que puede ser algo engorroso, por añadidura el código para consolidación es demasiado abultado con ciertas vueltas innecesarias.
— Veamos:
En la presente publicación no detallare esa solución (pero te puedes dar una idea en este vídeo), en su lugar, te mostraré como gracias a las maravillosa de la función List.TransformMany lo logramos de una manera prolija y alterna.
— Dale un vistazo a la solución en el script enseguida:
Como se corrobora no hay consultas intermedias ni nada parecido, es una solución que va al corazón del asunto, esto no quiero decir que el método de la interfaz sea malo, tan sólo que tenemos otra posibilidad desde el punto de vista de programación que en consecuencia brinda mayor flexibilidad (todo depende del escenario) – Además, es necesario ejecutar un análisis riguroso de rendimiento para determinar según el caso que conviene más, eso lo haremos futuras publicaciones.
Explicación 0 a 100
A continuación, voy desglosar paso por paso, con lo primero que nos encontramos es con:
Que es tan sólo el paso para conectarnos a una carpeta, yo he creado el parámetro wd previamente donde tengo la mayor parte de la ruta, es decir: «C:\Miguel Caballero\EFB\C – Productos (Development Product)\Libros\07 – El ADN del Lenguaje M\Capítulo 05\», esto simplifica la escritura, en otras palabras, es una forma manual de establecer un directorio de trabajo.
Si lo visualizamos en el área de resultado sería:
Por el lado de los argumentos de List.TransformMany tenemos (Por favor, lee atentamente los comentarios en el Script):
Es probable que hayas sentido algo compleja esta función, por es algo natural, eso mismo sucede la primera vez que estudiamos bucles como: for … next, Do loop, etc. En otros lenguajes de programación, sin embargo, luego de un repaso y un poco de practica no podremos «vivir» sin ellos, eso mismo tiene la función List.TransformMany pero desde el punto de vista de un lenguaje de programación funcional.
La última parte:
En ella se toma la Lista del resultado de List.TransformMany y se transforma en una tabla de una sola columna con la función Table.FromValue, teniendo así una tabla de una columna donde sus celdas son valores de lista, el último paso es sólo para abrazar el consolidado final.
Por favor, comenta y regálame tus comentarios, feedback y correcciones; así me animo a seguir revelando los secretos del Lenguaje M con contenido 100% original e inédito. Estaré leyendo y respondiendo todos los comentarios activamente.
— Miguel Caballero