Twinfield administratie self service inladen in Power BI

In eerdere blogs hebben we gedetailleerd beschreven hoe je gegevens kunt ophalen uit populaire online administratiepakketten zoals Exact Online en AFAS, en deze vervolgens kunt analyseren met behulp van Power BI. In dit artikel richten we ons op een ander veelgebruikt administratieplatform: Twinfield. Net als Exact Online en AFAS biedt Twinfield een krachtige API waarmee je gegevens kunt ophalen en naadloos kunt integreren in Power BI. In deze blog delen we een stapsgewijze handleiding om dit te realiseren, zodat je de volledige analysekracht van Power BI kunt benutten voor Twinfield-gegevens.

Als erkende experts in het koppelen van administratiesystemen met Power BI, hebben we uitgebreide ervaring in het ophalen en integreren van gegevens uit Twinfield. We laten je graag zien hoe je dit kunt doen, en delen handige tips en inzichten om het proces soepel te laten verlopen. Of je nu een financieel manager, een data-analist of een ondernemer bent, deze gids zal je helpen om waardevolle inzichten te verkrijgen uit je Twinfield-gegevens met behulp van Power BI.

Aanmaken parameters

Allereerst maken we een aantal parameters aan zodat we deze later in de scripts kunnen gebruiken bij het aanroepen van de API.

Ga naar Edit Queries en kies in het lint voor Home -> Manage Parameters.

Maak via het knopje New de volgende parameters aan:

Als username en password en organisatie gebruik je de credentials van een gebruiker met toegang tot de administraties die je wilt ophalen.

Vul bij de URL_Logon de volgende waarde in: https://login.twinfield.com/webservices/session.asmx?wsdl

Laat de andere twee URL’s en het SessionID nog even leeg. De juiste waardes hiervoor moeten we nog ophalen.

Opzetten sessie

Als eerste stap moeten we eerst een sessie opzetten waarbij de gebruiker zich moet authentiseren. Het resultaat hiervan is dat de Twinfield API een sessionID genereert. Deze sessionID kun je vervolgens gebruiken om gegevens aanvragen uit te voeren.

Daarnaast krijg je ook een clusterwaarde terug. Deze clusterwaarde is een url waar we de gegevens kunnen gaan opvragen. Maak via New Source een Blank Query aan en geef deze de naam Twinfield_sessie.

Ga naar de Advanced Editor.

Voeg hier de volgende code in:

let

Source = Xml.Tables(Web.Contents(“”& URL_Logon &””
,[Content=Text.ToBinary(”

<soap:Envelope xmlns:soap=”&Character.FromNumber(34)&”http://schemas.xmlsoap.org/soap/envelope/”&Character.FromNumber(34)&” xmlns:xsi=”&Character.FromNumber(34)&”http://www.w3.org/2001/XmlSchema-instance”&Character.FromNumber(34)&” xmlns:xsd=”&Character.FromNumber(34)&”http://www.w3.org/2001/XmlSchema”&Character.FromNumber(34)&”>
<soap:Body>
<Logon xmlns=”&Character.FromNumber(34)&”http://www.twinfield.com/”&Character.FromNumber(34)&”>

<user>”& User &”</user>

<password>”& Password &”</password>

<organisation>”& Organisation &”</organisation>

</Logon>

</soap:Body>

</soap:Envelope>
“),Headers=[#”Content-Type”=”text/xml;charset=UTF-8”]]))
in

Source

De uitkomst hiervan is een eerste resultaat met een geaggregeerd resultaat. Klik op de twee pijltjes in de kolomkop van de kolom Table om deze verder open te klappen en klik op OK.

Doe dit nog twee keer tot jet het volgende overzicht krijgt:

Klik nu op het woord Table in de rij Header. Je krijgt nu de SessionID te zien.

Neem de waarde van de SessionID op in de parameter SessionID.

Klik nu op het woord Table in de rij Body.

Je krijgt nu onder meer de cluster URL te zien.

Gebruik deze url om de waardes voor de parameters URL_Process en URL_Session in te vullen:

• URL_Process = [cluster]/webservices/processxml.asmx?wsdl
• URL_session = [cluster]/webservices/session.asmx?wsdl

Selecteren administratie

De volgende stap is het toevoegen van de juiste administratie aan de zojuist aangemaakt sessie. Maak via New Source een Blank Query aan en geef deze de naam SelectCurrentCompany. Ga naar de advanced editor en voer de volgende code in:

let

Source = Xml.Tables(Web.Contents(“” & URL_Session &””
,[Content=Text.ToBinary(”
<soap:Envelope xmlns:soap=”&Character.FromNumber(34)&”http://schemas.xmlsoap.org/soap/envelope/”&Character.FromNumber(34)&” xmlns:xsi=”&Character.FromNumber(34)&”http://www.w3.org/2001/XMLSchema-instance”&Character.FromNumber(34)&” xmlns:xsd=”&Character.FromNumber(34)&”http://www.w3.org/2001/XMLSchema”&Character.FromNumber(34)&”>

<soap:Header>

<Header xmlns=”&Character.FromNumber(34)&”http://www.twinfield.com/”&Character.FromNumber(34)&”>

<SessionID>”& SessionID &”</SessionID>

</Header>

</soap:Header>

<soap:Body>

<SelectCompany xmlns=”&Character.FromNumber(34)&”http://www.twinfield.com/”&Character.FromNumber(34)&”>

<company>XXXXX</company>

</SelectCompany>

</soap:Body>

</soap:Envelope>
“),Headers=[#”Content-Type”=”text/xml;charset=UTF-8”]]))
in
Source

Voeg tussen de tags <company>XXXX</company> het Twinfield administratie nummer in.

Het resultaat van deze query is weer een geaggregeerde tabel. Als je deze verder openklapt zie je in de kolom SelectCompanyResult ‘OK’ staan, hetgeen betekent dat het selecteren van de administratie gelukt is.

Ophalen transacties

We hebben nu een sessie en een verbinding met de juiste administratie. De volgende stap is het ophalen van transacties.

Maak via New Source een Blank Query aan en geef deze de naam Twinfield_Transacties.

Ga naar de advanced editor en voer de volgende code in:

let
Source = Xml.Tables(Web.Contents(“”& URL_Process &””
,[Content=Text.ToBinary(”

<soapenv:Envelope xmlns:soapenv=”&Character.FromNumber(34)&”http://schemas.xmlsoap.org/soap/envelope/”&Character.FromNumber(34)&” xmlns:twin=”&Character.FromNumber(34)&”http://www.twinfield.com/”&Character.FromNumber(34)&”>

<soapenv:Header>

<twin:Header>

<twin:SessionID>”& SessionID &”</twin:SessionID>

</twin:Header>

</soapenv:Header>

<soapenv:Body>

<twin:ProcessXmlString>

<twin:xmlRequest><![CDATA[
<columns code=’030_1′>

<column><field>fin.trs.head.office</field><label>Administratie</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from></column>

<column><field>fin.trs.head.officename</field><label>Adm.naam</label><visible>true</visible><ask>false</ask><operator>none</operator></column>

<column><field>fin.trs.head.year</field><label>Jaar</label><visible>true</visible><ask>true</ask><operator>none</operator><from>2018</from></column>

<column><field>fin.trs.head.period</field><label>Periode</label><visible>true</visible><ask>true</ask><operator>none</operator></column>

<column><field>fin.trs.head.yearperiod</field><label>Jaar/periode (JJJJ/PP)</label><visible>false</visible><ask>true</ask><operator>between</operator><from>2018/03</from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.head.code</field><label>Dagboek</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.head.number</field><label>Boekingsnummer</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.head.status</field><label>Status</label><visible>true</visible><ask>true</ask><operator>equal</operator><from>normal</from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.head.date</field><label>Boekdatum</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.line.dim1</field><label>Grootboekrek.</label><visible>true</visible><ask>true</ask><operator>between</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.line.dim1name</field><label>Grootboekrek.naam</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.line.dim1type</field><label>Dimensietype 1</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.line.basevaluesigned</field><label>Basisbedrag</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

<column><field>fin.trs.line.debitcredit</field><label>D/C</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>

</columns>
]]></twin:xmlRequest>

</twin:ProcessXmlString>

</soapenv:Body>

</soapenv:Envelope>

“),Headers=[#”Content-Type”=”text/xml;charset=UTF-8”]]))

in

Source

We maken hier gebruik van de Browse data optie van de Twinfield API.

Meer informatie hierover is te vinden op de website van Twinfield: https://c3.twinfield.com/webservices/documentation/#/ApiReference/Request/BrowseData

We gebruiken hier Browse code 030_2.

Vervolgens worden verschillende kolommen gedefinieerd die we willen ophalen.

Bij de kolommen fin.trs.head.year (=2018) en fin.trs.head.yearperiod (van 2018/03 tm 2018/03) hebben we tevens een filter opgenomen, zodat we in dit geval niet gelijk te veel data ophalen.

Als resultaat krijgen we weer een geaggregeerd resultaat.

Klap de tabel open tot je het volgende overzicht krijgt in de kolom ProcessXmlStringResult

Converteren resultaat

Het resultaat vanuit Twinfield staat hier in 1 kolom en moeten we converteren naar een normale tabel structuur.

Selecteer de kolom ProcessXmlStringResult en ga in het lint in de tab Transform naar Convert to list

Kies nu voor Parse en dan de optie XML

Klik in het volgende overzicht de kolom ‘tr’ en vervolgens ‘tr.td’ open.

Je ziet dat de gegevens van 1 transactie nu in meerdere rijen zijn opgenomen in plaats van netjes in één rij.

Dit gaan we omzetten de optie Pivot columns. Klap eerst de kolom ‘tr.key’ open. Verwijder de kolommen die je verder niet meer nodig hebt , zoals th en de kolommen beginnend met ‘attribute’.

Selecteer de kolom tr.td.Attribute.Fields en kies in het lint voor Pivot column

Kies bij Values column voor tr.td.Elements: Tekst en bij de Aggregate Value Function voor Don’t aggregate

Alle transacties komen nu netjes per transacties in een rij te staan en kunnen nu verder gebruikt worden in Power BI.

Afsluiting

Naast Exact Online en AFAS kunnen ook de gegevens uit Twinfield rechtstreeks in Power BI worden ingeladen. Het vraagt een aantal stappen, maar met de juiste queries kunnen alle gegevens prima uitgelezen worden.

Een vervolgstap kan zijn om van het opvragen van een sessionID en het cluster functies te maken die automatisch worden overgenomen in vervolg gegevens aanvragen.

Wellicht stof voor een volgende blog.