Coincidencia difusa con Power Query

Hace unos años, escribí una publicación sobre cómo realizar búsquedas difusas utilizando el “Complemento de búsqueda difusa para Excel”. Sin embargo, esta capacidad ahora está disponible en Power Query. ¿Esperar lo? ¡Si! Realizar una coincidencia aproximada es mucho más fácil e intuitivo en Power Query. Esta publicación explica los conceptos básicos… y muchas GRACIAS a los desarrolladores de Microsoft por esta función.

Objetivo

Antes de pasar a la mecánica, retrocedamos y comprendamos qué estamos tratando de hacer y qué es una coincidencia difusa.

En términos generales, las funciones de búsqueda de Excel esperan que los valores de búsqueda coincidan en ambas listas. El caso puede ser diferente (por ejemplo, “Microsoft” y “microsoft” coincidirían), pero en general, la idea es que las cadenas de texto deben ser las mismas. Hay algunas excepciones a esto, como se indica a continuación, pero en general, la idea es que los valores de búsqueda sean los mismos en ambas listas.

Cuando los valores de búsqueda son diferentes, por ejemplo ABC Company y ABC Company, Inc. , las funciones de búsqueda tradicionales no siempre son confiables. Ahora bien, para nosotros los humanos, estos dos nombres representan la misma empresa subyacente. Pero para Excel, estos representan diferentes valores de búsqueda. La idea con una búsqueda difusa es permitir que Excel las relaciones.

Por ejemplo, aquí hay una tabla (llamada CompanyName ) con algunos nombres de empresas:

Nos gustaría que Excel los relacionara con esta tabla (llamada CompanyList ):

Notarás que hay algunas diferencias: ABC Company vs ABC Company, Inc. , Micro soft vs Microsoft y Xeon vs Xeon Products .

Con una función de búsqueda tradicional como BUSCARV, no coincide:

Con la opción de combinación difusa de Power Query, haz lo siguiente:

Ahora, vayamos a la mecánica.

Nota: algunas funciones de búsqueda ofrecen una opción para una coincidencia “aproximada”. Por ejemplo, cuando configura el cuarto argumento de BUSCARV en VERDADERO, o el tercer argumento de COINCIDIR en 1 o -1, nos permite realizar búsquedas de rango. Y, dependiendo del conjunto de datos, los valores de búsqueda y el orden de clasificación, esto puede proporcionar o no la coincidencia aproximada esperada deseada. Además, algunas funciones de búsqueda admiten el uso de un comodín, lo que también ha ayudado en algunas situaciones. Pero ahora podemos usar la opción de combinación difusa incorporada en Power Query para realizar una verdadera coincidencia difusa

Detalles

Recorreremos este proceso en tres pasos:

• Lista de importación 1
• Lista de importación 2
• fusión difusa

Hagamos esto.

Nota: dependiendo de cuándo esté leyendo esto y de su versión de Excel, es posible que no tenga la opción de combinación difusa, que se lanzó recientemente a través de la suscripción a O365 (canal de actualización de Office Insiders).

Lista de importación 1

Comenzamos obteniendo nuestra primera lista (Nombre de la empresa) en Power Query seleccionando la tabla (u otra fuente de datos) y usando el comando Datos Desde tabla/rango . Se muestra una vista previa en el Editor de Power Query:

A continuación, vamos a Inicio Cerrar y cargar en… una consulta de conexión únicamente … y no es necesario cargarla en el modelo de datos :

Ahora es el momento de obtener nuestra segunda lista.

Lista de importación 2

Básicamente enjuagamos y repetimos para importar la tabla de búsqueda (CompanyList). Nuevamente cerramos y cargamos en… y creamos una consulta de solo conexión.

En este punto, deberíamos ver ambas consultas enumeradas en el panel Consultas y conexiones en el lado derecho de la ventana de Excel:

Con nuestra primera y segunda tablas en Power Query, ahora podemos realizar la combinación difusa.

fusión difusa

Para iniciar este paso, desde Excel seleccionamos Datos Obtener datos Combinar consultas Fusionar . Aparece el cuadro de diálogo Combinar y seleccionamos nuestra primera lista (CompanyName) y nuestra segunda lista (CompanyList). También identificamos las columnas de búsqueda haciendo clic en el encabezado de la columna de ambas, como se muestra a continuación:

Notarás que la parte inferior de la captura de pantalla dice: “La selección coincide con 0 de 3 filas de la primera tabla”. Esto se debe a que ABC Company no es lo mismo que ABC Company, Inc. , etc.

Pero… y aquí está lo bueno… notará una casilla de verificación de llamada Usar coincidencia aproximada para realizar la combinación.

Compruébalo y… ¡interesante! Ahora tenemos 2 de 3 filas que coinciden:

¡Guau! Ahora veamos qué elementos coinciden haciendo clic en Aceptar.

Nota: no se preocupe, exploraremos las opciones de fusión difusa en breve.

De vuelta en el Editor de Power Query, expandimos el campo Tabla CompanyList y podemos ver las coincidencias a continuación:

Nuestras dos primeras empresas coinciden… ¡increíble!

¿Podemos conseguir que nuestra tercera empresa, Xeon, también coincida? Es hora de explorar algunas opciones de fusión difusa. Haga clic en el ícono de ajustes en el paso Fuente aplicada y expanda las opciones de Fusión difusa:

• El campo Umbral de similitud nos permite especificar qué tan similares deben ser los dos valores para hacer una coincidencia. El rango es de 0 a 1 y puede configurarlo como desee según sus datos.
• El caso Ignorar está marcado de forma predeterminada (por lo que MICROSOFT coincidirá con Microsoft).
• La opción Coincidir combinando partes de texto está marcada de forma predeterminada (por lo que Micro soft coincidiría con Microsoft).
• El número máximo de coincidencias nos permite establecer cuántas coincidencias se devuelven para cada valor de búsqueda.
• La tabla de Transformación nos permite especificar una “tabla de mapeo” separada que aumenta la confiabilidad de las búsquedas que realizamos con frecuencia (más sobre esto a continuación).

Cambiemos el umbral de similitud a 0,5 y bam… 3 de 3 elementos ahora coinciden:

Damos clic en Aceptar y… sí… ¡funcionó!

Cerramos y cargamos en… una tabla, y los resultados ahora están nuevamente en Excel:

¡Y eso, amigos míos, es una búsqueda aproximada en Excel!

Ahora, una última nota. Si realizamos una búsqueda similar en listas similares una y otra vez, podemos aumentar la confiabilidad de nuestra coincidencia incorporando una tabla de “mapeo” de transformación.

Tabla de transformación

Para crear una tabla de transformación, cree una nueva tabla con una columna Desde y Hasta , como se muestra a continuación:

Luego, lo ingresa en Power Query ( Datos De tabla/rango ) y guarda la consulta como una consulta de solo conexión como lo hicimos anteriormente.

Ahora, abrimos el cuadro de diálogo Combinar editando la consulta de combinación difusa (haga doble clic en la consulta de combinación en el panel Consultas y conexiones ). Una vez más, haga clic en el ícono de ajustes en el paso Fuente aplicada. Expanda las opciones de combinación difusa y seleccione su tabla en el menú desplegable Tabla de transformación:

Piense en esta tabla como si proporcionará una coincidencia confiable para diferencias conocidas y, si no se puede encontrar un valor en ella, Power Query usa la lógica de coincidencia aproximada.

Bueno, para mí, esta es una capacidad muy interesante de Power Query, ya que resuelve un problema común que históricamente ha sido difícil de resolver. Ahora que está integrado en Power Query, es fácil de usar y realmente será de ayuda en la práctica. Nuevamente, gracias Microsoft por este regalo.

Archivo de muestra: FuzzyPQ.xlsx