Sin embargo, hasta hace un tiempo atrás (en realidad casi un año) Combinar Consultas presentaba una desventaja frente a BUSCARV: en Power Query las coincidencias tenían que ser exactas mientras que en BUSCARV tenemos también la posibilidad de búsqueda aproximada.
Hace unos meses atrás Microsoft introdujo la posibilidad de combinar consultas en Power Query con coincidencias aproximadas.
En este post voy a mostrar como utilizar esta nueva posibilidad (y también los peligros que implica y donde no podemos utilizarla).
Empezaremos con un ejemplo sencillo. Tenemos una tabla de empleados con sus fechas de nacimiento y una segunda lista con los nombres de los empleados. Queremos combinar ambas tablas para que las fechas de nacimiento aparezcan en la segunda tabla.
En la tabla de la izquierda los empleados aparecen por apellido y luego nombre; en la tabla de la derecha por nombre propio y luego apellido. Además hay diferencias como "Bob King" en lugar de "King Robert" o "Nancy C. Davolio" en lugar de "Davolio Nancy".
Creamos conexiones a ambas tablas y al combinarlas vemos que obtenemos 0 coincidencias
Chequeamos la opción Use las coincidencias aproximadas para comparar... y vemos que ahora tendremos 8 coincidencias.
Abriendo las Opciones de combinación parcial vemos lo siguiente
De todas la opciones la clave aquí es Umbral de similitud.
Umbral de similitud: es un número entre 0 y 1 que indica el nivel de similitud necesario para que dos valores coincidan. Un valor de 1 exige coincidencia exacta y el valor 0 hará que todos los valores coincidan los unos con los otros. Si dejamos la casilla en blanco el nivel de similitud es 0.80.
Para ver como funciona el umbral de similitud veamos que pasa si le ponemos el valor 1
Vemos solamente tres coincidencias. Coincidencia en este caso significa que todas las palabras coinciden sin importar el orden (nombre propio-apellido o apellido-nombre propio).
A medida que bajamos el valor del umbral de similitud tendremos más coincidencias. Por ejemplo con el valor por defecto de 0.80 nos queda solo una fila sin coincidir.
Si fijamos el umbral de similitud en 0 (o cualquier valor suficientemente bajo) tendremos una coincidencia de 9 sobre 9
Qué bueno, ¿no? Pues bien, no. Hay situaciones en las cuales debemos prestar atención para evitar resultados erróneos. Veamos el siguiente ejemplo.
Supongamos una lista de la población de 20 países
y una segunda lista con las superficies de todos los países (más de 190 filas). Queremos combinar la primer lista con la segunda para calcular la densidad de la población de los 20 países de la primer lista (población dividida por superficie).
Después de crear las conexiones a ambas listas usamos Combinar y vemos que hay solamente 11 coincidencias sobre 20
Expandimos la tabla que acabamos de combinar para ver que países nos faltan (todos los valores null en la columna [Pais.1]
12 sobre 20, una pequeña mejora. Pero al expandirlo vemos varias cosas extrañas
La tabla tiene 27 filas en lugar de las 20 que tendría que tener; algunos países aparecen repetidos (por ejemplo: China, Tanzania, Paraguay); Congo coincide con Togo y también con Tonga; China tiene tres coincidencias (con Ghana, con Chile y consigo misma).
Lo que sucede es que al bajar e umbral de similitud China y Chile tiene suficientes letras en común como para crear la coincidencia.
Para superar este problema vamos a usar otra opción de la coincidencia parcial: Número máximo de coincidencias
Fijamos el parámetro en 1 y expandimos la tabla
Bien, volvemos a las 20 filas originales. Nos faltan 8 coincidencias, pero fijémonos en la fila 1: ¡Congo coincide con Togo!
Dejando el número máximo de coincidencias en 1 bajamos el umbral de similitud a 0.5 y obtendremos 17 coincidencias sobre 20
Aquí ya estaríamos tentados a bajar el umbral de similitud a 0. Si lo hacen obtendrán, en este ejemplo, 19 coincidencias sobre 20 (Myanmar quedará sin "pareja"), pero ésto no es recomendable. Togo seguirá coincidiendo con Congo y este error puede pasar desapercibido (en la tabla final sólo aparecerán los países de la primer columna; la columna [Pais.1] la he agregado para demostrar el ejemplo).
Para solucionar estos problemas tenemos otra opción en Coincidencias aproximadas: Tabla de transformación
La Tabla de transformación tiene dos columnas: From y To (los encabezados tiene que estar en inglés) y lo que hace es asignar valores correspondientes. Por ejemplo, Ivory Coast y Côte d'Ivoire.
Creamos la tabla y la guardamos como consulta.
En la casilla Tabla de transformación se abre una lista desplegable donde podemos elegir la conexión a la tabla de correspondencias
Ahora al expandir obtenemos una tabla con las 20 filas requeridas y coincidencias exactas
Todo lo que nos queda por hacer es eliminar la columna [Pais.1] que usamos como control y agregar la columna [Densidad] dividiendo la población por la superficie.
El uso de Coincidencias aproximadas en operaciones de combinación de tablas es una mejora notable pero conlleva varios riesgos, como hemos mostrado en este post.
No podemos usar coincidencias aproximadas con valores numéricos, sólo con texto.
Buenos días Jorge, me has dejado con la miel en los labios, esta opción no me aparece en el powerquery y he visto que no estará disponible hasta octubre al menos.
ResponderBorrarsaludos
Ya veo, lo lamento. Uso la versión 365 (por suscripción) que se actualiza permanentemente. Me he fijado en la última actualización de PQ para XL2010 y veo que todavía no han agregado esta opción. Tampoco en Excel 2016 Professional (en uso en algunas de las compañías para las cuales trabajo).
BorrarEstimado Jorge, acabo de ver que la opción SI que esta disponible en Power BI Desktop. Resulta curioso pero es así, un abrazo Jorge.
Borrar