Self service data ophalen vanuit Exact Online in Power BI (deel 2) ?>

Self service data ophalen vanuit Exact Online in Power BI (deel 2)

In het verleden heeft mijn collega Gideon een blog geschreven over het ”self service ophalen van data uit Exact Online”. In deze blog is ingegaan op de authenticatie en het aanroepen van de API. In dit blog wil ik verder ingaan op het ophalen van deze data door middel van een aangepaste functie in Power BI.

Let op! In deze blogpost ga ik er vanuit dat de authenticatie inmiddels al is uitgevoerd. Nog niet gedaan? Lees dan eerst de blog van Gideon (Self Service data ophalen vanuit Exact Online in Power BI).

Inleiding

Eén van de eigenschappen van de API van Exact Online is dat er maximaal 60 regels per keer ontvangen kunnen worden. Bij het ophalen van bijvoorbeeld alle financiële transacties kunnen we er vanuit gaan dat dit meer dan 60 transactieregels zijn. Om alle transactieregels op te halen zal de API van Exact Online meerdere keren aangeroepen moeten worden. Hiervoor werkt Exact Online met de Cursor-Based pagination techniek. Dit betekent dat bij elke aanroep naar de API er een link mee teruggestuurd wordt waarmee de vervolgpagina opgehaald kan worden.

Cursor-Based pagination in Power BI ophalen

Het ophalen van deze zogenaamde Cursos-Based pagination kan in Power BI opgehaald worden door gebruik te maken van een zelf te maken functie en de functie List.Generate().

Allereerst maken we een functie waar we een willekeurige URL van Exact Online in kunnen plaatsen. Deze functie haalt vervolgens deze gegevens op uit Exact Online en geeft deze terug in een tabel. Dit is uiteraard nog gelimiteerd tot de eerste 60 regels.

Aangepaste functie maken

De Query die via de Geavanceerde Editor geplakt moet worden is:

let

    // Start Authentication //
    actualUrl =  URL & "/api/oauth2/token",
    cntnt = Text.ToBinary(Uri.BuildQueryString(
        [refresh_token= Refresh_token
        ,grant_type="refresh_token"
        ,client_id= Client_id
        ,client_secret=Client_secret])
    ),

    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
    result = Web.Contents(actualUrl, options),

    #"JSON" = Json.Document(result),
    access_token= #"JSON"[access_token],
    AccessTokenHeader = "Bearer " & access_token,

    // End Authentication //


    // Set variables //

    varURL= URL & "/api/v1/" & Number.ToText(DivisionCode) & "/financial/GLAccounts/",
    iterations = 60,

    // End variables //


    // Start dataload //

        Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])),
        xml = try Xml.Tables(Source,null,65001) otherwise null
    in
        xml

Let op! De variabelen zijn reeds ingesteld in het vorige blog.

De uitkomst van deze query laat de eerste 60 Grootboekrekeningen zien en de URL voor de volgende 60 Grootboekrekeningnummers. Voor de uiteindelijke functie hebben we nodig dat de data (60 grootboekrekeningnummers) en de link naar de volgende 60 regels naast elkaar staan.
Hiervoor vervangen we de volgende regels:

 
       xml = try Xml.Tables(Source,null,65001) otherwise null
    in
        xml

in:

        xml = try Xml.Tables(Source,null,65001) otherwise null,
        entry = xml{0}[entry],
        link = xml{0}[link],
        nextLink = link{1}[#"Attribute:href"]
    in
        [Data=entry, Next=nextLink]

Het resultaat is 2 regels. Regel 1 bevat de data van de eerste 60 regels. Regel 2 bevat de link naar de volgende 60 regels. Om deze Query om te zetten naar een functie zullen de volgende aanpassingen doorgevoerd moeten worden:

Helemaal bovenaan de query moet een nieuwe regel toegevoegd worden:


()=>

Deze regel zorgt ervoor dat Power BI de regels eronder als functie gaat lezen. Deze functie geven we in dit geval één variabele mee, namelijk de URL. Deze dient tussen de haakjes vastgelegd te worden. Meerdere variabelen kunnen opgegeven worden door een komma tussen de variabelen te plaatsen. Op de plaats waar we de tekst uit de variabele terug willen laten komen in de query zetten we de naam van de variabele nogmaals neer.

In onderstaand voorbeeld heet mijn variabele ‘varURI’ en geef ik deze het type ‘text’.


(varURI as text)=>

let

    // Start Authentication //

    actualUrl =  URL & "/api/oauth2/token",
    cntnt = Text.ToBinary(Uri.BuildQueryString(
        [refresh_token= Refresh_token
        ,grant_type="refresh_token"
        ,client_id= Client_id
        ,client_secret=Client_secret])
    ),

    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
    result = Web.Contents(actualUrl, options),
    #"JSON" = Json.Document(result),
    access_token= #"JSON"[access_token],
    AccessTokenHeader = "Bearer " & access_token,

    // End Authentication //


    // Set variables //

    varURL= URL & "/api/v1/"& Number.ToText(DivisionCode) & "/" & varURI,

    // End variables //


    // Start dataload //

    Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])),
    xml = try Xml.Tables(Source,null,65001) otherwise null,
    entry = xml{0}[entry],
    link = xml{0}[link],
    nextLink = link{1}[#"Attribute:href"]
in
    [Data=entry, Next=nextLink]

    // End dataload //

Let op! De overige variabelen zijn reeds ingesteld in het vorige blog.

Na het opslaan van deze query is onderstaande scherm zichtbaar:

PowerBI Exact Online Function

Bij het invullen van bijvoorbeeld de URL ‘financialtransaction/TransactionLines/’ krijg ik van de functie 2 regels teruggestuurd. 1 regel met daarin de tabel waarin de eerste 60 transactieregels aanwezig zijn en 1 regel waarin de link voor de volgende 60 regels aanwezig is.

In mijn volgende blog zal ik ingaan op de functie List.Generate() zodat alle regels uit de API van Exact Online uitgelezen kunnen worden.

Klik hier om naar onze site te gaan

8 gedachten over “Self service data ophalen vanuit Exact Online in Power BI (deel 2)

    1. Hi Willem,

      Bij het plakken van de code naar de blog is niet alles netjes meegekomen. Dit is nu aangepast zodat je de juiste code kan kopieren.

      Succes!

  1. Nu werkt het wel, krijg alleen de volgende melding:
    ‘Geef op hoe u verbinding wilt maken.’ Met de knop ‘Referenties bewerken’. Ik kan kiezen tussen Anoniem, Windows, Basis, Web-api, Organisatieaccount

      1. Via Anoniem werk het, thanks.

        Wel blijft de query op een of andere manier de tabel ophalen van ‘Financial/GLaccounts’ uit de blog van Gideon, ondanks dat ik de URI parameter heb aangepast of iets anders invul voor varURI bij het aanroepen van de functie.

        1. Kijk nog even in het laatste script op regel 26.
          Denk dat hier in jouw code nog hard de verwijzing naar Financial/GLaccounts in.

          Let even op dat je bij het aanroepen van een entrypoint altijd een ‘/’ aan het einde meegeeft (‘Financial/GLAccounts/’).

Geef een reactie

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