Power Query: Combinaciones sin repetición

Hace algunos años escribí un artículo donde mostraba como obtener un listado de las combinaciones sin repetición.
En aquel momento el ejemplo lo desarrollé empleando Access... asi que estos días me puse a pensar si era posible realizar una operación similar con Power Query... y a esto he llegado.

Donde en una tabla 'TblVBLE'añadimos nuestros elementos de un solo caracter o dígito!!, y en la celda D3 con un nombre definido asignado 'ndGrupo' y a través de una consulta de Power Query conseguimos nuestro listado :O
Hay dos pasos previos a realizar antes de ver nuestro código M.
El primero, es obvio, cargar solo como conexión nuestra Tabla... asegurándonos que solo queda cargada con Origen, sin Tipo cambiado.

Solo hemos empleado el asistente de carga... y el código quedaría:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblVBLE"]}[Content]
in
    Origen

En segundo lugar cargaremos la celda con el nombre definido, pasándola como parámetro (puedes ver aquí un ejemplo).

El código quedaría:

let
    Origen = Excel.CurrentWorkbook(){[Name="ndGrupo"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Column1", Int64.Type}}),
    Column1 = #"Tipo cambiado"{0}[Column1]
in
    Column1

El proceso consiste en hace clic derecho sobre la celda cargada y presionar Rastrear desagrupando datos

Con las etapas iniciales cumplidas pasamos ahora a la parte 'compleja' donde haremos un uso extensivo de código M.
Desde el editor de Power Query navegaremos a la ficha Inicio > grupo Nueva Consulta > Nuevo Origen > Otros orígenes > Consulta en blanco donde insertaremos el siguiente código...(he nombrado a esta consulta como 'qrVBLE_Combinaciones')

let
    //añadimos tantas veces como se indique en la celda 'ndGrupo' la misma TblVBLE
    AddElto1 = if pGrupo>=1 then TblVBLE else null,
    AddElto2 = if pGrupo>=2 then Table.AddColumn(AddElto1, "elementos1", each TblVBLE) else AddElto1,
    AddElto3 = if pGrupo>=3 then Table.AddColumn(AddElto2, "elementos2", each TblVBLE) else AddElto2,
    AddElto4 = if pGrupo>=4 then Table.AddColumn(AddElto3, "elementos3", each TblVBLE) else AddElto3,
    AddElto5 = if pGrupo>=5 then Table.AddColumn(AddElto4, "elementos4", each TblVBLE) else AddElto4,
    
    //esto generará, al expandirlas, todas las combinaciones posibles con repeticiones y sin considerar el orden....
    ExpandeElementos1 = if pGrupo>=2 then Table.ExpandTableColumn(AddElto5, "elementos1", {"Eltos"}, {"Eltos.1"}) else AddElto5,
    ExpandeElementos2 = if pGrupo>=3 then Table.ExpandTableColumn(ExpandeElementos1, "elementos2", {"Eltos"}, {"Eltos.2"}) else ExpandeElementos1,
    ExpandeElementos3 = if pGrupo>=4 then Table.ExpandTableColumn(ExpandeElementos2, "elementos3", {"Eltos"}, {"Eltos.3"}) else ExpandeElementos2,
    ExpandeElementos4 = if pGrupo>=5 then Table.ExpandTableColumn(ExpandeElementos3, "elementos4", {"Eltos"}, {"Eltos.4"}) else ExpandeElementos3,
    
    //eliminamos duplicados exactos al seleccionar todos los campos añadidos.
    //OJO, por que volveremos a este punto para trabajar con las columnas existentes en este instante!!!
    DuplicadoQuitados = Table.Distinct(ExpandeElementos4),

    //OJO en el paso siguiente que se incluye la ordenación por caracteres!! (List.Sort)!!
    //se concatenan todas las columnas existentes!!
    AddCol_Combinada = Table.AddColumn(DuplicadoQuitados, "Combinada", each Text.Combine(List.Sort(List.Transform(Record.FieldValues(_),Text.From)),"")),
    
    //incluiremos una evaluación de TRUE / FALSE para saber si existen elementos repetidos (List.IsDistinct) !!!
    AddDistintos = Table.AddColumn(AddCol_Combinada, "Distintos", each List.IsDistinct(Text.ToList([Combinada]))),
    //Filtramos aquellos registros sin elementos repetidos, independientemente del orden
    FiltrosTRUE_distintos = Table.SelectRows(AddDistintos, each ([Distintos] = true)),
    //Del resultado obtenido, ya sin registros con elementos repetidos, quitamos los duplicados
    //es decir, aquellos registros con mismos elementos colocados en distintas posiciones, por ejem. 1 2 3 y 3 2 1
    DuplicadosQuitadosCombi = Table.Distinct(FiltrosTRUE_distintos, {"Combinada"}),
    
    //Recuperamos una lista de todas las columnas disponibles en este momento (determinado por la celda ndGrupo)
    ColNames = Table.ColumnNames(DuplicadosQuitadosCombi),
    //se asocia a cada columna una tipología de dato Text
    TransformaTipoCol = List.Transform(ColNames, each {_, type text}),
    //para acabar asignando dicha tipología a cada columna
    CambioTipo = Table.TransformColumnTypes(DuplicadosQuitadosCombi, TransformaTipoCol),
    //... sí, con List.Select se consigue más fácilmente... pero eso para otro post :D

    //Con Table.CombineColumns unimos las columnas existentes en un paso inicial 'DuplicadoQuitados
    //emplearemos como separador la barra vertical |
    AddCombinaciones= Table.CombineColumns(CambioTipo, 
                        Table.ColumnNames(DuplicadoQuitados),
                        Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
                        "Combinaciones"),
                    
    //Finalmente quitamos todas las columnas excepto la generada con Table.CombineColumns
    OtrasColumnasQuitadas = Table.SelectColumns(AddCombinaciones,{"Combinaciones"})
in
    OtrasColumnasQuitadas

Presionamos 'Listo' y Cerrar y cargar como tabla en nuestra hoja de cálculo...
Ya tenemos nuestro listado de combinaciones sin repetición !!


El funcionamiento es muy simple... escribimos en nuestra tabla azul 'TblVBLE' los elementos (de un solo caracter/dígito)... tantos como necesitemos; a continuación indicamos de que tamaño serán las combinaciones... tomados de x en x... y Actualizaremos nuestra consulta.
La consulta está construida para un máximo de grupos de 5 elementos, es decir, combinaciones tomadas de 1 en 1, 2 en 2, 3 en 3, 4 en 4 o 5 en 5...
¿Por qué no de 6 en 6?, porque los primeros pasos de la consulta montada, al tratar de combinar seis tablas con todas sus posibilidades desborda el máximo de capacidad de Power Query y de Excel, por supuesto.
De echo es algo que puede ocurrir para combinaciones tomadas de 5 en 5 con un número alto de elementos en la TblVBE.
En algunos equipos además podria saltar un aviso de falta de memoría:

Probémoslo. Un listado de elemenos: A B C D E F G (7 elementos) tomados de 5 en 5 nos genera el siguiente listado de 21 combinaciones sin repetición

Se verifica que el resultado de la función (en la celda D4):
=COMBINAT(D2;D3)
nos devuelve 21.. coincide con el conteo de combinaciones obtenida...


Analicemos a grandes rasgos el código empleado.
La primera parte:

    //añadimos tantas veces como se indique en la celda 'ndGrupo' la misma TblVBLE
    AddElto1 = if pGrupo>=1 then TblVBLE else null,
    AddElto2 = if pGrupo>=2 then Table.AddColumn(AddElto1, "elementos1", each TblVBLE) else AddElto1,
    AddElto3 = if pGrupo>=3 then Table.AddColumn(AddElto2, "elementos2", each TblVBLE) else AddElto2,
    AddElto4 = if pGrupo>=4 then Table.AddColumn(AddElto3, "elementos3", each TblVBLE) else AddElto3,
    AddElto5 = if pGrupo>=5 then Table.AddColumn(AddElto4, "elementos4", each TblVBLE) else AddElto4, 

Añadimos tantas veces la 'TblVBLE' como hayamos seleccionado en la celda D3 con el seleccionable preparado.
Con la forma condicional if... then... else... controlamos cuantas columnas añadir (Table.AddColumn)

El siguiente tramo:

    //esto generará, al expandirlas, todas las combinaciones posibles con repeticiones y sin considerar el orden....
    ExpandeElementos1 = if pGrupo>=2 then Table.ExpandTableColumn(AddElto5, "elementos1", {"Eltos"}, {"Eltos.1"}) else AddElto5,
    ExpandeElementos2 = if pGrupo>=3 then Table.ExpandTableColumn(ExpandeElementos1, "elementos2", {"Eltos"}, {"Eltos.2"}) else ExpandeElementos1,
    ExpandeElementos3 = if pGrupo>=4 then Table.ExpandTableColumn(ExpandeElementos2, "elementos3", {"Eltos"}, {"Eltos.3"}) else ExpandeElementos2,
    ExpandeElementos4 = if pGrupo>=5 then Table.ExpandTableColumn(ExpandeElementos3, "elementos4", {"Eltos"}, {"Eltos.4"}) else ExpandeElementos3,

Expandimos las columnas añadidas...
Al añadir a la misma consulta la misma tabla origen, sin combinaciones de ningún tipo, obtenemos un listado exponencial de todas las combinaciones posibles con repetición de elementos y duplicidades...
Por ejemplo, de una lista A B C tomadas de 2 en 2 obtendríamos en este último paso lo siguiente:
Eltos Eltos.1
A A
A B
A C
B A
B B
B C
C A
C B
C C

Otro paso

    //eliminamos duplicados exactos al seleccionar todos los campos añadidos.
    //OJO, por que volveremos a este punto para trabajar con las columnas existentes en este instante!!!
    DuplicadoQuitados = Table.Distinct(ExpandeElementos4),

Paso que elimina posibles registros idénticos...
Este paso además es importante por que la situación actual de columnas generadas en este momento es básico para pasos posteriores!!.

Más pasos...

     //OJO en el paso siguiente que se incluye la ordenación por caracteres!! (List.Sort)!!
    //se concatenan todas las columnas existentes!!
    AddCol_Combinada = Table.AddColumn(DuplicadoQuitados, "Combinada", each Text.Combine(List.Sort(List.Transform(Record.FieldValues(_),Text.From)),"")),

En este paso de la consulta añadimos una columna personalizada que combina (Text.Combine) los valores de las columnas existentes (sean cuales fueran!!), pero ordenandos en sentido ascendente (List.Sort)
Esta ordenación es relevante puesto que en un paso posterior me permitirá eliminar registros con iguales elementos en diferentes columnas...
Eltos Eltos.1 Combinada
A A AA
A B AB
A C AC
B A AB
B B BB
B C BC
C A AC
C B BC
C C CC

En nuestro ejemplo tenemos A-B y B-A con igual resultado, ya que para nuestro efecto es la misma combinación...

Seguimos..

      //incluiremos una evaluación de TRUE / FALSE para saber si existen elementos repetidos (List.IsDistinct) !!!
    AddDistintos = Table.AddColumn(AddCol_Combinada, "Distintos", each List.IsDistinct(Text.ToList([Combinada]))),
    //Filtramos aquellos registros sin elementos repetidos, independientemente del orden
    FiltrosTRUE_distintos = Table.SelectRows(AddDistintos, each ([Distintos] = true)),
    //Del resultado obtenido, ya sin registros con elementos repetidos, quitamos los duplicados
    //es decir, aquellos registros con mismos elementos colocados en distintas posiciones, por ejem. 1 2 3 y 3 2 1
    DuplicadosQuitadosCombi = Table.Distinct(FiltrosTRUE_distintos, {"Combinada"}),

En estos pasos añadimos una nueva columna calculada, donde al aplicar sobre la columna anterior la función (List.IsDistinct) obtenemos un FALSE o TRUE, según tenga elementos coincidentes o no respectivamente.
Por ejemplo, A A devolvería FALSE, y A B TRUE
Aplicamos un filtro estándar y nos quedamos solo con los TRUE, esto es, solo con los registros en los que no se repiten los elementos...
Finalmente aplicamos una acción para eliminar los posibles duplicados (antes comentados):
A B - AB
B A - AB

Más etapas de nuestra consulta...

  //Recuperamos una lista de todas las columnas disponibles en este momento (determinado por la celda ndGrupo)
    ColNames = Table.ColumnNames(DuplicadosQuitadosCombi),
    //se asocia a cada columna una tipología de dato Text
    TransformaTipoCol = List.Transform(ColNames, each {_, type text}),
    //para acabar asignando dicha tipología a cada columna
    CambioTipo = Table.TransformColumnTypes(DuplicadosQuitadosCombi, TransformaTipoCol),
    

Conjunto de pasos esencial!!.
Primero tenemos un listado de los nombres de las columnas existentes con (Table.ColumnNames)... recuerda sean las que sean, todas las generadas en los primeros pasos (1,2,3,4 o 5!!)
A cada columna le asociamos una característica 'type text' usando (List.Transform)
Acabamos asignando con (Table.TransformColumnTypes) a cada columna el tipo de datos grabado (como texto)
Fíjate bien sobre qué puntos trabaja cada función!!!.
Ciertamente con la funcíón List.Select se consigue más fácilmente... pero esto lo veremos en otro post.

Casi llegamos...

  //Con Table.CombineColumns unimos las columnas existentes en un paso inicial 'DuplicadoQuitados
    //emplearemos como separador la barra vertical |
    AddCombinaciones= Table.CombineColumns(CambioTipo, 
                        Table.ColumnNames(DuplicadoQuitados),
                        Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
                        "Combinaciones"), 

Unificamos en una columna única, como unión de los valores de cada columna existente en el paso 'DuplicadoQuitados' (recuerda que te avisé en su momento!!). La función (Table.CombineColumns) nos ayuda con esto...

Y en el último paso sencillamente nos quedamos con la columna combinada resultante descartando todas las demás...

 //Finalmente quitamos todas las columnas excepto la generada con Table.CombineColumns
    OtrasColumnasQuitadas = Table.SelectColumns(AddCombinaciones,{"Combinaciones"})   

Un largo código, con mucha función M aplicada, pero muy potente!!.
Espero te resulte de interés...