Desglosando pares combinatorios

Hace unos días publiqué como obtener con Power Query un listado de las posibles combinaciones sin repetición a partir de un listado de elementos (ver aquí); hoy desarrollaremos con funciones una fórmula que aplica esas combinaciones generales a un caso concreto.
Supongamos un registro de veinte tiradas o sorteos de bonoloto, primitiva, etc... de seis elementos entre 1 y 49.
El objetivo es determinar cuales son las combinaciones (si es que existen) que más se repiten de entre esos veinte sorteos...

Vemos en el rango B4:G23 el detalle de los veinte sorteos (ordenados de izquierda a a derecha!!). Y pretendemos obtener estadísticas de:
- los números que más aparecen,
- los pares a-b más repetidos,
- las ternas a-b-c-d.
- los grupos de 4 elementos a-b-c-d,
- para finalmente hacer la misma operación con los grupos de 5 elementos a-b-c-d-e
Para ello con nuestra consulta vista en el post comentado obtenemos los listados de posiciones combinadas sin repetición...

Esas cinco tablas de la imagen anterior tienen los siguientes nombres:
- Tbl1en1
- Tbl2en2
- Tbl3en3
- Tbl4en4
- Tbl5en5
Tablas necesarias para automatizar el desarrollo buscado...

En la celda H1 he incluido una validación de datos tipo lista con elementos permitidos: 1 en 1; 2 en 2; 3 en 3; 4 en 4; 5 en 5
Y en H3 insertamos la fórmula desbordada:
=TRANSPONER(INDIRECTO("Tbl"&SUSTITUIR($H$1;" ";"")&"[Combinaciones]"))
que nos permite recuperar dinámicamente los elementos de nuestras combinaciones de 6 elementos tomados de n en n

Con la estructura desarrollada podemos incluir nuestra fórmula para obtener para cada sorteo cuales son esos pares, ternas, etc. de combinaciones.
Así pues en H4 añadimos:
=SI.ERROR(UNIRCADENAS("|";1;
INDICE($B4:$G4;
1;
COINCIDIR(--EXTRAE(SUSTITUIR(H$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(H$3;"|";"")));1);$B$3:$G$3;0)));
"")

La clave de esta fórmula reside en la función COINCIDIR empleada:
COINCIDIR(--EXTRAE(SUSTITUIR(O$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(O$3;"|";"")));1);$B$3:$G$3;0)
que nos sirve para indicar, basándose en el encabezado de B3:G3, y en la combinación de elementos, a qué números del sorteo corresponden.
Como 'valor buscado' de coincidir tenemos la fórmula:
--EXTRAE(SUSTITUIR(O$3;"|";"");SECUENCIA(1;LARGO(SUSTITUIR(O$3;"|";"")));1)
donde indicamos que recuperaremos cada posición (de nuestras combinaciones) exluyendo el caracter 'barra vertical |'.
El primer argumento de EXTRAE: SUSTITUIR(O$3;"|";"") retorna, a partir de la combinación de posiciones: 1|2|3 la cadena 123 sobre la que trabajar.
El segundo: SECUENCIA(1;LARGO(SUSTITUIR(H$3;"|";""))) devuelve una matriz de columnas del mismo ancho que elementos hayamos combinado, por ejemplo, si la combinación fuera de 3 en 3, la matriz sería siempre: {1\2\3}
El tercer argumento será siempre 1, ya que solo queremos extraer una única posición...
Acabamos convirtiendo en valor el texto devuelto por EXTRAE aplicándolo el doble menos: --EXTRAE(...)
Todo este COINCIDIR(...) nos devuelve una matriz de columnas con las numeraciones representadas en los encabezados... por ejemplo, del texto de la celda con valor 2|4|6 obtendríamos la matriz {2\4\6}

Esta matriz en el contexto de INDICE me permite recuperar los números del sorteo que correspondan a dichas posiciones de columnas...

Copiaremos la fórmula al rango H4:AB23.

Con nuestro detalle de pares desglosado para cada sorteo podemos aplicar alguna de las técnicas explicadas en este post 'Fórmulas desbordadas: Pasar de matriz a vector' (con Power Query o con funciones)... -leer más-
Quedándonos un listado ordenado de mayor a menor por el número de apariciones de esos veinte sorteos.

Para obtener las distintas estadísticas bastará ir cambiando en la celda H1 validada entre las distintas opciones...

Esta operación es algo que constantemente me solicitan los amantes del azar... y los que piensan que con análisis de históricos se puede vencer a la suerte ;-)
Para todos ellos.

Seguir leyendo...

Fórmulas desbordadas: Pasar de Matriz a Vector

Hace ya algunos años ya expuse la forma, con funciones estándar, de pasar valores de una matriz n filas x m columnas a un vector de (n x m) filas y 1 columna (ver aquí).
Hoy mejoraremos aquella fórmula empleando la función Desbordada SECUENCIA

Comprobamos que sobre una matriz, en nuestro ejemplo, de 5 filas x 3 columnas (rango B2:D6) con un nombre definido asignado 'ndCiudades' podemos insertar en F2:
=INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)

Hubiera sido con el método tradicional:
=INDICE(ndCiudades; 1+ENTERO((FILA(A1)-1)/COLUMNAS(ndCiudades)); RESIDUO(FILA(A1)-1+COLUMNAS(ndCiudades);COLUMNAS(ndCiudades))+1)
La mejora viene en el desbordamiento que aparece al emplear SECUENCIA, la cual genera una lista de valores para recuperar datos por fila:
SECUENCIA(CONTARA(ndCiudades);1;0;1/3)
De 15 filas de alto y 1 una columna de ancho
Comenzando desde cero!!
Y con un paso de 1/3 (1 entre el número de columnas). Si lo prefieres: 1/COLUMNAS(ndCiudades)

Por otra parte para recuperar datos por columnas:
SECUENCIA(CONTARA(ndCiudades);1;0;1)
que genera una lista de 15 filas y una columna
Comenzando desde cero!!
Y con un paso de 1.
Ambas secuencias se ajustan, como se exponía en el post previo, con las funciones TRUNCAR (o ENTERO) y RESIDUO:

Estos vectores 'virtuales' conseguidos con SECUENCIA nos sirven para listar y recuperar con INDICE cada uno de los elementos de nuestro rango 'ndCiudades': TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1
con TRUNCAR nos quedamos con la parte entera de esos valores incrementados de 1/3 en 1/3... a cuya parte entera incrementamos con +1
RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1
con RESIDUO (euqivale al resto de un cociente), al dividir entre el número de columnas del rango obtenemos la secuencia 1,2,3 correspondiente a nuestras columnas...

La ventaja de emplear SECUENCIA frente a la forma clásica es que automáticamente se desborda, ajustándose a las dimensiones de nuestra matriz.

Llegados a este punto, y como has podido comprobar en la imagen existen ciudades repetidas...
Así pués para obtener un listado de ciudades únicas ordenadas por repeticiones de mayor a menor... aplicaremos la siguiente fórmula (basada en la anterior).
Para facilitar el uso, la fórmula anterior la incluimos dentro de un nombre definido:

Tenemos en estos momentos dos nombres definidos:
ndCiudades =Hoja1!$B$2:$D$6
ndVector =INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)
Para nuestro objetivo de ordenar ciudades por apariciones necesitaremos llamar a UNICOS y ORDENARPOR.

Obviamente =UNICOS(ndVector) genera el listado de ciudades únicas... pero ordenadas según estaban en el origen.
Nosotros queremos llegar un poco más allá... queremos ordenarlas por número de veces que aparezcan.
Así nuestra fórmula deseada sería (en la celda F2):
=ORDENARPOR(UNICOS(ndVector);CONTAR.SI(ndCiudades;UNICOS(ndVector));-1)

Falta solo añadir un cálculo de conteo sencillo con CONTAR.SI sobre el rango anterior para verificar que el listado es el correcto... Así en G2 añadimos:
=CONTAR.SI(ndCiudades;F2#)

Alternativamente a esta técnica podemos emplear Power Query (como no). En este caso una herramienta ya vista en varias ocasiones en este blog como es Anular dinamización de columnas (Unpivot)
Como siempre, puesto que ya hemos asignado un nombre al rango de celdas, iremos a la ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango; y una vez cargado los datos y ya en el Editor de Power Query nos moveremos hasta el menú Transformar.

Suelo recomendar eliminar el paso creado 'Tipo cambiado' que habrá asignado un type text a las columnas...
Usaremos el asistente de grabación de pasos... muy sencillos.
Primero seleccionamos las tres columnas y presionamos Anular dinamización de columnas (Unpivot), consiguiendo el listado de quince ciudades tal cual aparecen en el rango de celdas (en su caso repetidas)

A continuación seleccionaremos la columna 'Valor' y agruparemos desde el menú de Inicio > grupo Transformar > botón Agrupar por y en la ventana de agrupación indicaremos como 'operación' el Recuento de filas

El resultado es el listado de ciudades únicas con el número de veces que aparecen repetidas a su lado (en la columna 'Recuento')

Obvio el último paso...Aplicar un orden descendente sobre la columna 'Recuento' y Cargar y cerrar en la hoja de cálculo...

Muy potentes herramientas nuevas de las que disponer ;-)

Seguir leyendo...

Fórmulas desbordadas: Añadir Totales Generales

Ya hemos hablado en varios post de estas magníficas fórmulas y funciones desbordadas. Sin duda una experiencia en Excel ;-)
Hoy combinaremos y aplciaremos este comportamiento desbordado sobre unas funciones clásicas: la función SI y SUMAR.SI.CONJUNTO.
Nuestra meta es lograr incorporar unos Totales generales por filas y columnas a una tabla de referencia cruzada de forma automática...

Partimos de una tabla 'TblDatos' en nuestra hoja de cálculo con campos: Año, País, Uds. A partir de dicha tabla queremos resumir o agrupar por Año y País las Uds vendidas, añadiendo además los Totales Generales por filas o columnas.

Comenzaremos incorporando los encabezados de la tabla resumen... para lo cual haremos uso de las funciones desbordadas UNICOS y ORDENAR.
Así en la celda I3 introducimos la siguiente fórmula:
=TRANSPONER(ORDENAR(UNICOS(TblDatos[Año])))
donde obtenemos los elementos únicos del campo 'Año', ordenados en Ascendente, y cuya matriz resultante transponemos de vertical a horizontal.

De forma similar obtenemos los encabezados para los países en la celda H4:
=ORDENAR(UNICOS(TblDatos[País]))
donde conseguimos en una matriz vertical los elementos únicos del campo 'País' ordenada en sentido Ascendente.

Finalmente, apoyándonos en estos rangos desbordados anteriores, construiremos nuestro resumen de uds vendidas por Año y País.
Entonces en I4 insertamos la fórmula buscada:
=SI.ERROR( SI(SECUENCIA(CONTARA(UNICOS(TblDatos[País]))+1;1)>CONTARA(UNICOS(TblDatos[País])); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#); SI(SECUENCIA(1;CONTARA(TRANSPONER(UNICOS(TblDatos[Año])))+1)>CONTARA(TRANSPONER(UNICOS(TblDatos[Año]))); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[País];H4#); SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#;TblDatos[País];H4#))); SUMA(TblDatos[Uds]))

Una fórmula algo larga, sin duda, pero sencilla de comprender... pero con una gran ventaja, y es que no necesita de ningún refresco ni actualización (como una tabla dinámica o una consulta de Power Query) para mostrar siempre los datos reales.
La explicamos.

Tenemos un primer uso de la función SI con una prueba lógica:
SI(SECUENCIA(CONTARA(UNICOS(TblDatos[País]))+1;1)>CONTARA(UNICOS(TblDatos[País]));
donde generamos una lista de números 1,2,3,.. hasta llegar al número que sobrepase el número de elementos únicos del campo 'País'; en nuestro ejemplo 1,2,3,4,5,6.
Comparando con el número de elementos únicos de 'País'.
Vemos la idea en la siguiente imagen

Esta prueba lógica nos dice, entonces, en qué fila nos encontramos... y si estuvieramos en la última fila (en nuestro ejemplo, la 6) aplicaría la opción de 'si_verdadero'
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#)
esto es, la suma acumulada para cada 'Año'.
De manera semejante aplicamos en nuestra fórmula un segundo condicional:
SI(SECUENCIA(1;CONTARA(TRANSPONER(UNICOS(TblDatos[Año])))+1)>CONTARA(TRANSPONER(UNICOS(TblDatos[Año])));
donde comparamos una lista de números 1,2,3,4,5,6,7 con el número total de elementos únicos del campo 'Año'

Identificando columna a columna si aplicamos un parcial o el Total General en la columna 7...
Del segundo condicional la opción de 'si_verdadero' que aplica cuando llegamos a esa columna 7 es:
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[País];H4#)
esto es, la suma acumulada para cada 'País'.

La última opción de nuestras condiciones nos sitúa en los casos existentes de Año-País, con un SUMAR.SI.CONJUNTO basado en dos criterios (obviamente Año y País):
SUMAR.SI.CONJUNTO(TblDatos[Uds];TblDatos[Año];I3#;TblDatos[País];H4#)

La fórmula acaba, o empieza según se mire, con un SI.ERROR para corregir el fallo que aparecería en el Total Absoluto (fila 6 y columna 7 de nuestro ejemplo), donde decimos que nos devuelva la suma de todas las unidades:
SUMA(TblDatos[Uds]).

En la primera imagen, a modo de comprobación veíamos una tabla dinámica donde por supuesto de forma más sencilla llegamos al mismo resultado... LLegando al conflicto de dos partes: uso tablas dinámicas - uso fórmulas desbordadas.
Comentaré ventajas e inconvenientes de ambas:
1- uso de tabla dinámica: mínimo esfuerzo en su construcción versus posibilidad de olvidar refrescar los datos y trabajar con información obsoleta no actualizada.
2- uso de fórmulas desbordadas: mayor trabajo en su elaboración versus datos constantemente actualizados.

Seguir leyendo...

Power Query: Ordenando caracteres por registro

En el día de hoy veremos un par de funciones M (List.Sort y Text.ToList) que nos permitiran descomponer por caracteres y ordenar estos para cada registro de una tabla.
El objetivo sería descomponer cada texto (cada registro) en caracteres individuales, para retornarlos después ordenados alfabéticamente...
Veremos dos métodos:

Como se observa en la imagen, ambos métodos, con igual resultado, ordenan caracteres de cada alfanumérico, i.e., tipo texto!! en esentido ascendente (o descendente si fuera el caso)...
Veamos los pasos a seguir...
Para un primer caso, más 'técnico'.
Desde la Tabla cargamos los datos la editor de Power Query (ficha Datos > grupo Obtener y transformar > Desde tabla o rango); y una vez cargado, y ya en el Editor de PQ, entraremos al editor avanzado y escribimos:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    AddOrdenaCaracteres  = Table.AddColumn(Origen, "Personalizado", each List.Sort(Text.ToList([nombres]))),
    ExtraeValores = Table.TransformColumns(AddOrdenaCaracteres, {"Personalizado", each Text.Combine(_), type text})
in
    ExtraeValores

En este caso la clave del asunto está en la línea donde aplicamos:
List.Sort(Text.ToList([nombres]))

En primer lugar con
Text.ToList(texto as text) : Devuelve una lista de valores de caracteres individuales (uno a uno) a partir del valor de texto especificado... por ejemplo, de 'excelforo' devolvería una lista {e x c e l f o r o}.
Sobre esta lista de caracteres obtenida aplicaremos el orden con
List.Sort(lista as list, optional comparisonCriteria as any): Ordena una lista de datos según los criterios opcionales especificados.
Se puede especificar un parámetro opcional como criterio de comparación, que podría tomar uno de los valores siguientes: Order.Descending, Order.Ascending

Así pues la explicación es simple.. primero descomponemos en caracteres individuales en modo Lista (con Text.ToList) para luego ser ordenados con List.Sort.
Finalmente transformamos la lista obtenida, extrayendo los valores, combinando cada elemento ordenado de esa lista...

Otra manera de trabajarlo más manual, empleando las herramientas disponibles.
En primer lugar cargamos la tabla.
Seguidamente agregamos una columna de Índice (desde 1). Dentro del editor de Power Query, en la ficha Agregar columna > grupo General > Columna Índice > Desde 1

A continuación seleccionaremos el campo 'nombres' (el que deseamos descomponer), y buscaremos en la ficha Inicio > grupo Transformar > Dividir columna > Por número de caracteres

En la ventana diálogo que se abrirá configuraremos las opciones de:
Número de caracteres: 1
Dividir: Varias veces
Avanzadas: Por filas

Esto descompone cada caracter en una fila...

Seguidamente seleccionaremos el campo Índice y presionaremos la opción de Agrupar por (ficha Inicio > grupo Transformar > Agrupar por) lo que abre la siguiente ventana de configuración donde indicaremos que l operación de agrupación es Todas las filas

En la línea de código insertada haremos un cambio:
= Table.Group(#"Dividir columna por posición", {"Índice"}, {{"Ordenados", each _, type table [nombres=nullable text, Índice=number]}})
por
= Table.Group(#"Dividir columna por posición", {"Índice"}, {{"ordenado", each [nombres]}})
Puedes ver aquí otro ejemplo y la explicación.
Un par de pasos más...

En la siguiente etapa expandimos el campo agrupado, presionando las flechitas del encabezado, eligiendo la opción
Extraer valores

En las ventanas siguientes elegiremos como Delimitador para usar en la concatenación de valores de la lista la opción 'Ninguno'
El código generado en este paso:
= Table.TransformColumns(#"Filas agrupadas", {"ordenado", each Text.Combine(List.Transform(_, Text.From)), type text})
lo cambiaremos por
= Table.TransformColumns(#"Filas agrupadas", {"ordenado", each Text.Combine(List.Transform(List.Sort(_), Text.From)), type text})
donde hemos insertado la función List.Sort para que ordene los elementos antes de unirlos de nuevo con Text.Combine.
/ Completando el proceso.

Dos maneras de conseguir nuestro objetivo ;-)

Seguir leyendo...

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...

Seguir leyendo...

Construir un Gráfico desbordado

Hemos trabajado y visto algunos trucos con las novedosas fórmulas desbordadas (para Microsoft 365), y hoy avanzaremos un poco más viendo un gráfico desbordado!!.
No te preocupes... es un gráfico estándar referenciado a un rango desbordado ;-)

Para mostrar el ejemplo nada mejor que generar un sencillo histograma sobre unos datos de ventas (con campos País, Producto y Ventas).
Para generar la tabla de rangos del histograma emplearemos la función desbordada SECUENCIA (leer algo más aquí), apoyándonos, para dinamizarlo un 'poquito' en tres celdas:
-Celda G1: longitud del intervalo
-Celda G2: inicio de los tramos (valor más bajo)
-Celda I2: la fórmula =MULTIPLO.SUPERIOR.MAT(MAX(TblVentas[Ventas]);G1)
que muestra el múltiplo superior al intervalo anterior dado en G1 del valor máximo de la base de datos.

En el paso siguiente, en la celda F6 escribimos nuestra fórmula desbordada:
=SECUENCIA(REDONDEAR.MAS((I2-G2)/G1;0);1;G2;G1)
el argumento REDONDEAR.MAS((I2-G2)/G1;0) nos devuelve el número de filas ajustado necesario para tener nuestros tramos de acuerdo a las condiciones dadas en G1, G2 y I2, esto es, dados la longitud del intervalo, inicio y fin de los rangos del histograma.
el segundo argumento (1) nos dice que la secuencia a generar tendrá una sola columna.
el tercer argumento (G2) indica cuál es el inicio o primer valor de la secuencia.
y el cuarto argumento (G1) apunta al 'paso' de la secuencia, i.e., la diferencia entre cada elemento de la secuencia.

Mientras, en G6 escribimos la fórmula desbordada:
=+F6#+G1
muy simple, ya que apoyándonos en la anterior, sumamos al elemento de cada fila obtenido el valor de G1, es decir, de la longitud definida del intervalo

Por último, en H6 añadimos la poco empleada función FRECUENCIA referenciandonos al rango desbordado anterior:
=FRECUENCIA(TblVentas[Ventas];G6#)
donde con el primer argumento indicamos el rango de datos a contabilizar; y en el segundo argumento (G6#) nos referimos al rango con los tramos... sabiendo que al primer valor de ese rango equivalen los elementos encontrados inferiores o iguales a éste; y el último los mayores a dicho valor.

Esta fórmula de FRECUENCIA se podría sustituir por:
=CONTAR.SI.CONJUNTO(TblVentas[Ventas];">"&F6#;TblVentas[Ventas];"<="&G6#)
también con un comportamiento desbordado...
OJO con las desigualdades empleadas!!!.


Vamos con la parte del gráfico desbordado.
Primero y fundamental, de otra manera no funcionaría, crearemos dos Nombres definidos con las siguientes fórmulas desbordadas referenciadas:
Desde =Hoja1!$F$6#/1000&" k- "&Hoja1!$G$6#/1000&" k"
Dtbon =DESREF(Hoja1!$H$6#;;;CONTARA(Hoja1!$H$6#)-1;1)
Con el primer nombre 'Desde' obtenemos un concatenado (tipo '1k-1,5k') basado en las diferentes filas existentes en el rango desbordado obtenido con nuestras fórmulas previas... GRAN TRUCO!!
Mientras que con el segundo 'Dtbon' conseguimos eliminar el último dato que incorpora FRECUENCIA para los valores mayores al último dato del intervalo... que sabemos, por los límites matemáticos empleados en nuestras fórmulas, será siempre cero.

Revisa la forma de crear nombres definidos en la categoría del blog
Pero basicamente accede a la ficha Fórmulas > Nombres definidos > Asignar nombre e incluye las fórmulas anteriores...

Listos para insertar el gráfico.
Selecciona el rango F5:H13... realmente no importa demasiado ya que lo cambiaremos en un paso posterior.
Desde la ficha Insertar > Gráficos > Gráficos 2-D > Columnas agrupadas

Entraremos a la edición de datos del gráfico, para lo que haremos clic derecho y buscaremos la opción Seleccionar datos... y dejaremos una sola serie de valores (no importa cuál sea, aunque mejor dejaremos 'Distribución') y una de etiquetas

En el último paso añadiremos los nombres definidos creados previamente...
Así Modificaremos la serie 'Distribución' para que tenga esta forma:
Hoja1!Dtbon

De igual forma modificaremos la serie de etiquetas con la fórmula:
Hoja1!Desde

Con estas últimas acciones nuestro gráfico ha quedado asociado al rango desbordado anterior... y cualquier cambio en nuestras condiciones iniciales (celdas G1, G2 o I2) se reflejará automáticamente en nuestro gráfico.

Seguir leyendo...

Power Query: Cargar datos de la primera hoja

Hoy veremos un excelente truco cuando debemos importar información de distintos orígenes (pongamos varios ficheros de Excel .xlsx) pero los nombres de las hojas son diferentes!!.
Pongamos tenemos tres ficheros .xlsx en una carpeta, con información a cargar en nuestra consulta... pero cada fichero contiene la información en la primera hoja con diferente nombre:
Libro1.xlsx -- hoja AAA
Libro2.xlsx -- hoja BBB
Libro3.xlsx -- hoja CCC

Fundamental que la ubicación o posición de la hoja que contiene información sea siempre la misma!
Así pues comenzamos desde la ficha Datos > grupo Obtener y transformar > desplegamos Obtener Datos > Desde un archivo > Desde una carpeta.
Por supuesto, el ejemplo que expondremos a continuación es válido para cualquier otra situación de carga.
En el asistente de carga indicamos la ruta de la carpeta en cuestión donde se ubican nuestros ficheros, en mi ejemplo: F:\excelforo\CARPETA_CSV

A continuación, tras Aceptar, se abrirá una vista previa del asistente.

Finalmente se abrirá el Editor de Power Query, desde donde presionaremos en la columna 'Content' el desplegable de 'Combinar archivos'

Aceptaremos la opción por defecto...

Como se observa nada novedoso hasta ahora... los pasos habituales dados en tantas ocasiones...
Pero en este caso comprobamos un resultado 'esperado a medias', y es que no ha habido un anexado de información de los tres ficheros... apareciendo 'Error' para los datos de los ficheros segundo y tercero!

El motivo es claro, la orden dada ha sido importar los datos de la hoja llamada 'AAA' de cualquier fichero contenido en la carpeta indicada ('F:\excelforo\CARPETA_CSV')...
Obviamente, en el segundo y tercer fichero tal hoja NO existe! :O

Podemos comprobar que ha pasado si accedemos al Editor avanzado de la consulta 'autogenerada': Transformar archivo de ejemplo

Si vemos el código M generado:

let
    Origen = Excel.Workbook(Parámetro1, null, true),
    AAA_Sheet = Origen{[Item="AAA",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(AAA_Sheet, [PromoteAllScalars=true])
in
    #"Encabezados promovidos"

Comprobamos en la segunda fila lo comentado, se menciona especificamente la hoja llamada 'AAA':
AAA_Sheet = Origen{[Item="AAA",Kind="Sheet"]}[Data],

Por fin el gran truco... pero antes debes saber que Power Query utiliza la indexación de los elementos en base 0; esto significa, para nuestro caso, que la primera hoja es la cero, la segunda es la 1, la tercera la 2, etcétera...

Si reemplazamos el nombre estático de la hoja 'AAA' por 'Origen{0}[Item]'

Quedando el código de esta consulta:

let
    Origen = Excel.Workbook(Parámetro1, null, true),
    AAA_Sheet = Origen{[Item=Origen{0}[Item],Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(AAA_Sheet, [PromoteAllScalars=true])
in
    #"Encabezados promovidos"

Tras 'Actualizar Todo' ya vemos el resultado en nuestra query final...

Finalmente cargaríamos y cerraríamos para devolver los datos de todas las primeras hojas de cada fichero existente en la carpeta indicada, sea cual sea el nombre de esa primera hoja.

OJO!!, las hojas ocultas cuentan a efectos de enumerar las posiciones!!!.
Esto implica que si hubiera una hoja oculta posicionada como 'primera hoja', cargaría lo que contuviera!! (pudiendo 'destrozar' los resultados de nuestra consulta) :(

Alternativamente a la forma descrita anteriormente...
AAA_Sheet = Origen{[Item=Origen{0}[Item],Kind="Sheet"]}[Data],
podrías igualmente emplear esta otra forma abreviada:
AAA_Sheet = Origen{0}[Data],
que es más genérica ya que recupera indistintamente lo que hubiera (Hoja - Kind="Sheet" o Tabla - Kind="Table") en la primera hoja...

Seguir leyendo...