Hoe importeer je Auditfiles (xaf) bestanden in Power BI?

Auditfiles, ook bekend als XAF-bestanden, zijn een vertrouwd bestandsformaat voor veel financiële professionals. Ze zijn gebaseerd op XML en bevatten de meest gebruikte gegevens van een boekhouding. Het importeren van gegevens uit een XAF-bestand naar een Power BI-rapport is redelijk eenvoudig.

Wat zijn XAF bestanden?

XAF staat voor “XML auditfile Financieel”. Deze open standaard is in 2003 geïntroduceerd door de belastingdienst en wordt sindsdien onderhouden in samenwerking met accountants.

XAF-bestanden zorgen ervoor dat alle gegevens volgens een uniforme standaard worden opgeslagen. Kolommen hebben altijd dezelfde naam en de structuur van een auditfile is consistent. Ongeacht het boekhoudprogramma waaruit het bestand afkomstig is.

Hierdoor zijn deze bestanden handig voor het delen van boekhoudgegevens met de belastingdienst of accountants. De meeste moderne boekhoudsoftware (bijv.  Exact, AFAS, E-boekhouden, Microsoft Dynamics) biedt de mogelijkheid om XAF-bestanden te genereren.

Dit maakt ze ook uitermate geschikt voor het gebruik van boekhoudgegevens in Power BI. Vooral wanneer directe gegevensverbindingen niet beschikbaar zijn.

Wil je het zelf proberen maar heb je geen auditfile? Hier kun je zelf een XAF voorbeeldbestand downloaden.

Hoe importeer je XAF auditfiles in Power BI?

Auditfiles hebben een XML-structuur. Dus je kunt ze importeren door naar het “Gegevens ophalen” (Get Data) menu in Power BI Desktop te gaan en te kiezen voor XML.

Klik op “Connect” en gebruik het verkennervenster dat verschijnt om naar de map met XAF-bestanden te navigeren.

Let op: De verkenner zoekt standaard naar bestanden met de extensie “.xml”, maar je wilt juist een bestand met de extensie “.xaf” importeren. Verander daarom de extensiefilter in het verkennervenster naar “alle bestanden” (all files), selecteer de auditfile en klik op “Openen”.

In het navigatorvenster kies je vervolgens voor de tabel ‘Company’ en klik je op ‘Transform data’. Power Query wordt nu geopend met een query naar het XAF bestand.

Power Query stappen

In eerste instantie lijkt Power Query slechts één record te bevatten. Maar XML bestanden zijn een vorm van semigestructureerde gegevens. Waarbij records weer vertakkingen kunnen hebben die andere records bevatten. Power Query ziet dit als een enkele record, dat voor ieder lager niveau ook weer tabellen bevat. Die tabellen kunnen dan ook weer tabellen bevatten.

Let op: Wat je hier absoluut niet moet doen is alle tabellen openklappen. Omdat XML geen echte relaties bevat zouden dan bijvoorbeeld alle grootboekrekeningen gecombineerd worden met alle transacties. Iedere transactie voor iedere grootboekrekening dus. Dit geeft uiteraard compleet onbruikbare data. En zal waarschijnlijk enorm groot worden.

Sowieso is het altijd het beste om een ster schema te hebben. Die kan je uit het XML bestand halen met een paar stappen in Power Query.

Stap 1
Als eerste zorg je dat de ‘Company’ query niet geladen wordt. Dit doe je door deze met de rechtermuisknop aan te klikken en het vinkje bij ‘enable load’ uit te zetten.

Je kunt ook deze query aan het eind verwijderen.

Stap 2
Voor elke tabel haal je de gegevens uit de basisquery. In dit voorbeeld beperk ik me tot de grootboekrekeningen en de transacties, maar je kunt op dezelfde manier andere tabellen aanmaken. Selecteer hier eerst de kolom ‘GeneralLedger’. Klik erop met de rechtermuisknop op en kies voor ‘Add as New Query’.

Er ontstaat nu een nieuwe query, die in eerste instantie de vorm van een zogenaamde ‘list’ heeft die een tabel bevat. Klik hier op de ‘table’ tekst in de lijst. Er ontstaat een tabel met een ‘cel’.

Er wordt nu een nieuwe query gemaakt. Die in eerste instantie de vorm heeft van een ‘list’ met een tabel erin. Klik op de tekst ’table’ in de lijst. Er wordt een tabel met een cel weergegeven. Vouw de tabel uit door op de knop rechtsboven in de kolom te klikken en selecteer de benodigde kolommen. De optie ‘Use original column name as prefix ‘ voegt niet veel toe, dus zet die uit.

Het eindresultaat is een query die de grootboekrekeningen importeert uit het auditfile.

Stap 3
Ga terug naar de ‘company’ query, selecteer de kolom ’transactions’ en klik op ‘Add as new Query’.

Klik in de nieuwe query op de tabel. Je wilt alleen de kolom ‘journal’ gebruiken, dus verwijder de andere kolommen. Vouw de ‘journal’-kolom uit door op de knop rechtsboven in de kolom te klikken. In dit geval wil je alleen de kolommen ’transaction’ en ‘jrnID’ behouden.

TIP: Als je ook de journaalposten wil hebben dan kun je die toevoegen met een nieuwe query op dezelfde manier. Waarbij je alleen de eerste 3 kolommen selecteert. Je kan dan via de ‘jrnID’ kolommen een relatie maken in het datamodel.

Klap hierna de transaction kolom open met in ieder geval de kolom ‘trLine’. Klap ook deze kolom open. Je hebt nu de tabel met de transactieregels. Maar er zijn nog een enkele aanpassingen nodig om hem bruikbaar te maken.

Stap 4
Zoals je kunt zien in de kolom ‘amnt’ (bedrag), gebruikt het XAF-bestand een punt als decimaalteken. Dit kan problemen veroorzaken op Europese computers. Dus gebruik de optie “Waarde vervangen” (Replace Value) om punten door komma’s te vervangen en pas de gegevenstypen aan.

Let op: de kolom ‘nr’ bevat zowel cijfers als tekst, dus deze moet het gegevenstype ’tekst’ krijgen.

Stap 5
De kolom ‘amnTp’ geeft aan of het bedrag debet of credit is. In de kolom ‘amnt’ staan zowel debet- als creditbedragen door elkaar. Hoewel er andere oplossingen mogelijk zijn (bijvoorbeeld met DAX), is het handig om deze kolom te splitsen.

Selecteer de kolommen ‘amnTp’ en ‘amnt’ (in die volgorde) en gebruik de optie “Kolom draaien” (Pivot Column) vanuit het menu ‘Transformeren’ (Transform).

Het resultaat is dat debet en credit nu elk een eigen kolom hebben:

Stap 5
Voer de query’s uit, maak relaties tussen de tabellen en gebruik de gegevens in je Power BI-rapport.

Conclusie:

Zoals je kunt zien, kun je zonder kennis van M-code of complexe transformaties in Power Query de gegevens uit een XAF-bestand importeren. Omdat XAF-bestanden altijd dezelfde opmaak hebben, kun je deze query’s ook gemakkelijk gebruiken voor meerdere boekhoudingen.

Bekijk hier meer handige tips en video’s over Power BI. Meer leren over Power BI onder begeleiding van een ervaren docent? Bekijk onze Power BI trainingen voor beginners en gevorderden.