En este post voy a detallar el paso a paso de un reto que tuve que afrontar recientemente en un proyecto de consultoría.
Reto:
El objetivo era construir un cuadro de mando financiero para un cliente, para lo que tenía que capturar de la base de datos SQL Server de su ERP los datos de los movimientos del libro diario.
El cliente quería ver los datos de los últimos 5 años y en la base de datos se generaba una tabla distinta para cada empresa y ejercicio.
Al ser un modelo financiero multiempresa (había que mostrar la información de las 3 empresas del grupo), había que conectarse a 15 tablas distintas para conseguir toda la información contable necesaria (3 empresas * 5 ejercicios).
La nomenclatura de las tablas seguía el siguiente patrón: DATDIXXYY , donde XX refleja el código de la empresa (podía tener los valores 21, 22 o 24) e YY refleja los 2 últimos dígitos del ejercicio (22 para 2022, 23 para 2023 …).
Por tanto, y a modo de ejemplo, DATDI2122 sería la tabla que recoge los apuntes contables de la empresa 21 del ejercicio 2022.
Mi primer acercamiento fue hacer una consulta distinta para cada empresa y ejercicio (15 consultas distintas) y después anexarlas en Power Query.
Conseguía tener toda la información pero tenía un principal problema a resolver:
Cada cambio de ejercicio tenía que modificar el origen de las consultas. Por ejemplo, con la entrada del año 2026, tenía que añadir las tablas DATDIXX26 y eliminar las tablas DATDIXX21 (quiero tener únicamente 5 ejercicios).
Voy a explicar a continuación cómo poder hacer esa búsqueda de las tablas a cargar al modelo de forma dinámica y, además, anexar las 15 tablas dentro del mismo script de Power Query para evitarnos tener que hacer 15 consultas separadas y después anexarlas.
Solución:
A efectos de poder simplificar la explicación lo máximo posible, voy a indicar la solución paso a paso apoyándome de un fichero Excel en el que tengo creadas 15 tablas que simulan cada una de las tablas a las que nos conectaríamos a SQL.
NOTA: Te dejaré enlace de descarga al final del post con el fichero Excel donde están las tablas, un txt con el script del código M empleado, y el fichero pbix con el reto resuelto. Con una pequeña modificación del paso de Origen que te explicaré más adelante obtendrás el mismo resultado cuando cambies el origen a tablas de una BBDD de SQL Server.
Muestro en la siguiente imagen la primera de las tablas, la de la empresa 21 del ejercicio 2022, es decir, DATDI2122:
Vemos como en el nombre de la tabla hemos indicado “DATDI2122” y, para hacer la comprobación en la carga de los datos, tanto en el importe del Debe (celda B2) como en el concepto (celda D2) he hecho referencia al nombre de la tabla (2122).
NOTA: He incluido en cada tabla un único apunte contable y unas pocas columnas para visualizar mejor la información una vez que anexemos todas las tablas. En el SQL real tendremos obviamente todos los apuntes contables de la empresa y ejercicio seleccionado, además de todas las columnas que se almacenen en la base de datos.
Explicado esto, vamos a ir a Power Query para ver cómo realizamos la conexión a la información:
Pinchamos en Nuevo Origen → Libro de Excel ; y seleccionamos en nuestro equipo el fichero donde tenemos la información.
Una vez cargado nos aparecen en el navegador todas las posibles conexiones (todas las tablas y hojas).
Como se muestra en la siguiente imagen, en lugar de hacer check en las distintas tablas, hacemos click derecho sobre la parte que tiene el icono de carpeta y el nombre del fichero Excel, y pulsamos sobre “Transformar datos”:
Nos aparecen entonces todas las posibles conexiones pero dentro de una consulta de Power Query:
Si vamos al editor avanzado de Power Query, veremos que tenemos únicamente el paso de Origen:
Vamos a pegar ahora en el editor avanzado el script que te dejo en el material de descarga y después lo vamos a explicar paso a paso.
NOTA: Debes adaptar en el paso de origen del script la ruta de tu equipo donde guardes el fichero y el nombre que le des al fichero.
Quedaría el editor avanzado como muestro en la siguiente imagen:
Y si pinchamos en “Listo”, veremos cómo obtenemos el resultado deseado: traernos en una única consulta la información de los apuntes contables de las 3 empresas de los 5 años (vemos en la columna “Debe” como los importes hacen referencia a los códigos de empresa y ejercicios requeridos (XXYY que indicábamos anteriormente):
Explicación del código M:
Una vez conseguido el reto, vamos ahora a explicar cada uno de los pasos aplicados en Power Query:
Paso 1.- Origen:
Ya explicado anteriormente. En la segunda imagen del post vemos en detalle cómo llegar, y el resultado sería el de la tercera imagen.
Paso 2.- ListaEmpresas:
Creamos una lista con las empresas que queremos incorporar a nuestro modelo multiempresa (en nuestro caso las empresas 21, 22 y 24):
Paso 3.- Anio:
Nos quedamos con los 2 últimos dígitos del año actual (dinámico con el DateTime.LocalNow), que será el último que queremos incorporar a nuestro modelo. Serían las YY del nombre de las tablas a importar:
Paso 4.- ListaAnios:
Creamos una lista que va hasta el año calculado en el paso anterior (que sería el último ejercicio) y empieza 4 años antes (para traernos los últimos 5 ejercicios requeridos); y los pasamos a formato texto:
Paso 5.- FiltrarTablas:
El código completo de este paso no se ve en la siguiente imagen y es por lo que lo pego en texto a continuación:
= Table.SelectRows(Origen, each
let
Nombre = [Item],
EsTabla = [Kind] = «Table»,
EmpresaValida = List.AnyTrue(List.Transform(ListaEmpresas, (e) => Text.StartsWith(Nombre, «DATDI» & e))),
AnioValido = List.AnyTrue(List.Transform(ListaAnios, (a) => Text.EndsWith(Nombre, a)))
in
EsTabla and EmpresaValida and AnioValido
)
Lo que hacemos es seleccionar los registros que nos quedamos del primer paso (Origen), filtrando porque el nombre de la columna “Item” empiece por “DATDI” y cualquiera de los códigos de empresa de la ListaEmpresas del paso 2 (es decir, DATDI21, DATDI22 o DATDI24).
Además, el nombre de la columna “Item” debe acabar por cualquiera de los ejercicios indicados en la lista “ListaAnios” del paso 4 (22 a 26, es decir, de los ejercicios 2022 al 2026).
De forma adicional, la columna “Kind” del paso de Origen tiene que ser del tipo “Table”.
Obtenemos como resultado lo siguiente:
Paso 6.- TablasConEmpresa:
Detallamos nuevamente el código M de este paso:
= Table.AddColumn(
FiltrarTablas,
«DataConEmpresa»,
each
let
NombreTabla = [Item],
Empresa = Number.FromText(Text.Middle(NombreTabla, 5, 2))
in
Table.AddColumn(
[Data],
«id Empresa»,
each Empresa,
Int64.Type
)
)
Lo que hacemos es añadir una nueva columna a los datos que vienen en la columna “Data”. Al ser un modelo multiempresa, necesitamos añadir a los datos que nos vienen en los apuntes el código de la empresa (en mi caso esta información no venía del origen, si en tu caso ya te viene el código de la empresa en las tablas de los apuntes contables te ahorrarías este paso)..
La consulta que estamos construyendo será nuestra tabla de hechos de los movimientos contables, y esta columna del código de empresa será la que relacionaremos con la dimensión empresas para poder filtrar de qué empresa/s queremos mostrar la información.
El código de la empresa lo cogemos de la columna “Item”, con Text.Middle, empezando por la posición 5 y cogiendo 2 caracteres. Por ejemplo, en DATDI2122 nos quedamos con 21.
Además, le damos formato número entero.
Toda la información que venía en las tablas originales, más la nueva columna con el código de la empresa, quedan en la nueva columna “DataConEmpresa”:
Paso 7.- ListaTablas:
Creamos una lista con las tablas de la columna “DataConEmpresa” (posteriormente combinaremos las tablas incluidas en esta lista):
Paso 8.- TablasReducidas:
Nos quedamos únicamente con las columnas que queramos llevar a nuestra consulta definitiva.
Al conectarnos al ERP, nos pueden llegar multitud de columnas y en este paso elegiríamos las columnas que necesitemos:
Paso 9.- CombinarTodo:
Combinamos todas las tablas de la lista anterior y obtendríamos las columnas que hayamos seleccionado para todos los apuntes contables de las 15 tablas que hemos indicado.
En nuestro caso nos salen solo 15 registros porque en el Excel había únicamente 1 apunte por cada tabla, pero cuando llevemos este código a producción nos aparecerán todos los apuntes contables de las 3 empresas de los últimos 5 años, en una única consulta:
En un último paso daríamos a cada columna su tipo de dato y ya tendríamos toda la información lista para incorporar a nuestro modelo.
Origen SQL:
Si queremos cambiar el código para que nos sirva para capturar los datos de una base de datos de SQL, únicamente tendremos que modificar el código del primer paso, el de Origen, indicando lo siguiente:
Origen = Sql.Database(Servidor, BBDD),
Fin:
Y con eso ya lo tendríamos!!!
Espero que te haya resultado útil.
Este proyecto ha sido el primero en el que he usado esta técnica y la verdad es que me ha arreglado la papeleta…
Agradecer al gran Ricardo Rincón que me ayudó a encontrar esta solución. Entre su gran soporte, un poco de ChatGPT y adaptando todo a los requisitos del proyecto, llegué a esta solución que funcionó de maravilla.
Seguramente haya otras formas de llegar al objetivo o de simplificar/optimizar esta solución, pero aquí tenéis una propuesta que podéis usar en vuestros futuros proyectos 😉
Te dejo por aquí el material para que puedas replicar el paso a paso y adaptarlo a tus propias necesidades (en el código M adapta la ruta de tu equipo en el primer paso de Origen):