Twinfield administratie self service inladen in Power BI ?>

Twinfield administratie self service inladen in Power BI

In eerdere blogs hebben wij laten zien hoe gegevens vanuit Exact Online en AFAS op te halen zijn in Power BI, zodat je hier alle analysekrachten op los kan laten die je je maar kan voorstellen. Een andere veel gebruikte online administratiepakket is Twinfield. Ook Twinfield heeft een API waarmee de gegevens opgehaald kunnen worden in Power BI. In deze blog laten we de stappen zien waarmee je dit voor elkaar krijgt.

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.

Klik hier om naar onze site te gaan

17 gedachten over “Twinfield administratie self service inladen in Power BI

  1. Dit leek mij interessant en ik heb deze stappen gevolgd. PowerBI gaf bij invoeren van de stuk code voor het selecteren van een administratie een foutmelding ” Token comma expected” bij regel 36: “),Headers=[#”Content-Type”=”text/xml;charset=UTF-8”]])). PowerBI viel over de “)

    Klopt dit stukje?

    1. Beste Daniel,

      Het lijkt erop dat het stukje code dat geplakt is in de blog een paar opmaak tekens anders heeft geinterpreteerd als bedoeld. Ik heb de code vervangen in de blog.

      Dit zou de juiste moeten zijn voor het eerste onderdeel:

      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
      
  2. Ik heb dit ook geprobeerd, bij de stap Pivot column krijg ik een Error in de velden :

    Expression.Error: There were too many elements in the enumeration to complete the operation.
    Details:
    List

    Op de forums wordt dan gezegd dat je geen dubbele waarden in je rijen mag hebben voor een pivot. Dus hoe heb je dit gedaan want uit Twinfield krijg je altijd dubbele waarden in je rijen, bijvoorbeeld administratie is voor alle rijen hetzelfde. Ik zie bij jou ook duplicaten bijvoorbeeld head.code = BNK2 voor beide zichtbare records in jouw screenshot.

    Als ik Pivot Column doe met een aggregratie werkt het wel maar dat wil ik natuurlijk niet 🙂

    1. Ik had tr.key niet eerst opgeklapt maar meteen verwijderd. Daarom werkte het blijkbaar niet. Maar dat vind ik wel raar omdat tr.key dezelfde waardes bevat voor alle rijen behalve tr.key.line. Als je tr.key verwijderd mag je geen pivot column meer doen .

      1. Hi Gijs,

        Ik heb even gekeken en ik krijg inderdaad ook een melding als ik niet eerst de tr.Key openklap. Ik krijg alleen de melding dat er nog ‘nested data’ aanwezig is. Blijkbaar houdt Power BI onder water nog iets vast over de oorpsonkelijk data structuur. Ik zie eigenlijk geen andere redenen waarom dit niet zou werken.

  3. Wow!! Helemaal super dit, maar hoe krijg ik het voor elkaar dat het sessionID automatisch wordt opgehaald, want nu kan ik het niet rechtstreeks in PowerBi laden, omdat de sessionID continu veranderd.

    1. Robert,
      Er zijn wat issues met het herhalend opvragen van session id’s in power bi. Het werkt wel met het opvragen van een session id in bijvoorbeeld Postman en deze gebruiken in Power BI

  4. Mooie en duidelijke handleiding! Super!

    Ik krijg bij de stap “Selecteren administratie” echter een foutmelding:

    DataSource.Error: Web.Contents failed to get contents from ‘https://accounting.twinfield.com/webservices/session.asmx?wsdl’ (500): Internal Server Error

    Zijn er specifieke rechten noodzakelijk voor de gebruiker om toegang te krijgen tot de administratie? In Twinfield kan ik met de gebruiker inloggen en alles muteren.

    1. Erwin,
      Een 500 melding geeft ‘in principe’ aan dat het een foutmelding is aan de kant van de server.

      Er zijn echter wat issues met het herhalend opvragen van session id’s in power bi. Het werkt wel met het opvragen van een session id in bijvoorbeeld Postman en deze gebruiken in Power BI

  5. Bij ophalen van de transacties, waarbij bovenstaande code gebruikt is, komt onderstaande foutmelding:

    DataFormat.Error: Ongeldige URI: de hostnaam kan niet worden geparseerd.
    Details:
    [https://accounting.twinfield.com]/webservices/processxml.asmx?wsdl

    Hoe kan ik dit oplossen?

    1. Ik zie [https://accounting.twinfield.com]/webservices/processxml.asmx?wsdl tussen blokhaken staan, Klopt dat?

      Ik zou verwachten dat hier het cluster staan (bijvoorbeeld c4.twinfield.com).

      In de blog staan ook de blokhaken zie ik, maar die moeten er niet bij.

  6. Hé Gideon,

    Ik zou graag alle transacties op willen halen uit Power BI en niet alleen die van maart. Ik krijg het niet voor elkaar om het script aan te passen naar bijv. 12. en krijg nu slechts een deel van de transacties te zien.

    Hoe werkt dit?

    1. hi

      kijk even onder het kopje Ophalen transacties en dan in het script rond regel 28.

      Bij de kolommen fin.trs.head.year (=2018) en fin.trs.head.yearperiod (van 2018/03 tm 2018/03) staat er nu een filter opgenomen, zodat we in dit geval niet gelijk te veel data
      Als je dit aanpast naar 2018/00 tm 2018/56 moet het lukken.

  7. Hey Gideon,
    Ik krijg een foutmelding bij het ophalen van het sessie id:

    DataSource.Error: Web.Contents failed to get contents from ‘https://login.twinfield.com/webservices/session.asmx?wsdl’ (400): Bad Request
    Details:
    DataSourceKind=Web
    DataSourcePath=https://login.twinfield.com/webservices/session.asmx
    Url=https://login.twinfield.com/webservices/session.asmx?wsdl

    Waar gaat dit mis?

    Groet, Pascal

    1. Bij een bad request wil dat meestal zeggen dat je toch iets ‘verkeerds’ probeert in te sturen .
      Krijg je in de advanced editor geen syntax foutmeldingen?

      Misschien dat er een spelfoutje in het aanmaken van de paramaters zit?

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *