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

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

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.

  1. Geef een startnummer op
  2. Controleer of een waarde voldoet aan een voorwaarde
    Zo ja, ga verder met stap 3. Zo nee, stop.
  3. Voer een functie uit
  4. Zet de resultaten uit stap 3 in een lijst.

Vertaald naar de situatie met Exact Online betekent dit het volgende:

  1. Startnummer is in ons geval niet noodzakelijk en stellen we op 0
  2. De voorwaarde is dat er een URL aanwezig moet zijn
  3. De uit te voeren functie hebben we grotendeels in de vorige blog al beschreven
  4. 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//

Klik hier om naar onze site te gaan

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

  1. 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?

      1. 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?

  2. 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.

  3. 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.

Geef een reactie

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