Automatizar conciliaciones con Power Query (increíble ahorro de tiempo)

Las conciliaciones son una actividad común y que requiere mucho tiempo para los profesionales de finanzas y datos. Entonces, en esta publicación, veremos cómo automatizar las conciliaciones con Power Query.

Una conciliación no es más que comparar dos datos y asegurarse de que contengan los mismos valores. Desafortunadamente, las conciliaciones pueden llevar mucho tiempo, ya que es posible que un usuario deba trabajar en cada lista y marcar los elementos que son iguales (literalmente con lápiz y papel o en la pantalla). Luego, es necesario investigar los elementos restantes que no son iguales para poder tomar medidas correctivas.

Con Power Query, podemos automatizar un proceso de conciliación. No puede investigar las diferencias, pero ciertamente puede encontrarlas en unos segundos.

Tabla de contenido
  • Métodos y principios para automatizar conciliaciones.
  • Ejemplo
  • Paso 1: cree una lista de elementos únicos
  • Paso 2: fusionar las consultas en la lista única
    • Fusionar la primera consulta
    • Fusionar la segunda consulta
  • Paso 3: Calcula las diferencias
  • Consideraciones adicionales
  • Conclusión

Descargue el archivo de ejemplo: haga clic en el botón a continuación para unirse al programa Insiders y obtener acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0184 Power Query Reconciliation.zip

Tener acceso

Métodos y principios para automatizar conciliaciones.

El objetivo de cualquier conciliación es identificar las diferencias entre dos o más entradas. Hay varias formas de lograrlo con Power Query; Estamos viendo sólo un método en este ejemplo.

Los pasos requeridos para este método son:

  1. Cree una lista de referencias únicas a partir de todas las entradas.
  2. Fusione los elementos en cada entrada con la lista única creada en el Paso 1
  3. Calcula las variaciones.
Índice
  1. Ejemplo
  2. Paso 1: cree una lista de elementos únicos
  3. Paso 2: fusionar las consultas en la lista única
    1. Fusionar la primera consulta
    2. Fusionar la segunda consulta
  4. Paso 3: Calcula las diferencias
  5. Consideraciones adicionales
  6. Conclusión

Ejemplo

Para este ejemplo, utilizamos datos de nómina.

Supongamos que enviamos un cronograma de salarios a nuestra empresa de nómina externa. Calcularon los impuestos y el pago neto y nos devolvieron los valores para su aprobación.

Sin embargo, el total de la empresa de nómina no coincide con el cronograma que les enviamos. Realicemos la conciliación para identificar las diferencias.

Tenemos 2 entradas en nuestros archivos de ejemplo:

Listado : el archivo enviado a la empresa de nómina. Valor bruto total: £4.092.558,19

Información de listado

Nómina : el archivo enviado por la empresa de nómina. Valor bruto total: £4.092.099,86

Información de nómina

Hay una diferencia de £458,33 entre los dos horarios. Necesitamos encontrar y corregir estas diferencias.

CONSEJO:

Para nuestro archivo de ejemplo, ambas son tablas de una hoja de trabajo que se cargaron en Power Query. Sin embargo, en realidad, nos conectaríamos directamente a los archivos y crearíamos la conciliación sin copiar ningún dato en el libro. Esto también permite volver a realizar la conciliación simplemente haciendo clic en Actualizar datos todos .

Paso 1: cree una lista de elementos únicos

Comencemos por crear una lista de elementos únicos.

En nuestro ejemplo, las columnas de número y nombre de empleado no tienen los mismos nombres en ambos conjuntos de datos.

  • Listado : las columnas se llaman Ref y Nombre del empleado
  • Nómina – las columnas se llaman Número y Nombre

La primera acción es cambiar el nombre de las columnas para asegurarse de que sean idénticas.

  • Listado : cambie el nombre de la columna Nombre del empleado a Nombre
  • Nómina : cambie el nombre de la columna Número a Ref.

CONSEJO:

Técnicamente, sólo necesitamos el número de referencia para completar el proceso de conciliación. Sin embargo, conservar el nombre o la descripción suele ser útil, por lo que tenemos una forma fácil de consultar cada registro.

En la cinta, haga clic en Inicio Agregar consultas (menú desplegable) Agregar consultas como nuevas .

En el cuadro de diálogo Agregar consultas , incluya las consultas de Listado y Nómina y luego haga clic en Aceptar .

Agregar cuadro de diálogo

La consulta se llamará Append1 de forma predeterminada. Entonces, cambiemos el nombre de la consulta a Reconciliación .

Ahora, necesitamos conservar sólo las columnas de referencia. Seleccione las columnas Ref y Nombre y haga clic en Inicio Quitar columnas (menú desplegable) Quitar otras columnas .

Finalmente, seleccione la columna Ref y haga clic en Inicio Eliminar filas Eliminar duplicados .

Ahora tenemos una lista única de números de empleados y sus nombres.

Lista única de empleados

Como la lista única se crea a partir de ambas entradas, sabemos que está completa. Por lo tanto, las diferencias conciliatorias deben constar en estos registros.

Paso 2: fusionar las consultas en la lista única

Ahora podemos fusionar las consultas individuales nuevamente en la lista única.

Fusionar la primera consulta

Con la consulta de conciliación seleccionada, haga clic en Inicio Consultas de combinación .

En la ventana Combinar , seleccione la consulta de Listado como segunda Tabla, luego haga clic en la columna Ref (que es el identificador único) en ambas Tablas. Asegúrese de que el tipo de unión sea Left Outer . Luego haga clic en Aceptar .

Cuadro de diálogo Fusionar: agregar valores a la conciliación

El mensaje en la parte inferior de la ventana de combinación nos dice que faltan 3 registros en la lista única en la consulta de listado.

Haga clic en la flecha de doble punta en la parte superior de la columna Listado e incluya solo la columna Bruto (es decir, la columna que estamos conciliando).

Expandir columna - Listado

Dado que la columna se llama Bruto en ambas consultas, he conservado el nombre de la consulta original como prefijo para que sea fácil identificar de dónde se originó cada columna.

Los 3 valores faltantes se mostrarán nulos en la columna Bruto. Sin embargo, no podemos calcular con un valor nulo, por lo que debemos reemplazar los valores nulos por ceros.

Seleccione la nueva columna Listing.Gross . En la cinta, seleccione Transformar Reemplazar valores .

En el cuadro de diálogo Reemplazar valores , ingrese lo siguiente

  • Valor a encontrar: nulo
  • Reemplazar con: 0
  • Haga clic en Aceptar .

Cuadro de diálogo Reemplazar valores

Nuestra consulta de conciliación ahora se ve así.

Conciliación con Power Query: lista única con bruto

Fusionar la segunda consulta

Para fusionar los datos de la segunda consulta, repita los pasos anteriores utilizando la consulta de Nómina .

Ahora tenemos una consulta de Conciliación con los datos de las consultas de Nómina y Listado .

Conciliación automática de ambas tablas fusionadas

Paso 3: Calcula las diferencias

A continuación, debemos calcular las diferencias.

En la cinta, haga clic en Agregar columna Columna personalizada .

En el cuadro de diálogo Columna personalizada , ingrese lo siguiente:

  • Nuevo nombre de columna: Variación
  • Fórmula de columna personalizada: =[Listing.Bross]-[Payroll.Bross]
  • Haga clic en Aceptar

Cálculo de columna personalizada

Cambie el tipo de datos de la columna Varianza a Número decimal.

Finalmente, filtre la columna Varianza para eliminar 0 (es decir, los elementos que coinciden exactamente).

Ahora tenemos una lista de las diferencias entre ambos horarios.

Conciliación automática con Power Query

Cierre y cargue la consulta en Excel y agregue una fila total a la tabla.

Tabla de Excel automatizar conciliaciones

Nuestra conciliación muestra una variación de £458,33 , precisamente el valor que queríamos encontrar.

Ahora podemos investigar por qué existe esa diferencia y emprender las acciones correctivas necesarias.

Consideraciones adicionales

Para utilizar esta técnica con éxito, hay algunos elementos que se deben tener en cuenta.

  • Debe haber una referencia compartida entre las entradas. Si es necesario, podemos combinar varias columnas para crear una referencia única. Sin embargo, donde no existe un referente compartido; entonces no hay nada que Power Query pueda usar.
  • Cuando haya varias instancias de una referencia en una tabla, utilice la transformación Agrupar por con el operador Suma antes de crear la lista única y fusionar las consultas. Esto genera una consulta resumida que solo incluye una única instancia de cada número de referencia.
  • Cuando las referencias contienen solo números, Power Query puede tratarlos como un tipo de datos de número entero. Recomiendo cambiar a un tipo de datos de texto para garantizar que se mantengan los ceros a la izquierda.

Conclusión

Power Query es una herramienta valiosa para automatizar conciliaciones. Podemos conectarnos fácilmente a fuentes de datos y comparar valores.

Para actualizar la conciliación de datos nuevos, solo necesitamos guardar los datos en la misma ubicación y luego hacer clic en Actualizar datos todos .

Artículos Relacionados:

  • Power Query: valor de búsqueda en otra tabla con combinación
  • Anexar Power Query: combine rápidamente muchas consultas en 1
  • Cómo utilizar Power Query Group By para resumir datos

Este sitio utiliza cookies para una mejor experiencia - Mas información