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
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:
- Cree una lista de referencias únicas a partir de todas las entradas.
- Fusione los elementos en cada entrada con la lista única creada en el Paso 1
- Calcula las variaciones.
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
Nómina : el archivo enviado por la empresa de nómina. Valor bruto total: £4.092.099,86
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 .
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.
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 .
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).
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 .
Nuestra consulta de conciliación ahora se ve así.
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 .
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
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.
Cierre y cargue la consulta en Excel y agregue una fila total a la tabla.
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
Deja una respuesta