Análisis combinatorio usando Power Query en LuckyTemplates

Quiero profundizar en algo llamado análisis combinatorio. Esto se puede definir como la elección y disposición de elementos de conjuntos de acuerdo con reglas prescritas. Puede sonar esotérico, pero en realidad tiene algunos casos de uso muy prácticos. Puede ver el video completo de este tutorial en la parte inferior de este blog.

En este tutorial, le mostraré cómo hacer esto usando Power Query en LuckyTemplates.

Tabla de contenido

• Caso de ejemplo de análisis combinatorio
• Análisis combinatorio: permutaciones en Power Query
• Análisis Combinatorio: Combinaciones en Power Query
• Conclusión

Caso de ejemplo de análisis combinatorio

En este análisis combinatorio de ejemplo, la tarea era que teníamos una lista de seis almacenes y necesitábamos generar cada conjunto único de tres. Suena como un problema bastante sencillo, pero este es uno de esos casos en los que el idioma realmente importa.

La respuesta correcta aquí es 216, 120, 56 o 20. Entonces, dependiendo de cómo definas ese conjunto único y las reglas que prescribiste, podrías equivocarte por un factor de 10 si no obtienes el conjunto correctamente.

Y entonces, hay dos dimensiones que importan aquí. La primera es "¿ importa el orden ?" Entonces, cuando hablamos de tres conjuntos de tres, ¿ABC es equivalente a BCA? Entonces, ¿importa el orden? Y si el orden importa , estamos hablando de permutaciones .

Un buen ejemplo de permutaciones donde el orden podría ser importante es (estamos hablando de un contexto de almacén) en el caso del tiempo de viaje. Es muy posible que si visita el almacén A, luego el almacén B y luego el almacén C, el tiempo de viaje debido al flujo de tráfico con tráfico contra tráfico, la hora en que llegó a cada almacén, podría ser bastante diferente según el pedido. en que los visitas.

La otra cara de esto es que " el orden no importa ", en cuyo caso estamos hablando de combinaciones , no de permutaciones. El caso de uso aquí que es común es la distancia. Por ejemplo, en línea recta, la distancia de A a B a C no va a ser diferente de la distancia de B a C a A. Esos van a ser considerados en un contexto de combinación.

Las combinaciones se consideran duplicados entre sí. Mientras que, en un contexto de permutación, se considerarán únicos.

Entonces, como puede ver, un candado de combinación realmente debería ser un candado de permutación porque en ese caso, el orden sí importa, por lo que es una permutación, no una combinación.

La segunda pregunta o dimensión que importa es " una vez que se elige un artículo, ¿se puede volver a elegir ?" Por ejemplo, si elegimos A como nuestro primer almacén, ¿podemos volver a elegir A o tenemos que elegir elementos únicos cada vez?

El término que usamos aquí es " con reemplazo " o " sin reemplazo ". Entonces, si un artículo se puede elegir nuevamente, eso es con reemplazo, lo que significa que usted lo elige. Y, en cierto sentido, vuelve al contenedor de selección y se vuelve a seleccionar. Si la respuesta es no, es sin reemplazo.

Y así, puede ver en esta solución que las permutaciones con reemplazo son las menos restrictivas. Así que ese es el que nos da 216 conjuntos únicos. Y las combinaciones sin reposición es la más restrictiva dándonos 20.

Solo vamos a usar estas fórmulas como una forma de verificar nuestros resultados de Power Query . Así que recordaremos 216, 120, 56 y 20. Ahora saltemos a Power Query y veamos cómo se desarrolla todo este análisis combinatorio.

Análisis combinatorio: permutaciones en Power Query

En Power Query, tengo aquí un caso muy simple con seis almacenes con nombre.

Y he desarrollado esta función de Power Query M, que he usado para procesar combinaciones y permutaciones.

Primero, voy a desarrollar ese primer caso de uso, que es permutaciones con reemplazo , el menos restrictivo del grupo. La forma de hacerlo en Power Query es tomar esta tabla de almacenes y hacer referencia a ella (en DAX, usamos CROSSJOIN ).

Llamaré a esto, Per w Rep (permutaciones con reemplazo).

Hay varias formas de realizar una unión cruzada en Power Query. La forma más fácil que he encontrado es simplemente ir Agregar columna , luego Columna personalizada . En la fórmula de la columna personalizada, haga referencia a la tabla Almacenes .

Y lo que encontraremos cuando hagamos clic en Aceptar es que genera una tabla anidada.

Luego, expandimos eso y desmarcamos Usar el nombre de las columnas originales como prefijo .

Y obtenemos esta Location.1 , que es cada combinación posible de dos. Entonces toma las primeras seis referencias contra las otras seis combinaciones. El segundo seis hace lo mismo hasta que tenemos 36 conjuntos diferentes.

Podemos hacer el tercero haciendo esto de nuevo. Así que vamos Agregar columna, luego una columna personalizada y luego Almacenes.

Y tendremos las 216 filas como permutaciones con reemplazo.

Y pueden ver desde aquí abajo, 3 columnas, 216 filas.

El siguiente caso de uso son las permutaciones sin reemplazo . Lo que estamos pensando aquí es básicamente todo lo que tiene un elemento repetido. Entonces, en esta tabla, vamos a ver desde la fila 1 hasta la fila 8.

Para ello, echaremos un vistazo a esta función, fxSortAndIsDistinct .

En el Editor avanzado, puede ver que esto básicamente solicita una tabla y luego hace un SortLocs . Pero la parte importante aquí para "con reemplazo" o "sin reemplazo" es que esta es una función IsDistinct . Lo que esto hace es básicamente tomar una lista de elementos y determinar verdadero o falso, si es distinto o si tiene duplicados en la lista de elementos.

Así que vamos aquí, seleccionamos Perm w Rep y presionamos Invocar .

Esto nos dará estas dos nuevas columnas, SortLocs e IsDistinct . Ahora, recuerde que los primeros ocho registros tenían duplicados y el noveno registro fue la primera vez que vimos los distintos . Es por eso que la novena fila aquí es la primera que aparece como verdadera.

Si recordamos los números que teníamos en los cálculos factoriales, esperamos 120 registros si eliminamos los registros no distintos. Entonces, intentemos filtrar aquí y sacar el FALSO.

Mientras hacemos eso, obtenemos aquí abajo exactamente las 120 filas que estamos esperando. Ahora cambiemos el nombre de esto como Perm wo Rep (permutación sin reemplazo).

Análisis Combinatorio: Combinaciones en Power Query

Ahora haremos las combinaciones con reemplazo . Para hacer eso, simplemente hacemos el proceso nuevamente y repetimos el paso (Función Invocada). Así que está bien que tengamos duplicados, pero no queremos conjuntos que tengan los mismos elementos , pero en un orden diferente.

En este caso, cuando hablamos de combinaciones. Si recuerda que ABC es igual a BCA es igual a CAB, todos se consideran duplicados entre sí. Y la forma en que determinamos eso es a través de la función Ordenar ( fxSortAndIsDistinct ).

Si volvemos a la función en el Editor avanzado, veremos en esa segunda columna que hemos tomado esa lista y la hemos ordenado alfabéticamente. Luego, extrajimos los valores delimitados por comas de esa lista. Ahora tenemos cada conjunto ordenado alfabéticamente. Hemos normalizado el orden para que sea más fácil encontrar duplicados.

Ahora, si volvemos a esta función invocada, recuerde que estamos buscando 56 registros aquí. Así que tomamos este tipo de ubicación ( SortLocs ) y eliminamos nuestros duplicados .

Con eso, obtenemos 56 filas.

Y así, este es nuestro tercer caso de uso. Cambiemos el nombre de esto como CC w Rep (combinaciones con reemplazo).

Terminemos esto tomando nuevamente la tabla de permutaciones con reemplazo (Perm w Rep) y hagamos el mismo patrón (invoke). Luego, vamos a eliminar los duplicados (columna SortLocs) porque esta es una combinación y el orden no importa. No tiene reemplazo, por lo que también queremos solo la lista distinta . Entonces, hacemos clic en FALSO en la columna IsDistinct.

Con esto, obtenemos 20 registros. Y ahora tenemos nuestros cuatro casos de uso.

Clasificación de las columnas de la tabla de fechas en LuckyTemplates
Guía para principiantes del código M en LuckyTemplates
Convierta la función de tabla de fechas en una consulta de tabla en LuckyTemplates

Conclusión

Así que ahora tenemos nuestros cuatro casos de uso. Puede ver realmente cuán poderoso es esto en términos de desarrollar ese análisis combinatorio que le brinda control sobre el reemplazo y los elementos distintos.

Esto es incluso aplicable en algunos casos más complejos en los que tiene un reemplazo parcial, o tal vez selecciona un elemento duplicado para el primero, pero no la segunda vez. Todavía puede manejar eso muy bien dentro de Power Query .

Espero que hayas encontrado esto útil. Echa un vistazo a los enlaces a continuación para obtener más contenido. También puede ver el video completo de este tutorial a continuación.

¡Mis mejores deseos!