In mijn vorige blog ben ik ingegaan op het maken van een functie om herhalende taken uit te voeren. In deze blog zal ik uitleggen hoe deze functie te gebruiken is om meer dan 60 regels uit Exact Online op te halen.
Next URL
Een eigenschap van de Cursor-Based pagination techniek is dat bij elk teruggestuurd bestand een URL voor het volgende bestand meegegeven wordt.
Wanneer de inhoud van het teruggestuurde bestand van Exact Online beter bekeken wordt kun je zien dat de URL voor de volgende call staat in de tabel welke je terugvindt onder de kolom ‘link’.
Het aanroepen van deze URL geeft eenzelfde dataset terug, maar dan van de volgende 60 regels.
List.Generate()
Een ingebouwde functie om functies repeterend uit te voeren is ‘List.Generate()’. Deze functie heeft ongeveer dezelfde werking als de Foreach functie in andere programmeertalen.
De List.Generate() functie voert de volgende stappen uit.
- Geef een startnummer op
- Controleer of een waarde voldoet aan een voorwaarde
Zo ja, ga verder met stap 3. Zo nee, stop. - Voer een functie uit
- Zet de resultaten uit stap 3 in een lijst.
Vertaald naar de situatie met Exact Online betekent dit het volgende:
- Startnummer is in ons geval niet noodzakelijk en stellen we op 0
- De voorwaarde is dat er een URL aanwezig moet zijn
- De uit te voeren functie hebben we grotendeels in de vorige blog al beschreven
- Maak een lijst van alle tabellen die we ophalen
De query om uiteindelijk alle regels uit Exact Online op te halen vind je hieronder.
Let hierbij voornamelijk op de regels 48 t/m 53. Hierin zie je hoe de functie Generate.List() uiteindelijk gebruikt wordt.
Naast dat de Generate.List() functie gebruikt kan worden om data uit Exact Online op te halen kun je dit uiteraard ook gebruiken voor de Facebook Graph API of voor andere doeleinden.
Heb je zelf nog vragen of ideeën over de data die uit Exact Online komt of wat je kunt doen met de Generate.List() functie? Laat het mij weten via de reacties.
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 //
FuncGetOnePage = (varURL) as record =>
let
Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])),
xml = try Xml.Tables(Source,null,65001) otherwise null,
data = try xml{0}[entry] otherwise null,
link = try xml{0}[link] otherwise null,
nextURL = try link{1}[#"Attribute:href"] otherwise null,
res = [Data=data, Next=nextURL]
in
res,
GeneratedList = List.Generate(
()=>[i=0, res = FuncGetOnePage(varURL)],
each [i]<iterations and [res][Data] <> null,
each [i=[i]+1, res = FuncGetOnePage([res][Next])],
each [res][Data]
),
#"Geconverteerd naar tabel" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 uitgevouwen" = Table.ExpandTableColumn(#"Geconverteerd naar tabel", "Column1", {"id", "title", "updated", "author", "link", "category", "content"}, {"id", "title", "updated", "author", "link", "category", "content"}),
#"Andere kolommen verwijderd" = Table.SelectColumns(#"Column1 uitgevouwen",{"content"}),
#"content uitgevouwen" = Table.ExpandTableColumn(#"Andere kolommen verwijderd", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
#"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata uitgevouwen" = Table.ExpandTableColumn(#"content uitgevouwen", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", {"properties"}, {"properties"}),
#"properties uitgevouwen" = Table.ExpandTableColumn(#"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata uitgevouwen", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices"}),
#"http://schemas.microsoft.com/ado/2007/08/dataservices uitgevouwen" = Table.ExpandTableColumn(#"properties uitgevouwen", "http://schemas.microsoft.com/ado/2007/08/dataservices", {"BalanceType", "Code", "Description"}, {"BalanceType", "Code", "Description"})
in
#"http://schemas.microsoft.com/ado/2007/08/dataservices uitgevouwen"
// End dataload//
MEER OVER EXACT ONLINE EN ONZE POWER BI DIENSTVERLENING? https://growteq.nl/microsoft-power-bi/
9 reacties. Reactie plaatsen
PBI geeft de volgende melding:
‘Er is een fout opgetreden in de -query. Expression.Error: De naam iterations wordt niet herkend. Controleer of de naam correct is gespeld.’ Hoe dit op te lossen?
Hoi Willem,
Heb jij de code 1 op 1 gekopieerd vanaf de blog? Zelf krijg ik namelijk deze fout niet.
Wil je anders jouw code delen?
Copy&Paste was inderdaad niet goed gegaan, werkt nu wel.
Hoe pas ik dit nu aan, zodat ik de tijd- en kostentransacties van projecten in Power BI krijg?
Je zult dan de URL aan moeten passen van het entrypoint.
Deze verwijst nu ‘hard’ naar de grootboekrekeningen.
Is er een eenvoudige manier om alle kolommen uit te vouwen (in plaats van handmatig)? Code regel 57 en verder in bovenstaand voorbeeld?
Sommige kolommen moeten namelijk meerdere malen uitgevouwen worden en met 20+ kolommen is dit niet praktisch.
Dit is helaas zo niet mogelijk. Je moet namelijk door de structuur van het XML-bestand heen ‘bladeren’.
Is het mogelijk om in de query een filter in te bouwen, zodat bijv. alleen de tijdregistraties/factuurregels/etc. van dit jaar of een bepaalde periode worden opgehaald?
Gezien de introductie van de API limieten door Exact Online krijg ik anders niet alle data binnen.
Op deze helppagina van Exact Online wordt beschreven hoe je een filter meegeeft bij het ophalen van data: https://support.exactonline.com/community/s/knowledge-base#All-All-DNO-Content-api-example-sales-invoice
Het artikel gaat hier niet specifiek over, maar laat wel een voorbeeld zien.
Is het mogelijk om deze manier van data ophalen, te laten werken in Power BI Service, zodat deze gescheduled kan worden?