Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Select only one VAT for my table using Invoices XML

I would like to make an extraction of a data in a table within my Invoice XML, I explain more in detail:

I am in the extraction of Specific Data from the XML I am working on (Date, Issuer: Name, Payment Method, Total and VAT)
I have everything excellently, except the VAT part.

My client only declares normal VAT, but there is another Tax (IEPS) that appears on the invoices, which my client does not declare.
At the time of loading the XML (Extracted from 1 folder), editing the tables I need and sorting, I only have to deal with VAT (Annex photo)

Whose values appear in the table, at the time of I want only to select AMOUNT to show:

The table generates 2 problems for me mainly:
It duplicates values (Showing Values 0 in VAT and doubling TOTAL)
It shows me the IEPS in some tables (And I only NEED THE VATIVE)

The data in some tables are as follows:

And as I said, I am only interested in having the VAT of type 2 tax, or 0.160000.
Any support with that? Thank you very much in advance for the support.

1 Reply

  • freginier's avatar
    freginier
    Solution Sage
    3 months ago

    The duplication is happening because each invoice's Traslados node contains more than one tax row (one for IVA, one for IEPS), so when you expand Traslados to rows you get a row per tax and the invoice Total repeats - that's the "doubling TOTAL" and the 0 / IEPS rows you're seeing. You only want the IVA row. In the CFDI catalog, Attribute:Impuesto = 002 is IVA and 003 is IEPS.

     

    The cleanest fix is to filter to IVA before you expand, so each invoice keeps exactly one row:

     

    1) Don't expand Traslados to rows yet. Add a custom column that filters the nested tax table to IVA only:

    = Table.SelectRows([Traslados], each [#"Attribute:Impuesto"] = "002")

     

    2) Then expand only Attribute:Importe (and TasaOCuota if you need it) from that filtered column. Now each invoice contributes a single VAT amount and the Total no longer doubles.

     

    If you've already expanded to rows, the quick fix is to filter the Attribute:Impuesto column to 002 and remove 003 (IEPS) - but filtering before expanding is better because it avoids the duplicated Total rows in the first place.

     

    (Adjust the nested column name to match yours, and if your files store the tax name instead of the code, filter on "IVA" instead of "002".)