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
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
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
No sé cuántos de mis lectores usan o conocen la opción "Utilice una fuente de datos externa" para crear tablas dinámicas
Cuando no existe la necesidad de hacer transformaciones a los datos originales, esta opción es muy conveniente (cuando necesitamos transformar datos Power Query es la herramienta más apropiada).
La idea del post surgió a partir de la consulta de un colega en el trabajo. Su problema: tenía que construir un reporte con una tabla dinámica, pero la tabla con los datos de origen tenía varios cientos de miles de filas, número que iba incrementándose cada mes. A corto plazo la base de datos superaría el millón de filas, más allá de la capacidad de una hoja de Excel.
Inmediatamente comprendí que mi colega importaba los registros (filas) de la base de datos a una hoja de Excel, lo cual es innecesario.
Las ventajas de crear una conexión sin importar físicamente los datos a una hoja de Excel son evidentes:
evitamos duplicar los datos (también en a base de datos y también en la hoja);
nuestro archivo será mucha más liviano;
cuando hay cambios en la base de datos (nuevos registros, por ejemplo) basta con apretar el botón Actualizar para refrescar los datos;
podemos desconectar las tablas dinámicas creadas del origen y así proteger datos discretos. Sobre este tema pondré un ejemplo al final del post.
Para crear una tabla dinámica con esta técnica empezamos con una hoja vacía. Abrimos el menú Insertar-Tabla dinámica-Utilice una fuente..." y apretamos el botón "Elegir conexión".
Si la conexión que buscamos no aparece en la lista de conexiones disponibles, usamos el botón "Examinar en busca de más" (en la parte inferior izquierda del formulario). En este ejemplo voy a crear una conexión a la base de datos Northwind que puede estar en mi máquina, en la red a la cual estoy conectado o en la Web. Al apretar el botón aceptar veremos l plantilla de la tabla dinámica vacía y la lista de campos que podemos utilizar para crear el reporte dinámico
A partir de aquí podemos crear nuestros reportes sin que los datos de base estén físicamente en la hoja. Por ejemplo, este reporte de ventas donde usamos el campe de Filtro para filtrar los informes por vendedor
Una de las ventajas, como señalamos, es que nuestro archivo será más liviano. El archivo de este ejemplo peso 132 KB. Si hubiéramos importado los datos a una hoja del cuaderno pesaría 427 KB, tres veces más.
La otra ventaja de este método surge cuando trabajamos con datos que requieren discreción. Siguiendo con nuestro ejemplo supongamos que generamos un reporte para cada agente de ventas (que hacemos con facilidad con la técnica que muestro en este post) pero no queremos que puedan ver los resultados de los otros colegas.
Una posibilidad es copiar y pegar los datos en otra hoja con la opción "sólo valores". Pero esta técnica tiene dos inconvenientes:
es trabajosa si se trata de muchas hojas (agentes en nuestro ejemplo);
al pegar los datos los formatos de la tabla desaparecen (podemos usar el tip que muestro en este post, pero también es trabajoso).
Si creamos los reportes dinámicos en base a una tabla dinámica "conectada", todo lo que tenemos que hacer es cancelar al conexión
Excel transforma la tabla dinámica en una matriz de valores, sin fórmulas, sin conexiones y manteniendo los formatos. Veamos el proceso en este video
