Utilice Power Query para crear una lista desplegable sin duplicados

En esta publicación, crearemos un menú desplegable que contiene una lista única de opciones derivadas de una columna que contiene valores duplicados. Esto puede resultarle familiar, ya que anteriormente lo logramos con una tabla dinámica. Sin embargo, la función Power Query integrada en Excel 2016 facilita este proceso.

Objetivo

Tenemos una tabla de datos que contiene las columnas RepID, Fecha y Monto, como se muestra a continuación.

En otra hoja, queremos que el usuario pueda seleccionar un representante de un menú desplegable.

Queremos que la lista desplegable contenga una lista única de representantes de la tabla.

Nuestra solución debe ser rápida y fácil de mantener a lo largo del tiempo, incluso cuando puedan aparecer nuevos representantes en la tabla de datos. No queremos usar VBA ni tener que actualizar ninguna fórmula en el futuro. Veamos cómo puede ayudar Power Query.

Pasos

Lograremos nuestro objetivo realizando los siguientes pasos:

• Crear la consulta
• Crea un nombre
• Crea el menú desplegable

Bueno, vayamos a ello.

Tenga en cuenta que los pasos a continuación están escritos con Excel 2016 para Windows. Si está utilizando una versión diferente de Excel, es posible que Power Query no esté disponible o que necesite descargar el complemento gratuito Power Query.

Crear la consulta

Primero, navegamos a la hoja de trabajo que contiene nuestra tabla de datos, que se muestra a continuación.

Luego, seleccionamos cualquier celda dentro de la tabla y hacemos clic en el siguiente comando:

• Datos De tabla (en el grupo Obtener y transformar)

Esto inicia el cuadro de diálogo Editor de consultas, como se muestra a continuación.

Los resultados que queremos devolver deben ser la columna RepID, sin duplicados, ordenada en orden ascendente. Entonces, comencemos eliminando las otras columnas.

Con la columna RepID seleccionada, hacemos clic en el siguiente comando:

• Administrar columnas Eliminar otras columnas

La consulta actualizada se muestra a continuación.

Ahora necesitamos eliminar duplicados, por lo que seleccionamos el siguiente comando:

• Reducir filas Eliminar duplicados

La consulta actualizada se muestra a continuación.

Finalmente, ordenamos la lista en orden ascendente usando el menú desplegable RepID. La consulta actualizada se muestra a continuación.

Por último, necesitamos devolver los resultados a Excel, por lo que usamos el comando Cerrar y cargar. Nuestra lista RepID única ahora aparece en una tabla en nuestro libro de trabajo, como se muestra a continuación.

Casi hemos terminado en este punto, y todo lo que queda es usar esta lista de opciones en nuestro menú desplegable.

Crea el nombre

Necesitamos crear un nombre que haga referencia a la tabla que almacena los resultados de la consulta. Hay un par de opciones para realizar este paso. Una opción es seleccionar todas las celdas de datos de la tabla y luego ingresar el nombre deseado en el cuadro de nombre (ese pequeño cuadro justo a la izquierda de la barra de fórmulas).

Otra opción es utilizar el Administrador de nombres. Para utilizar el Administrador de nombres, primero debemos anotar el nombre de la tabla. Para hacer esto, seleccione cualquier celda de la tabla y luego mire el campo Herramientas de tabla Nombre de tabla. Esto le mostrará el nombre actual y le permitirá darle un nombre diferente si lo desea. Por ejemplo, Excel asignó automáticamente el nombre Tabla1_2 a la tabla de resultados. Podríamos cambiarlo si quisiéramos, pero, suponiendo que no lo hagamos, nuestro siguiente paso es abrir el Administrador de nombres haciendo clic en el siguiente icono:

• Fórmulas Administrador de nombres

En el cuadro de diálogo Administrador de nombres, hacemos clic en el botón Nuevo para crear un nuevo nombre. Ingresamos el nombre deseado, como por ejemplo RepList . En el campo Se refiere a, ingresamos un signo igual y luego el nombre de la tabla, como =Tabla1_2 . Esto se muestra a continuación.

Hacemos clic en Aceptar y ahora que nuestro nuevo nombre está creado, podemos configurar el menú desplegable.

Crea el menú desplegable

Seleccionamos la celda de entrada que debe contener nuestro menú desplegable y luego usamos el siguiente comando:

• Datos Validación de datos

En el cuadro de diálogo Validación de datos, queremos permitir una lista. La Fuente es igual a nuestro nombre =RepList , como se muestra a continuación.

Cuando hacemos clic en Aceptar, Excel inserta el menú desplegable y ahora podemos elegir de nuestra lista de opciones.

Cada vez que actualizamos la tabla con nuevas transacciones, todo lo que tenemos que hacer es hacer clic derecho en nuestra tabla de resultados y seleccionar Actualizar. Power Query reconstruirá la tabla de resultados y los valores fluirán automáticamente a la lista desplegable.

Si tiene otras ideas divertidas de Power Query, compártalas publicando un comentario a continuación… ¡gracias!

Recursos

• Archivo de muestra: UniqueDVwithPowerQuery
• Publicación anterior para hacer esto con una tabla dinámica: https://www.excel-university.com/unique-data-validation-drop-down-from-duplicate-table-data/