BUSCARV y Power Query - primera nota

Todo coacher que se precie nos conmina a "pensar fuera de la caja".Cuando de Excel se trata hay pensar fuera y dentro de la caja al mismo tiempo. O mejor dicho, en Excel pensar "fuera de la caja" es también ver qué hay "dentro de la caja", la caja de herramientas de Excel que se va enriqueciendo de versión en versión.

Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.

BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:

• la búsqueda se realiza de izquierda a derecha, lo que nos en muchos casos a reorganizar el orden de las columnas en nuestra tabla de búsqueda o a utilizar una combinación de INDICE y COINCIDIR;
• la búsqueda debe realizar en base a un único parámetro o utilizar alguna de las técnicas que muestro en el post "BUSCARV con dos parámetros" (si la dan un vistazo a la complejidad de las fórmulas, traten de imaginar una búsqueda en base a tres o más parámetros);
• si hay valores repetidos en la columna de búsqueda, BUSCARV siempre dará el primer valor encontrado ignorando los restantes.

Todas estas limitaciones pueden ser superadas con facilidad usando Power Query en lugar de BUSCARV.

Una pequeña introducción al uso de Power Query para aquellos lectores que aún no estén familiarizados con esta herramienta.(quienes esté interesados en ampliar conocimientos recomiendo este curso).

Para usar Power Query, incorporado orgánicamente a Excel desde la versión 2013 o como complemento en Excel 2010, creamos una conexión a la fuente de datos. Estos datos pueden encontrarse en el mismo cuaderno como tabla o en cualquier fuente remota (cuaderno Excel, Access, página WEB, archivo de texto, etc.). Una vez creada la conexión podemos volcar los datos en una hoja de Excel o dejarlos como "solo conexión". Esta última opción nos permite superar la limitación de 1.48 millón filas por hoja.

Vamos ahora a ver el primer caso que menciono más arriba usando tablas extraídas de la base de datos Northwind. Los dos casos restantes los veremos en los próximos posts.

Aclaración: en un caso real sólo crearíamos conexiones a la base de datos sin necesidad de cargar los datos en hojas de Excel. A los efectos del ejemplo, vamos a suponer que los datos están en tablas en hojas de Excel.

Caso 1 - Búsqueda de derecha a izquierda.

Supongamos esta tabla de productos. El identificador de categoría (Category ID) está en la columna C.

En esta tabla de categorías el identficador está en la columna B (la segunda de la tabla) y la descripción en la columna A.

Queremos agregar la descripción de la categoría a la tabla de productos. Para hacerlo con BUSCARV tendríamos que reordenar la tabla de categorías o usar una combinación de INDICE y COINCIDIR.
Con Power Query hacemos lo siguiente:

a. Creamos una conexión a la tabla de productos

En la ventana del editor de Power Query seleccionamos la opción "Guardar como..." y "Sólo conexión"

b. Hacemos lo mismo con la tabla de categorías. En el panel de conexiones veremos ambas conexiones que acabamos de crear

c. Usamos la opción Datos-Obtener Datos-Combinar consultas

En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).

Apretamos "Aceptar" y veremos que se crea una nueva consulta con la tabla Productos a los que se le agregado una nueva columna, "Categories". En realidad esta columna contiene toda la tabla Categories y la doble flecha en el encabezamiento nos permite expandirla y elegir la o las columnas de Categories que queremos traer

Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"

Apretamos "Aceptar" y aquí tenemos el resultado

Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno

Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:

• no hay necesidad de hacer cambios en la tabla de búsqueda;
• en nuestro ejemplo hay sólo 77 filas. En casos con miles o decenas de miles de filas, el uso de BUSCARV hace que el cuaderno responda lentamente con cada cambio que conlleve recalcularlo. Por ejemplo, en una hoja con 100 mil filas (caso real de un colega), cada aplicación de Autofiltro puede llevar varios minutos.

En el próximo post veremos los dos restantes casos. usar BUSCARV con más de un criterio de búsqueda y BUSCARV con valores repetidos en la tabla de búsqueda.

Cursos Excel básicos, intermedios y avanzados

Hace cerca de dos años, más precisamente desde setiembre del 2017, que no he publicado post alguno en este blog. Durante estas “vacaciones” he considerado varias veces reanudar el diálogo con mis lectores y hoy voy a aprovechar una propuesta de colaboración para hacerlo.

Iván Pinar Domínguez, ofrece en su sitio, tal vez deberíamos decir “en su academia”, una serie de cursos de los cuales hay varios que no sólo serán de interés para mis lectores, sino que también recomiendo (y me apresuro a aclarar que recibiré una comisión por cada alumno que se inscriba).

Para usuarios con conocimientos básicos e intermedios en Excel recomiendo este curso

Para los usuarios que todavía no hayan “descubierto” las nuevas herramientas de modelado de datos de Excel (Power Query, Power Pivot y otras), recomiendo este otro curso

En ambos casos usando el hipervínculo se harán acreedores a un descuento del 20%.

Para usuarios que ya se hayan embarcado en el uso del Power Pivot y el Power BI recomiendo este curso sobre el lenguaje DAX

Tabla de amortización de préstamos con Power Query

Hace varios días que venía dándole vueltas a la idea de mostrar cómo crear una tabla de amortización de préstamos, como el modelo que publiqué en el pasado,  pero usando Power Query en lugar de las funciones de Excel.
A diferencia de los ejemplos anteriores de Power Query que he mostrado en este blog, en este caso no partimos de una fuente de datos, de la hoja o remota, que cargamos a una consulta en forma de tabla. De hecho, tenemos que crear la tabla de amortización "de la nada". Es decir, una vez definidos los parámetros iniciales (monto del préstamo, tasa de interés, número de cuotas) tenemos que crear una tabla en Power Query que luego cargaremos a una hoja de Excel.
Para crear la tabla "de la nada" tendremos que usar funciones del lenguaje M (el lenguaje del Power Query). En este post no entraré en los detalles técnicos de M (objetos, funciones, etc.) y me limitaré a mostrar cómo fue construido el modelo.
Investigando las distintas funciones de Power Query llegué rápidamente a la conclusión que la función indicada era List.Generate. Lamentablemente la ayuda en la hoja de funciones de Microsoft es oscura (por lo menos para el usuario promedio). En mi ayuda vino este  post publicado por Andrew Todd en PowerPivot(Pro).

Y ahora, manos a la obra! Empezamos por definir los tres parámetros iniciales: monto del préstamo (Capital), tasa de interés anual (Interes) y el número de pagos o cuotas (Pagos). Los ingresamos en celdas de una hoja y creamos nombres definidos que se refieran a esas celdas. La forma más rapida de hacerlo es seleccionando el rango y usando Fórmulas-Nombres definidos-Crear desde la selección:

Ahora creamos una consulta para cada parámetro. Como hemos creado nombres definido Power Query no carga el rango A1:B3 a la consulta, sino sólo la celda del nombre definido

Power Query cambia el tipo de datos s Entero automáticamente. Con un clic del botón derecho abrimos el menú contextual y usamos "Rastrear desagrupando datos" (Drill down)

Vamos a Inicio, cerramos y cargamos como "sólo conexión"
Hacemos lo mismo con los otros dos parámetros

Para crear la tabla de amortización empezamos por crear una consulta en blanco (Desde otras fuentes-Consulta en blanco). En la venta del editor pegamos este código, que paso a explicar

 let  
 P=Capital,  
 i=Interes,  
 n=Pagos,  

 Mes=Table.ExpandRecordColumn(  
     Table.FromList(  
           List.Generate(  
           ()=>[Counter=1],  
           each [Counter]<=n,  
           each [Counter=[Counter]+1]  
           ),  
     Splitter.SplitByNothing(), {"Balance Inicial"},   
    null, ExtraValues.Error),"Balance Inicial", {"Counter"}, {"Mes"}),  

   BalanceInicial = Table.AddColumn(Mes, "Balance Inicial",   
     each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1),   
     type number),  

   PagoMensual = Table.AddColumn(BalanceInicial, "Pago Mensual",   
     each P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1), type number),  

   InteresMensual=Table.AddColumn(PagoMensual , "Interes Mensual",   
     each (i/12)*[Balance Inicial], type number),  

   CapitalMensual = Table.AddColumn(InteresMensual, "Capital Mensual",   
     each [Pago Mensual]-[Interes Mensual], type number),  

   BalanceFinal = Table.AddColumn(CapitalMensual, "Balance Final",   
     each [Balance Inicial]-[Capital Mensual], type number)  
 in  
   BalanceFinal  

Las primeras tres líneas crean las variables que reciben el valor de las consultas de los parámetros. Luego creamos la columna "Mes" (el número de cuota) para lo cual anidamos tres funciones de M; de adentro hacia afuera:

• List.Genetate: crea una lista (columna) con los números de mes; empieza con [Counter] = 1 y va agregando 1 hasta que el valor sea igual a n (Pagos);
• Table.FromList: convierta la lista que acabamos de generar en Tabla de manera que podamos usarla en Power Query;
• Table.ExpandRecordColumn: expandimos la tabla

A esta altura de los acontecimientos nuestra consulta ser verá así

En Power Query no disponemos de las funciones para calcular el pago fijo, el interés y la parte del capital en cada pago como tenemos en Excel (PAGO, PAGO.INT, PAGOPRIN), así que tendremos que hacer los cálculos con operadores. Por ejemplo, para calcular el balance inicial de cada período usamos

P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1)

Para crear la columna podemos usar el menú en la interfaz del usuario (Agregar Columna-Columna Personalizada) o pegar el código en el editor. Lo mismo para las demás columnas.  Al final de proceso tenemos la tabla 

La descargamos en la hoja donde pusimos los parámetros y nuestro modelo está listo 

Este modelo es absolutamente dinámico. Todo lo que hay que hacer es cambiar los parámetros y apretar Datos-Actualizar todo como puede verse en este video

El archivo del ejemplo puede descargarse aquí.