Comparar listas con Power Query

Para el analista de datos, comparar listas en Excel es una tarea frecuente. Las distintas técnicas de comparar listas en Excel que hemos visto a lo largo de la historia de este blog van desde el uso de formato condicional, pasando por la función COINCIDIR, la herramienta Datos-Consolidar hasta el uso de Access y por  supuesto usando Vba (macros).

En este post vamos a mostrar cómo usar Power Query (Excel 2010 o posterior) para la tarea. La gran ventaja de usar Power Query es que obtenemos los resultados de la comparación en una nueva lista en forma inmediata.

Supongamos que tenemos dos listas de países con sus respectivos PBI. La primer lista proviene del CIA World Factbook; la segunda de las Naciones Unidas.

Para obtener estas listas hemos usado también el Power Query (Data Catalog Search). Podemos por supuesto usar todo tipo de lista en una hoja de Excel a condición que los hayamos definido previamente como Tabla.

Una vez cargadas las listas en hojas de Excel (también podemos crear la conexión sin necesidad de cargar la lista en una hoja), podemos ver en el panel de edición del Query que las listas no tienen la misma cantidad de países

Ahora queremos saber qué países en la lista de la CIA no aparecen en la lista de las Naciones Unidas y viceversa.

Para realizar la comparación vamos a usar la herramienta Merge del Power Query.

Seleccionamos la primer lista (CIA World Factbook) y con un clic marcamos el campo en común con la segunda tabla (Country/Region); hacemos lo mismo con la segunda tabla y en la ventanilla de tipo de unión (Join Kind) elegimos "Left Anti". Por Left (izquierda) señalamos la primer tabla; de la misma manera Right (derecha) indica la segunda tabla.
Apretamos OK y obtenemos es resultado en la ventana del Query

Podemos ver que la consulta resulta en 16 países o regiones en la lista de la CIA que no aparecen en la de las Naciones Unidas. Podemos eliminar la columna "New Column" y cambiar el nombre de la consulta a algo más significativo. Apretamos Close&Load para poner la consulta en una hoja del cuaderno.
Para obtener la lista de países que aparecen en las Naciones Unidas y no en la CIA, usamos el mismo proceso cambiando el orden de las tablas en la consulta y usando el tipo de unión Left Anti

Esta consulta resulta en 9 países/regiones en la lista de las Naciones Unidas que no aparecen el la lista de la CIA

Como en el caso anterior podemos eliminar la columna "NewColumn" y pasar la consulta a una hoja del cuaderno.

Ahora tenemos una hoja con 16 países/regiones que aparecen en la lista de CIA y no en la de las Naciones Unidas y otra de 9 países/regiones que aparecen en las Naciones Unidas y no en la CIA. Esto explica la diferencia de 7 países/regiones (16-7; 218 - 211) que vimos al comparar las listas

De vuelta de las vacaciones

Tres semanas de vacaciones, sin cuestiones de trabajo, sin ocuparme del blog (mis disculpas a las tantas consultas que todavía no han recibido respuesta), sólo disfrutando de todo lo bueno que la Argentina tiene para ofrecer al turista. En particular si el turista es argentino.

Pero imposible pasar tanto tiempo sin Excel, así que acepté la gentil invitación de Rodolfo Laría (TodoExcel) para la más porteña de las actividades: tomar café y charlar.

Naturalmente la charla giró en torno a Excel. Sin profundizar tocamos varios temas como las relaciones del consultor Excel con sus clientes, las nuevas y no tan nuevas herramientas de Excel (Power Query, PowerPivot), por qué conviene tener también Access en la caja de herramientas y más.

En los próximos días volveré a publicar posts en el blog y a responder a las consultas.

JLD de vacaciones

Después de varios años de ausencia, ha llegado el momento de volver a visitar el terruño y como Octubre es el mejor mes del año para visitar Buenos Aires, ahí voy.

Desde el próximo viernes y por tres semanas estaré gozando de unas vacaciones en la Argentina. El blog estará inactivo y no contestaré consultas ni comentarios. A mi vuelta trataré de ponerme al día.

Aprovecho la oportunidad para agradecer a los lectores de este blog y a todos los que me alientan a seguir adelante con su cálido agradecimiento.

Hasta la vuelta!

Jorge