Consolidar e integrar ficheros con Power Query

Consolidar e integrar ficheros con Power Query

Todo lo que necesitas para dejar de copiar y pegar hojas y ficheros a mano. Siete tutoriales sobre cómo Power Query consolida, integra y combina datos repartidos en varios sitios, ordenados de lo más útil a lo más específico.

Los tutoriales de esta serie

Contabilidad analítica: automatiza tus repartos de costes
El caso completo de principio a fin. Repartir costes indirectos entre centros con criterios que cambian cada mes, sin rehacer el reparto a mano.

Integrar Power Query, modo difícil (La Bestia)
Cuando las fuentes no se dejan integrar: estructuras distintas, cabeceras que no coinciden y datos sucios. El caso duro.

Consolida cualquier número de hojas automáticamente
Un libro con 5 hojas o con 50, mismo esfuerzo. La consulta se adapta al número de hojas sin tocarla.

Integra datos de Excel con unos pocos clicks
La versión rápida para el caso sencillo: varias tablas con la misma estructura, unidas en dos minutos.

Crea tu modelo de datos con 1 click (VBA + Power Query)
Segunda parte de la serie de modelo de datos: automatizar la creación combinando VBA con Power Query.

Consolida ficheros en carpeta con selección dinámica
Leer todos los ficheros de una carpeta, pero eligiendo cuáles entran según un criterio, no todos a ciegas.

Combinar consultas (2023 Job Interview)
Combinar vs anexar: qué hace cada una y cuál toca en cada caso. Sale en entrevistas técnicas.

Por dónde empezar

Si vienes de consolidar hojas a mano, empieza por Consolida cualquier número de hojas. Si tu problema son ficheros sueltos en una carpeta, ve directo a Consolida ficheros en carpeta.

Dejar de copiar y pegar hojas a mano

Consolidar es el trabajo invisible de cualquier informe: cinco ficheros de sucursal, doce hojas mensuales, una carpeta que crece cada semana. Se hace a mano, se tarda una mañana, y al mes siguiente vuelta a empezar.

Power Query existe justamente para eso. Esta serie reúne siete tutoriales sobre consolidación e integración, ordenados de lo más útil a lo más específico. Aquí desarrollamos el más completo de todos, un caso de reparto de costes de principio a fin, y al final tienes el mapa para elegir por dónde seguir.

El caso completo: repartir costes indirectos entre clientes

El planteamiento es el de cualquier contabilidad analítica real. Tienes una tabla de contabilidad con cuenta, cliente, saldo, año y mes. Los ingresos vienen ya asignados por cliente. Los costes, no: están todos apilados bajo un contrato genérico. Y lo que quieres es repartir ese coste entre los clientes reales.

Para repartir hacen falta dos cosas: una base (el importe a distribuir) y un driver (el criterio que decide qué proporción se lleva cada uno). Aquí el driver son las unidades vendidas a cada cliente. Podrían ser metros cuadrados, horas imputadas o número de pedidos: la mecánica no cambia.

Primera parte: calcular los ratios

Los porcentajes se calculan por mes, así que hacen falta dos agregaciones distintas de la misma tabla de unidades:

  1. Unidades por mes — agrupar solo por mes, sumando el valor. Te da el denominador.
  2. Unidades por cliente y mes — agrupar por las dos columnas usando la opción avanzada del cuadro de agrupación. Te da el numerador.

Después se combinan las dos consultas por el campo mes. Cada fila de cliente arrastra así el total de su mes, repetido, que es exactamente lo que necesitas. Una columna personalizada con el total del cliente dividido entre el total del mes da el ratio de reparto. La comprobación de que está bien es directa: los porcentajes de cada mes suman 1.

Segunda parte: aislar la base

Se vuelve a la tabla de contabilidad, se filtra por el código de contrato genérico y quedan las líneas de coste a repartir. En tu caso puede ser un número de cuenta o cualquier marca que identifique lo que está imputado a un cajón de sastre.

Tercera parte: cruzar y generar el movimiento

Base por ratios, y cada cliente recibe su parte. El resultado se suele montar como doble movimiento: el importe positivo que entra en cada cliente y el negativo que sale del contrato genérico, de modo que el total sigue cuadrando con la contabilidad original.

El método que hace mantenible todo esto

Hay una decisión de estilo que se repite en todo el ejercicio y que vale para cualquier modelo tuyo: trabajar con referencias en vez de encadenar pasos sobre la consulta original.

Cada bloque de cálculo arranca haciendo clic derecho y Referencia sobre la consulta anterior. Suena a trámite y no lo es: cada etapa queda como una consulta con nombre propio (unidades por mes, ratios reparto, base reparto), puedes abrir cualquiera para ver qué salió mal, y si mañana cambia el origen no hay que rehacer la cadena entera, solo el punto donde se rompe.

Es la diferencia entre un modelo que otra persona puede mantener y una consulta de cuarenta pasos que solo entiende quien la escribió.

Los siete tutoriales de la serie

  • Contabilidad analítica: automatiza tus repartos de costes — el desarrollado aquí. El caso completo de principio a fin.
  • Integrar Power Query, modo difícil (La Bestia) — cuando las fuentes no se dejan integrar: estructuras distintas, cabeceras que no coinciden, datos sucios.
  • Consolida cualquier número de hojas automáticamente — 5 hojas o 50, mismo esfuerzo. La consulta se adapta sin tocarla.
  • Integra datos de Excel con unos pocos clics — la versión rápida del caso sencillo: varias tablas con la misma estructura, unidas en dos minutos.
  • Crea tu modelo de datos con 1 clic (VBA y Power Query) — automatizar la creación del modelo combinando ambas herramientas.
  • Consolida ficheros en carpeta con selección dinámica — leer todos los ficheros de una carpeta eligiendo cuáles entran según un criterio, no todos a ciegas.
  • Combinar consultas (2023 Job Interview) — combinar frente a anexar: qué hace cada una y cuál toca en cada caso. Cae en entrevistas técnicas.

Conceptos clave

  • Agrupar en modo avanzado: permite agrupar por varias columnas a la vez, que es lo que separa un total general de un total por grupo.
  • Combinar consultas: el cruce por clave que trae el total del grupo a cada fila de detalle.
  • Referencia frente a duplicado: la referencia parte de la consulta anterior manteniéndola viva; es lo que hace el modelo trazable.
  • Driver de reparto: el criterio que convierte un importe global en importes por cliente. Cambiar el driver es cambiar una consulta, no el modelo.

Por dónde empezar

Si vienes de consolidar hojas a mano, empieza por Consolida cualquier número de hojas. Si tu problema son ficheros sueltos en una carpeta, ve directo a Consolida ficheros en carpeta. Y si lo que tienes delante es un reparto de costes, el caso desarrollado aquí es el camino completo.

Más casos con estas funciones

Más contenido de Excel en InflueXcel