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.
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:
| [code language=”csharp”] 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]])), |
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:
| [code language=”csharp”] xml = try Xml.Tables(Source,null,65001) otherwise null in xml [/dm_code_snippet] |
in:
| [code language=”csharp”] 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] [/dm_code_snippet] |
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:
| [code language=”csharp”]
()=> [/dm_code_snippet] |
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’.
| [code language=”csharp”]
(varURI as text)=> let // Start Authentication // actualUrl = URL & “/api/oauth2/token”, options = [Headers =[#”Content-type”=”application/x-www-form-urlencoded”],Content=cntnt], // End Authentication // // Set variables // varURL= URL & “/api/v1/”& Number.ToText(DivisionCode) & “/” & varURI, // End variables // // Start dataload // Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])), // End dataload // |
Let op! De overige variabelen zijn reeds ingesteld in het vorige blog.
Na het opslaan van deze query is onderstaande scherm zichtbaar:
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.
Meer weten over onze oplossingen?
Onze consultants hebben veel ervaring binnen een grote verscheidenheid aan branches.
Eens verder brainstormen over de mogelijkheden voor jouw organisatie?
Maak kennis met onze specialist Arnoud van der Heiden.
Wellicht herkent u een van deze vraagstukken.
Inmiddels hebben wij ruime ervaring opgedaan met Business Intelligence. Wij staan ook u graag te woord bij vragen of opmerkingen.
8 reacties. Leave new
Ik krijg de foutmelding ‘Token Literal verwacht’ bij het &-teken van >
Hoe dit op te lossen
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!
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
Hier moet je kiezen voor Anoniem. De authenticatie vind plaats via een Token.
Mocht het niet lukken met de authenticatie. Probeer dan eerst het blog van mijn collega Gideon: http://blog.refine-it.nl/self-service-data-ophalen-vanuit-exact-online-in-power-bi/
Hierin is de werkzijze rondom authenticatie beschreven.
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.
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/’).
Ik ben benieuwd naar deel 3!
Ha Willem. Deel 3 staat online!