Cómo enumerar todas las combinaciones posibles de múltiples listas (2 formas)

Imagine un escenario en el que tiene dos o más listas en Excel y, a partir de ahí, desea enumerar todas las combinaciones posibles en una tabla. Esto se conoce como unión cartesiana. La pregunta podría presentarse en una forma simple, como por ejemplo: “¿Cómo podemos obtener una lista completa de todas las combinaciones del Libro mayor y del Centro de costos?”. Suena simple, ¿verdad? Debe haber una única función en Excel o Power Query que genere ¿ese derecho?

Er, en realidad... No. Tendremos que involucrarnos un poco más. Profundicemos en Power Query para ver cómo podemos resolver este problema.

En esta publicación, se presentan dos soluciones. Cada una de ellas nos enseña cosas diferentes sobre Power Query, por lo que vale la pena analizar ambas y comprender las diferencias.

Tabla de contenido

• Cargue los datos en Power Query
• Opción n.º 1: fusionar tablas
• Opción #2: Trabajar con tablas
• Conclusión: enumere todas las combinaciones posibles.

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0049 Lista todas las combinaciones posibles.xlsx

Tener acceso

Cargue los datos en Power Query

Ambas soluciones de esta publicación requieren que los datos se carguen en Power Query, así que comencemos por ahí.

El archivo de ejemplo contiene 3 tablas:

• Producto
• Tamaño
• Color

Primero, necesitamos cargar las tres tablas en Power Query.

1. Seleccione una celda con la tabla.
2. Haga clic en Datos de tabla/rango
   
3. El editor de Power Query se abrirá con los datos seleccionados cargados.
   
4. A continuación, debemos cargar los datos solo como una conexión. Haga clic en Inicio Cerrar Cargar Cerrar y Cargar en….
   
   
5. Se abrirá el cuadro de diálogo Importar datos. Seleccione la opción Sólo crear conexión y luego haga clic en Aceptar .
   

Repita estos pasos para cada mesa.

Opción n.º 1: fusionar tablas

Una vez que se hayan cargado las 3 tablas, regrese al editor de Power Query haciendo clic en Datos Obtener datos (menú desplegable) Iniciar el editor de Power Query .

La primera opción utiliza la función de combinación de Power Query. Sin embargo, antes de poder usarla, hay otro paso que debemos realizar para preparar los datos.

1. Seleccione la primera consulta.
   
2. Haga clic en Agregar columna Columna personalizada en la cinta.
   
3. En el cuadro de diálogo Columna personalizada, ingrese la siguiente información:
   Nombre de nueva columna: Temp
   Fórmula de columna personalizada: =1
   El nombre personalizado y la fórmula de columna pueden tener cualquier valor que desee. Pero en este ejemplo, he usado estos valores.
   
   Luego haga clic en Aceptar.

Repita los pasos anteriores para cada consulta. Asegúrese de que el nombre de la columna personalizada y la fórmula de la columna personalizada sean idénticos en todas las consultas.

Nuestra preparación está completa, por lo que podemos pasar a fusionar las consultas.

1. Haga clic en Inicio Combinar consultas (menú desplegable) Combinar consultas como nuevas .
   
2. Se abrirá el cuadro de diálogo Fusionar.
   Seleccione dos listas separadas en cada sección.
   Haga clic en la columna Temp (o el nombre que elija) en ambas listas.
   Configure el tipo de unión para que sea completamente externo (todas las filas de ambos).
   
   Luego haga clic en Aceptar .

Ahora combine las tablas adicionales en la tabla combinada existente.

Su ventana de Power Query tendrá un aspecto similar a este.

Para crear la lista de todas las combinaciones posibles:

1. Haga clic en el botón Expandir en el encabezado de la columna.
   Desde el submenú:
   Seleccione solo la columna con los datos que deseamos conservar (es decir, en nuestro ejemplo, desmarque la columna Temp)
   Desmarque la opción Usar nombre de columna original como prefijo
   Haga clic en Aceptar
   
2. La lista de posibles combinaciones ahora aparece en la ventana de Power Query.

Si tiene más de dos consultas, continúe y expanda esas columnas también. Si está trabajando con los datos de ejemplo, la ventana Vista previa ahora se verá así:

Seleccione la columna Temp y presione la tecla Eliminar , ya que ya no la necesitamos. Finalmente, cambie el nombre de las columnas a Producto, Tamaño y Color.

Ahora tenemos nuestra lista de todas las combinaciones posibles.

Finalmente, solo necesitamos cargar esto en Excel, así que haga clic en Archivo Cerrar y Cargar (menú desplegable) Cerrar Cargar en…

En el cuadro de diálogo Importar datos, seleccione Tabla y Nueva hoja de cálculo y luego haga clic en Aceptar.

Opción #2: Trabajar con tablas

Existe una forma más sencilla, que será menos obvia a menos que tenga un poco de experiencia trabajando con tablas y listas en Power Query.

Comience con los datos ya cargados en Power Query.

1. Desde Excel, abra una consulta en blanco haciendo clic en Datos Obtener datos de otras fuentes Consulta en blanco .
2. En la barra de fórmulas escriba =Producto . (Si no tiene abierta la barra de fórmulas, haga clic en Ver barra de fórmulas en el editor de Power Query).
   
3. A continuación, haga clic en Agregar columna Columna personalizada
4. En el cuadro de diálogo Columna personalizada, ingrese lo siguiente:
   Nombre de nueva columna: Tamaño
   Columna personalizada Fórmula: =Tamaño
   Luego haga clic en Aceptar .
   
5. Agregue cualquier consulta adicional de la misma manera.
6. Expanda las tablas con el mismo método que se muestra en la opción 1.

Esto nuevamente nos dará una tabla que contiene todas las combinaciones posibles.

Ahora sólo necesitamos cargar esto en Excel. Haga clic en Archivo Cerrar y cargar (menú desplegable) Cerrar Cargar en…

En la ventana Importar datos, seleccione Tabla y Nueva hoja de cálculo y luego haga clic en Aceptar.

Pasó mucho tiempo después de que comencé a usar Power Query cuando me di cuenta de que las tablas se podían usar de esta manera. Espero que hayas aprendido algo nuevo.

Conclusión: enumere todas las combinaciones posibles.

Obtener una tabla de todas las combinaciones posibles en Excel puede no ser tan simple como queríamos, pero con suerte, esta publicación ha demostrado que podemos lograrlo con unos pocos pasos.

Solo hay una advertencia mía. De 3 listas que contienen 3 elementos, creamos 27 filas. Ahora imagina trabajar con cientos o miles de filas. Eso podría resultar en una gran cantidad de datos. Suponiendo que todas las listas son únicas, entonces se siguen los principios básicos de la multiplicación. ¡1000 filas combinadas con otras 1000 filas darían 1.000.000 de filas! Entonces esto puede volverse demasiado engorroso si tenemos muchos datos.

Artículos Relacionados:

• Introducción a Power Query
• Power Query: valor de búsqueda en otra tabla con combinación
• Cómo combinar filas en Power Query

---