Resumen

Aprenda algunos problemas de formateo comunes asociados con los archivos del libro mayor y cómo automatizar el proceso de formateo con Excel Power Query.

En este artículo utilizaremos la exportación del diario de QuickBooks Desktop, sin embargo, muchos de los pasos que se tomen serán aplicables a otras exportaciones de software de contabilidad.

Casos de uso

Si tiene muchos archivos del libro mayor del mismo software de contabilidad, que presentan el mismo formato y que necesitan ser transformados antes de la ingesta en MindBridge, entonces Excel Power Query podría ser la herramienta perfecta para usted.

Una vez que se haya transformado un archivo a un formato ingerible y se hayan grabado los pasos realizados, ¡podrá transformar cualquier archivo en el mismo formato al instante!

El problema

A continuación se muestra un ejemplo del formateo de un informe de un diario de QuickBooks Desktop, tomado de nuestra guía de extracción.

Los problemas que impiden que MindBridge procese este archivo son:

1. Los datos se agrupan por transacción; las columnas N.º de transacción, Tipo, Introducido/Última modificación, Última modificación por, Fecha y Número deben rellenarse con sus datos en cada fila de la transacción.
2. El N.º de transacción no es un campo obligatorio dentro de QuickBooks, por lo que no todas las transacciones tienen un N.º de transacción.
3. El número de cuenta y el nombre ocupan el mismo campo.
4. Hay filas extrañas, como los totales.

La solución

1. Rellene las filas vacías con los datos relevantes de cada columna utilizando la función Rellenar hacia abajo.
2. Al rellenar los datos, asegúrese de que los campos N.º de transacción que estén en blanco (por ejemplo, las celdas B5 y B27) se mantienen y no se rellenan con datos incorrectos utilizando una Columna personalizada.
3. Separe el número de cuenta y el nombre en columnas independientes mediante la funciónDividir columna por delimitador.
4. Filtre los espacios en blanco en una columna que contenga datos en cada fila de asiento.
   
   

Obtener y transformar datos

Para empezar a transformar los datos, primero tenemos que importar los datos en Power Query. Hay varias formas de hacerlo dependiendo del tipo de archivo.

1. Abra una nueva hoja de cálculo o el archivo del libro mayor en Excel.
2. Vaya a la cintaDatos que ese encuentra en la parte superior. En la sección Obtener y transformar datos, encontrará varias opciones para obtener los datos, dependiendo de si sus datos están en su hoja de trabajo abierta o en un archivo independiente.
3. Seleccione el método adecuado para los datos que tiene. En este ejemplo, seleccionaremos De tabla/rango, ya que nuestros datos están dentro de nuestra hoja de trabajo abierta. Seleccione una celda dentro de sus datos y Excel intentará detectar los datos presentes y convertirlos en una tabla. Asegúrese de que todos sus datos estén en esta tabla y cambie el tamaño de forma adecuada en la ventana emergente Crear tabla.
   Si desea importar un archivo .txt o .csv, simplemente seleccione Desde Texto/CSV y seleccioneTransformar datos en la ventana de vista previa que aparece.
   
   
   
4. Ahora que los datos se han cargado en Power Query, podemos iniciar el proceso de transformación. En las cintas superiores hay numerosas formas de manipular los datos. En el panel derecho titulado Configuración de consulta podemos ver lasPropiedadesy Pasos aplicados. En Power Query, cuando realiza una acción para manipular los datos, se crea automáticamente un paso y se puede visualizar en este panel. Estos pasos registrados son el principio responsable de la automatización.
5. Podemos nombrar nuestra consulta en el cuadro Nombre. Verá que se han aplicado algunos pasos de forma automática. El paso Cambiar tipo se ha aplicado a todas las columnas; básicamente, Excel está intentando adivinar qué datos hay en cada columna y le ha asignado un tipo.
   Tendremos que eliminar este paso, ya que preferimos seleccionar los tipos de datos de forma manual y no que se asignen de forma automática. Para ello, seleccione el icono de cruz que se encuentra al lado del paso.
   
   
   ¡Ya estamos listos para empezar a manipular los datos!
   
   

Rellenar los datos con la función de relleno hacia abajo

Podemos empezar a transformar los datos rellenando las filas vacías dentro de las siguientes columnas:

• N.º de transacción
• Tipo
• Introducido/Última modificación
• Última modificación realizada por
• Fecha
• Número

En este ejemplo, sabemos que la fecha siempre está presente en la primera fila de un grupo de transacciones, pero, a veces el N.º de transacción puede estar en blanco.

1. Comience creando una columna personalizada para el número de transacción, teniendo en cuenta que debemos conservar las filas donde el número de transacción está en blanco. Vaya a la cinta Agregar columna y seleccioneCrear columna personalizada. Cuando aparezca la ventana de la columna personalizada, le daremos el nombre de Identificador de la transacción.
   
   
   

2. si [#"Trans #"] = nulo y [Date] <> nulo, entonces "@" de lo contrario
   si [#"Trans #"] <> es nulo y [Date] es <> nulo, entonces [#"Trans #"] es nulo

   Esta es la fórmula que utilizaremos para crear nuestra columna personalizada.
   La lógica detrás de esta fórmula es comprobar si hay datos en una fila en las columnas N.º de transacción y Fecha. Cabe destacar que, en Power Query, el valor "nulo" representa una celda en blanco. Si la celda del número de transacción está en blanco y la fecha no está en blanco, necesitamos emitir un caracter especial. En este caso, el símbolo "@". Si las celdas N.º de transacción y Fecha contienen valores, entonces debemos generar el valor en la celda N.º de transacción. Si las celdas N.º de transacción y Fecha están en blanco, se debe genera un valor nulo.

   
   
   
3. La sección Columnas disponibles de la derecha le permite insertar nombres de columna en el cuadro Fórmula de columna personalizada para crear fácilmente la fórmula personalizada relativa a sus propios datos. El creador de columnas personalizado detectará automáticamente cualquier error de sintaxis para ayudarle a llegar a la solución correcta. Una vez que se haya rellenado la fórmula, seleccione Aceptar para generar su nueva columna personalizada.
4. Se ha creado la columna personalizada Identificador de la transacción que rellena la fórmula con el número de transacción o con un carácter especial en la primera línea de cada transacción. Haga clic con el botón derecho en el encabezado de la columna Identificador de la transacción y seleccione Rellenar hacia abajo >para rellenar los valores nulos con los valores anteriores.
5. Ahora podemos usar Reemplazar valores para encontrar estos caracteres especiales, en este caso "@", y reemplazarlos por nulos. Esto conservará esas filas con identificadores de transacción en blanco mientras rellena los datos necesarios.
   
   
   
6. Con esta lógica, ahora podemos crear columnas personalizadas para:
   • Tipo
   • Introducido/Última modificación
   • Última modificación realizada por
7. Podemos volver a abrir Crear columna personalizada y copiar y pegar la misma fórmula, sustituyendo las columnas apropiadas.
   
   

División de columnas por delimitador

A continuación, podemos resolver el hecho de que el número de cuenta y el nombre de cuenta ocupan la misma columna. En este ejemplo, el número y el nombre están separados por un delimitador. Con la función Dividir columnas por delimitador en la consulta de potencia podemos dividir fácilmente estos valores en dos columnas separadas.

1. Haga clic con el botón derecho en el encabezado de la columna y seleccione Dividir columna > por delimitador.
   Esto abrirá una ventana con algunas opciones. Power Query intentará detectar el delimitador automáticamente y le dará un delimitador sugerido. Si es incorrecto, puede seleccionar un delimitador diferente del menú o introducir un delimitador personalizado. También puede seleccionar la posición y la ocurrencia de la división en la sección Dividir en.
   
   
   
2. Seleccione Aceptar cuando esté conforme con la configuración.
   Power Query dividirá la columna en columnas independientes para número de cuenta y nombre de cuenta.
3. Notará que Power Query intentará añadir el paso de Cambiar tipo que detectará qué tipo de datos hay en las nuevas columnas e intentará asignarle un tipo de forma automática.
   De nuevo, es necesario eliminar este paso, ya que puede provocar problemas con los números de cuenta que tienen ceros a la izquierda.
4. Podemos cambiar el nombre de estas columnas recién creadas haciendo doble clic en el nombre asignado en el encabezado de la columna. En este ejemplo, se les ha nombrado Identificador de cuenta y Descripción de cuentapara que coincidan con las columnas MindBridge.
   
   

Limpieza de los datos

Con los cambios más importantes ya completados, podemos centrarnos en tareas más pequeñas como reorganizar las columnas, eliminar cualquier dato extraño, cambiar los tipos de datos y asegurarnos de que los datos cumplen con los estándares que permiten importarlos a MindBridge.

1. Algunas columnas se han sustituido por las columnas personalizadas creadas anteriormente durante el proceso. Puede eliminar cualquiera de estas columnas haciendo clic con el botón derecho en el Encabezado de columna y seleccionandoEliminar:
   • N.º de transacción
   • Tipo
   • Introducido/Última modificación
   • Última modificación realizada por
   • Fecha
   • Número
2. Puede reorganizar las columnas según sea necesario seleccionando y arrastrando el encabezado de columna a la izquierda o a la derecha, hasta la posición deseada.
3. Puede cambiar el tipo de datos en cada columna seleccionando el icono Tipo en cada encabezado de columna. Es importante asegurarse de que...
   • Las columnas Fecha utilizan el formato Fecha
   • La columna Fecha de entrada utiliza un formato de fecha/hora
   • Las columnas de debe/haber/cantidad utilizan un formato de número decimal
   • Recomendamos mantener la columna Identificador de la transacción como texto, ya que convertirla a un formato de número eliminará los ceros anteriores en el identificador.
4. Filtre los espacios en blanco en la columna Identificador de cuenta para asegurarse de que se eliminen las filas que contienen datos extraños, como los totales.
5. Utilice de nuevo la herramienta Reemplazar valores para reemplazar los valores “nulo” en Debe/Haber por “0”.
   
   

Carga de datos y uso futuro

Una vez que los datos estén en el formato correcto y se hayan registrado los pasos, podemos cerrar y cargar los datos en una hoja de Excel.

Seleccione Cerrar y cargar en la parte superior izquierda. Se creará una nueva hoja con una tabla rellenada con los datos formateados. Ahora puede copiar y pegar los datos en un nuevo libro de trabajo e importarlos a MindBridge.

Con la consulta ahora creada, puede utilizar los pasos generados para cualquier conjunto de datos que tenga el mismo formato, es decir, que tenga los mismos nombres de columna, posición de columna y número de columnas. Hay 2 formas de cargar un nuevo conjunto de datos en la consulta:

1. Si utilizó Obtener datos: Desde Tabla/Rango, simplemente elimine los datos antiguos de la tabla y pegue los datos nuevos. Una vez que los nuevos datos estén en su lugar, vaya a la cinta de datos y seleccione Actualizar todo.
   
   
   
2. Si utilizó Obtener datos: En Archivo, edite el origen de la consulta desde el panel Pasos aplicados:
   
   
   

¿Tiene alguna otra idea en mente? Converse en línea con nosotros o envíe una solicitud de asistencia adicional.