¿Tiene preguntas? Tenemos respuestas.

Guía de Formateo de Datos: Automatizar tareas repetitivas en Excel Power Query

  • Actualización

Resumen

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

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


Casos de uso

Si tiene muchos archivos de libro mayor del mismo software de contabilidad que están todos en el mismo formato y requieren transformación antes de la ingestión en MindBridge, entonces Excel Power Query podría ser la herramienta perfecta para usted.

Una vez que un archivo ha sido transformado a un formato ingerible y los pasos tomados han sido registrados, ¡podrá transformar cualquier archivo en el mismo formato al instante!


El problema

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

mceclip2.png

Los problemas que impiden que este archivo sea ingerido por MindBridge son:

  1. Los datos están agrupados por transacción; las columnas Trans#, Tipo, Fecha de entrada/Última modificación, Última modificación por, Fecha y Num necesitan tener sus datos poblados hacia abajo en cada fila de la transacción.
  2. Trans# no es un campo obligatorio dentro de QuickBooks, por lo tanto, no todas las transacciones tienen un Trans#.
  3. El número de cuenta y el nombre ocupan el mismo campo.
  4. Hay filas adicionales, como totales.

La solución

  1. Rellene las filas vacías con los datos relevantes de cada columna usando la función Rellenar hacia abajo.
  2. Al rellenar los datos, asegúrese de que los campos Trans# en blanco (por ejemplo, celdas B5 y B27) se mantengan y no se llenen con datos incorrectos usando una Columna personalizada.
  3. Divida el número de cuenta y el nombre en columnas separadas usando la función Dividir columna por delimitador.
  4. Filtre los espacios en blanco en una columna que contenga datos en cada fila de entrada.


Obtener y transformar datos

Para comenzar a transformar los datos, primero necesitamos importar los datos a Power Query. Hay varias formas de hacer esto dependiendo del tipo de archivo.

  1. Abra una nueva hoja de cálculo o su archivo de libro mayor en Excel.
  2. Navegue a la cinta de Datos en la parte superior. Dentro de 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 cálculo abierta o en un archivo separado.
  3. Seleccione el método apropiado para los datos que tiene. En este ejemplo, seleccionaremos Desde tabla/rango ya que nuestros datos están dentro de nuestra hoja de cálculo 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 redimensione adecuadamente en la ventana emergente Crear tabla.
    Si desea importar un archivo .txt o .csv, simplemente seleccione Desde texto/CSV y seleccione Transformar datos desde la ventana de vista previa que aparece.
    Obtener y transformar datos

  4. Ahora que los datos han sido cargados en Power Query, podemos comenzar el proceso de transformación. A lo largo de las cintas superiores hay numerosas formas de manipular los datos. En el panel derecho titulado Configuración de consulta podemos ver las Propiedades y los Pasos aplicados. En Power Query, cuando realiza una acción para manipular los datos, se crea automáticamente un paso que se puede ver en este panel. Estos pasos registrados son el principio detrás de la automatización.
  5. Podemos nombrar nuestra consulta en el cuadro Nombre. Verá que se han aplicado algunos pasos automáticamente. El paso Cambiar tipo se ha aplicado a todas las columnas; Excel está esencialmente tratando de adivinar qué datos hay en cada columna y les ha asignado un tipo.
    Necesitaremos eliminar este paso ya que queremos la opción de seleccionar tipos de datos manualmente en lugar de que se asignen automáticamente. Para hacer esto, seleccione el icono de cruz junto al paso.
    mceclip1.png

    ¡Ahora estamos listos para comenzar a manipular los datos!


Rellenar los datos usando rellenar hacia abajo

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

  • Trans#
  • Tipo
  • Fecha de entrada/Última modificación
  • Última modificación por
  • Fecha
  • Num

En este ejemplo, sabemos que la Fecha siempre está presente en la primera fila de un grupo de transacciones, sin embargo, Trans# a veces puede estar en blanco.

  1. Comience creando una columna personalizada para el número de transacción, teniendo en cuenta que necesitamos preservar las filas donde el número de transacción está en blanco. Navegue a la cinta de Agregar columna y seleccione Crear columna personalizada. Cuando aparezca la ventana de columna personalizada, la nombraremos Identificador de la operación.
    mceclip2.png

  2. if [#"Trans #"] = null and [Date] <> null then "@" else
    if [#"Trans #"] <> null and [Date] <> null then [#"Trans #"] else null

    Esta es la fórmula que usaremos para hacer nuestra columna personalizada.
    La lógica detrás de esta fórmula es verificar una fila para ver si hay datos presentes en las columnas Trans# y Fecha. Vale la pena señalar que el valor "null" representa una celda en blanco en Power Query. Si la celda del número de transacción está en blanco y la fecha no está en blanco, queremos generar un carácter especial, en este caso un símbolo "@". Si las celdas Trans# y Fecha contienen valores, entonces generamos el valor en la celda Trans#. Si ambas celdas Trans# y Fecha están en blanco, entonces generamos null.

    mceclip2.png

  3. La sección Columnas disponibles a la derecha le permite insertar nombres de columnas en el cuadro Fórmula de columna personalizada para crear fácilmente la fórmula personalizada que hace referencia a sus propios datos. El creador de columnas personalizadas detectará automáticamente cualquier error de sintaxis para ayudarle a llegar a la solución correcta. Una vez que la fórmula se haya completado, seleccione Ok para generar su nueva columna personalizada.
  4. La columna personalizada Identificador de la operación ahora ha sido creada, la cual pobló ya sea el número de transacción o un carácter especial en la primera línea de cada transacción. Haga clic derecho en el encabezado de la columna Identificador de la operación y seleccione Rellenar>Hacia abajo para llenar los valores null con los valores anteriores.
  5. Ahora podemos usar Reemplazar valores para encontrar estos caracteres especiales, en este caso "@", y reemplazarlos con null. Esto preservará esas filas con identificadores de transacción en blanco mientras se rellenan los datos necesarios.
    Fill_down.gif

  6. Con esta lógica en su lugar, ahora podemos crear columnas personalizadas para:
    • Tipo
    • Fecha de entrada/Última modificación
    • Última modificación por
  7. Podemos abrir Crear columna personalizada nuevamente y copiar y pegar la misma fórmula, sustituyendo las columnas apropiadas.


Dividir columnas por delimitador

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

  1. Haga clic 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 dará un delimitador sugerido. Si es incorrecto, puede seleccionar un delimitador diferente del menú o ingresar un delimitador personalizado. También puede seleccionar la posición y la ocurrencia de la división en la sección Dividir en.
    mceclip0.gif

  2. Seleccione OK una vez que esté satisfecho con la configuración.
    Power Query dividirá la columna en columnas separadas de número de cuenta y nombre de cuenta.
  3. Notará que Power Query intenta agregar un paso Cambiar tipo que detectará qué tipo de datos hay en las nuevas columnas e intentará asignarles automáticamente un tipo.
    Nuevamente, querrá eliminar este paso ya que puede causar problemas con los números de cuenta que tienen ceros a la izquierda.
  4. Podemos renombrar estas columnas recién creadas haciendo doble clic en el nombre asignado en el encabezado de la columna. En este ejemplo, se han nombrado ID de cuenta y Descripción de la cuenta para que coincidan con las columnas de MindBridge.


Limpiar los datos

Con los cambios principales ahora completados, podemos centrarnos en tareas más pequeñas como reorganizar las columnas, eliminar cualquier dato adicional, cambiar los tipos de datos y asegurarnos de que los datos cumplan con los estándares que permiten que se importen a MindBridge.

  1. Algunas columnas han sido reemplazadas por las columnas personalizadas creadas anteriormente en el proceso. Puede eliminar cualquiera de estas columnas haciendo clic derecho en el Encabezado de la columna y seleccionando Eliminar:
    • Trans#
    • Tipo
    • Fecha de entrada/Última modificación
    • Última modificación por
    • Fecha
    • Num
  2. Puede reorganizar las columnas según sea necesario seleccionando y arrastrando el encabezado de la columna hacia la izquierda o hacia la derecha a 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 de Fecha usen el formato Fecha
    • La columna Fecha de entrada use un formato Fecha/Hora
    • Las columnas de Crédito/Débito/Monto usen un formato Número decimal
    • Recomendamos mantener la columna Identificador de la operación como texto, ya que convertirla a un formato numérico eliminará cualquier cero precedente en el ID.
  4. Filtre los espacios en blanco en la columna ID de cuenta para asegurarse de que se eliminen las filas que contienen datos adicionales, como totales.
  5. Use la herramienta Reemplazar valores una vez más para reemplazar los valores "null" en Débitos/Créditos con "0".


Cargar datos y uso futuro

Una vez que los datos están en el formato correcto y los pasos han sido registrados, 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 poblada con los datos formateados. Ahora puede copiar y pegar los datos en un nuevo libro de trabajo e importar esto a MindBridge.

mceclip0.png

Con la consulta ahora creada, puede utilizar los pasos generados para cualquier conjunto de datos que esté en el mismo formato, es decir, que tenga los mismos nombres de columnas, posición de columnas 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 nuevos datos. Una vez que sus nuevos datos estén en su lugar, navegue a la cinta de datos y seleccione Actualizar todo.
    mceclip0.png

  2. Si utilizó Obtener datos: Desde archivo, edite la fuente de la consulta desde el panel Pasos aplicados:
    mceclip1.png


¿Algo más en mente? Chatee con nosotros o envíe una solicitud para obtener más ayuda.

¿Fue útil este artículo?