Esta entrada trata de la posibilidad de utilizar un archivo Excel con un listado de ventas del cliente para obtener un archivo válido para la importación de datos de A3.
Identificamos un archivo origen (proporcionado por el cliente) del que extraer los datos para, una vez transformados, cargar en un nuevo archivo (válido para importar).
Es una de las primeras veces en las que estoy utilizando Power Query de Excel y es importante tener en cuenta los motivos:
- Abrir un archivo Excel previamente programado será más fácil para otros usuarios que usar Access u otras BBDD como PostgreSQL o MariaDB
- Indicar a otros usuarios que el archivo de origen sea guardado con un nombre concreto en una carpeta concreta es más sencillo que importar datos
- El resultado de los datos transformados es un rango de datos nombrado, lo que facilita que tablas y gráficos dinámicos construidos sobre ellos se actualicen fácilmente
- Se puede configurar el archivo para que actualice los datos transformados y las tablas y gráficos dinámicos al abrir el archivo para mayor facilidad
En el ejemplo teníamos un listado de facturas con suplidos, cuotas al 10% y cuotas al 21%.
Necesitábamos transformar los datos para añadir: base al 10%, base al 21%, tipos de operación, tipos de IVA, cuentas contables de contrapartida
Finalmente necesitábamos para el importador de datos de A3 que cada factura estuviera presentada por diferentes líneas para cada combinación base, tipo de IVA, contrapartida que el programa agrupa identificando por fecha y número de factura
Solución inicial
- Crear una consulta de añadir columnas con los datos necesarios para suplidos, bases al 10% y bases al 21% desactivando la carga de datos
- Table.AddColumn(#»Filas filtradas», «Base», each Number.Round(([#»Total IVA 10%»]/0.10),2))
- Table.AddColumn(#»Base10″, «Cuenta», each «70000999»)
- Table.AddColumn(#»Cuenta10″, «T_IVA», each «10»)
- Table.AddColumn(#»Tipo10″, «T_Oper», each «1»)
- Crear una consulta de unión de las consultas anteriores del desglose de bases
- Table.Combine({Base10, Base21, Suplido})
Problemas adicionales y solución aplicada
Las columnas a añadir de bases de IVA se aplican sólo a facturas con cuota de IVA
- Crear un filtro al origen de datos anterior a añadir colunmas
- Table.SelectRows(#»Tipo cambiado», each ([#»Nº Factura»] <> null and ([#»Total IVA 10%»] + [#»Total IVA 21%»]) <> 0))
La suma de valores calculados no coincide con el total (posible problema de redondeo)
- Elegir un campo calculado (base al 21%) que sea la diferencia entre el Total de la factura y los otros campos calculados
- Table.AddColumn(#»Filas filtradas», «Base», each Number.Round([#»Total»]-[#»Suplidos»]-[#»Total IVA 10%»]-[#»Total IVA 21%»] – ([#»Total IVA 10%»]/0.10) ,2))
Posibilidad de bases no sujetas por exportación / prestación de servicios – entrega de bienes intracomunitarias
- Condición simultánea: Facturas con cuotas de IVA = 0 & Total – Suplidos <> 0
- Table.SelectRows(#»Tipo cambiado», each ([#»Nº Factura»] <> null and ([#»Total IVA 10%»] + [#»Total IVA 21%»]) = 0) and ([#»Total»] – [#»Suplidos»]) <> 0)
- Añadir otra consulta a la consulta unión que contemple esta posibilidad
- Table.Combine({Base10, Base21, Intra_Exp, Suplido})
Importación de datos de A3 no admite NIF de longitud mayor a 14 caracteres
- Crear un nuevo campo para el NIF en el cual si el original tenía más de 14 caracteres darlo por erróneo y dejarlo vacío
- Table.AddColumn(Filtros10, «NuevoNIF», each if Text.Length([NIF Solicitante])>14 then «» else [NIF Solicitante])
Así quedó, finalmente, la estructura de la consulta generada
Y estas son las columnas generadas en diferentes filas
Para importar en A3 luego hubo que especificar en qué columnas estaban los datos necesarios configurando una plantilla de importación de Excel
Como ventaja de este sistema está la posibilidad de crear una tabla dinámica para verificar los importes totales.
La hoja que contiene la tabla no deberá importarse.
Y el resultado es 100 % ok
